Ctrl+F in MsAccess table is very slow
Ctrl+F in MsAccess table is very slow
am 21.03.2007 12:54:10 von Arnaud Lesauvage
Hi list !
I have a linked table in Access 2000 (the backend is postregsql 8.2.3).
This table is a view with ~30.000 rows.
If I try to search a field in this table, it takes a very long time to complete (searching in a varchar field for an exact match).
The commlog is quite surprising : Access starts by fetching rows 10 by 10, then 1 by 1 (even though FETCH=100 in my driver options).
Do I have to change a setting in my connection string ? Or should I change something on my server ?
Thanks a lot for your help ! (below is the start of my commlog)
DSN info: DSN='PostgreSQL30W',server='xxxx',port='5432',dbase='xxxx',u ser='xxxx',passwd='xxxxx'
onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',sho wsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''
Global Options: Version='08.02.0200', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=1
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_', conn_settings='' conn_encoding='OTHER'
[ PostgreSQL version string = '8.2.3' ]
[ PostgreSQL version number = '8.2' ]
conn=098CADA0, query='select oid, typbasetype from pg_type where typname = 'lo''
[ fetched 0 rows ]
[ Large Object oid = -999 ]
[ Client encoding = 'UTF8' (code = 6) ]
conn=098CADA0, PGAPI_DriverConnect(out)='DSN=PostgreSQL30W;DATABASE=xxxx;SE RVER=xxxx;PORT=5432;UID=xxxx;PWD=xxxxxxxxxxxxxxxx;CA=d;A6=;A 7=100;A8=4096;B0=254;B1=8190;BI=0;C2=dd_;CX=1b8d47bb;A1=7.4- 1'
conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for SELECT "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#' ,"planfax_x","planfax_y","planfax_scale","web" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 62503 OR "codelocalite" = 60406 OR "codelocalite" = 62834 OR "codelocalite" = 59158 OR "codelocalite" = 59025 OR "codelocalite" = 2524 OR "codelocalite" = 2650 OR "codelocalite" = 2478 OR "codelocalite" = 59324 OR "codelocalite" = 8404'
conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
[ fetched 10 rows ]
conn=098CADA0, query='close "SQL_CUR071D99C0"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for SELECT "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#' ,"planfax_x","planfax_y","planfax_scale","web" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 89190 OR "codelocalite" = 88271 OR "codelocalite" = 39006 OR "codelocalite" = 58053 OR "codelocalite" = 21361 OR "codelocalite" = 68331 OR "codelocalite" = 75101 OR "codelocalite" = 75102 OR "codelocalite" = 75103 OR "codelocalite" = 75104'
conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
[ fetched 10 rows ]
conn=098CADA0, query='close "SQL_CUR071D99C0"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for SELECT "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#' ,"planfax_x","planfax_y","planfax_scale","web" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 75105 OR "codelocalite" = 75106 OR "codelocalite" = 75107 OR "codelocalite" = 75108 OR "codelocalite" = 75109 OR "codelocalite" = 75110 OR "codelocalite" = 75111 OR "codelocalite" = 75112 OR "codelocalite" = 75113 OR "codelocalite" = 75114'
conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
[ fetched 10 rows ]
conn=098CADA0, query='close "SQL_CUR071D99C0"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for SELECT "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#' ,"planfax_x","planfax_y","planfax_scale","web" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 75115 OR "codelocalite" = 75116 OR "codelocalite" = 75117 OR "codelocalite" = 75118 OR "codelocalite" = 75119 OR "codelocalite" = 75120 OR "codelocalite" = 80357 OR "codelocalite" = 60046 OR "codelocalite" = 80219 OR "codelocalite" = 2371'
conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
[ fetched 10 rows ]
conn=098CADA0, query='close "SQL_CUR071D99C0"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for SELECT "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#' ,"planfax_x","planfax_y","planfax_scale","web" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 62889 OR "codelocalite" = 60300 OR "codelocalite" = 2392 OR "codelocalite" = 62260 OR "codelocalite" = 16211 OR "codelocalite" = 33061 OR "codelocalite" = 31112 OR "codelocalite" = 17355 OR "codelocalite" = 82102 OR "codelocalite" = 12110'
conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
[ fetched 10 rows ]
conn=098CADA0, query='close "SQL_CUR071D99C0"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 62503'
conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
[ fetched 1 rows ]
conn=098CADA0, query='close "SQL_CUR071DB260"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 60406'
conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
[ fetched 1 rows ]
conn=098CADA0, query='close "SQL_CUR071DB260"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 62834'
conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
[ fetched 1 rows ]
conn=098CADA0, query='close "SQL_CUR071DB260"'
conn=098CADA0, query='RELEASE _per_query_svp_'
conn=098CADA0, query='COMMIT'
conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE "codelocalite" = 59158'
conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
etc etc etc ...
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Ctrl+F in MsAccess table is very slow
am 21.03.2007 16:55:38 von David Gardner
Try this with and without the Declare/Fetch option checked. Also this
may be a situation where you may have an index on your varchar field,
yet Access doesn't know about it.
Also what datatype does Access think the field is? I have run into
problems where Access will think the text datatype is a Memo field (FAQ
6.5 & 6.6). While on the subject of Access being weird is either your
index field or the codelocalite field declared as int8(bigint)?
Arnaud Lesauvage wrote:
> Hi list !
>
> I have a linked table in Access 2000 (the backend is postregsql 8.2.3).
> This table is a view with ~30.000 rows.
> If I try to search a field in this table, it takes a very long time to
> complete (searching in a varchar field for an exact match).
>
> The commlog is quite surprising : Access starts by fetching rows 10 by
> 10, then 1 by 1 (even though FETCH=100 in my driver options).
>
> Do I have to change a setting in my connection string ? Or should I
> change something on my server ?
>
> Thanks a lot for your help ! (below is the start of my commlog)
>
>
> DSN info:
> DSN='PostgreSQL30W',server='xxxx',port='5432',dbase='xxxx',u ser='xxxx',passwd='xxxxx'
>
>
> onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',sho wsystable='0'
> conn_settings='',conn_encoding='OTHER'
> translation_dll='',translation_option=''
> Global Options: Version='08.02.0200', fetch=100, socket=4096,
> unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
> disable_optimizer=1, ksqo=1, unique_index=1,
> use_declarefetch=1
> text_as_longvarchar=1, unknowns_as_longvarchar=0,
> bools_as_char=1 NAMEDATALEN=64
> extra_systable_prefixes='dd_', conn_settings=''
> conn_encoding='OTHER'
> [ PostgreSQL version string = '8.2.3' ]
> [ PostgreSQL version number = '8.2' ]
> conn=098CADA0, query='select oid, typbasetype from pg_type where typname
> = 'lo''
> [ fetched 0 rows ]
> [ Large Object oid = -999 ]
> [ Client encoding = 'UTF8' (code = 6) ]
> conn=098CADA0,
> PGAPI_DriverConnect(out)='DSN=PostgreSQL30W;DATABASE=xxxx;SE RVER=xxxx;PORT=5432;UID=xxxx;PWD=xxxxxxxxxxxxxxxx;CA=d;A6=;A 7=100;A8=4096;B0=254;B1=8190;BI=0;C2=dd_;CX=1b8d47bb;A1=7.4- 1'
>
> conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for
> SELECT
> "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#'
> ,"planfax_x","planfax_y","planfax_scale","web" FROM
> "geo"."view_localites_compat" WHERE "codelocalite" = 62503 OR
> "codelocalite" = 60406 OR "codelocalite" = 62834 OR "codelocalite" =
> 59158 OR "codelocalite" = 59025 OR "codelocalite" = 2524 OR
> "codelocalite" = 2650 OR "codelocalite" = 2478 OR "codelocalite" = 59324
> OR "codelocalite" = 8404'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
> [ fetched 10 rows ]
> conn=098CADA0, query='close "SQL_CUR071D99C0"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for
> SELECT
> "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#'
> ,"planfax_x","planfax_y","planfax_scale","web" FROM
> "geo"."view_localites_compat" WHERE "codelocalite" = 89190 OR
> "codelocalite" = 88271 OR "codelocalite" = 39006 OR "codelocalite" =
> 58053 OR "codelocalite" = 21361 OR "codelocalite" = 68331 OR
> "codelocalite" = 75101 OR "codelocalite" = 75102 OR "codelocalite" =
> 75103 OR "codelocalite" = 75104'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
> [ fetched 10 rows ]
> conn=098CADA0, query='close "SQL_CUR071D99C0"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for
> SELECT
> "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#'
> ,"planfax_x","planfax_y","planfax_scale","web" FROM
> "geo"."view_localites_compat" WHERE "codelocalite" = 75105 OR
> "codelocalite" = 75106 OR "codelocalite" = 75107 OR "codelocalite" =
> 75108 OR "codelocalite" = 75109 OR "codelocalite" = 75110 OR
> "codelocalite" = 75111 OR "codelocalite" = 75112 OR "codelocalite" =
> 75113 OR "codelocalite" = 75114'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
> [ fetched 10 rows ]
> conn=098CADA0, query='close "SQL_CUR071D99C0"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for
> SELECT
> "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#'
> ,"planfax_x","planfax_y","planfax_scale","web" FROM
> "geo"."view_localites_compat" WHERE "codelocalite" = 75115 OR
> "codelocalite" = 75116 OR "codelocalite" = 75117 OR "codelocalite" =
> 75118 OR "codelocalite" = 75119 OR "codelocalite" = 75120 OR
> "codelocalite" = 80357 OR "codelocalite" = 60046 OR "codelocalite" =
> 80219 OR "codelocalite" = 2371'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
> [ fetched 10 rows ]
> conn=098CADA0, query='close "SQL_CUR071D99C0"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071D99C0" cursor with hold for
> SELECT
> "index","codecontinent","continent","codepays","pays","coder egion","region","codedepartement","departement","codearrondi ssement","arrondissement","codeuniteurbaine","uniteurbaine", "codelocalite","localite","localitemin","localitecodepostal" ,"localitetype","localitepopulation",'#S_C_H#'
> ,"planfax_x","planfax_y","planfax_scale","web" FROM
> "geo"."view_localites_compat" WHERE "codelocalite" = 62889 OR
> "codelocalite" = 60300 OR "codelocalite" = 2392 OR "codelocalite" =
> 62260 OR "codelocalite" = 16211 OR "codelocalite" = 33061 OR
> "codelocalite" = 31112 OR "codelocalite" = 17355 OR "codelocalite" =
> 82102 OR "codelocalite" = 12110'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071D99C0"'
> [ fetched 10 rows ]
> conn=098CADA0, query='close "SQL_CUR071D99C0"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for
> SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE
> "codelocalite" = 62503'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
> [ fetched 1 rows ]
> conn=098CADA0, query='close "SQL_CUR071DB260"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for
> SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE
> "codelocalite" = 60406'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
> [ fetched 1 rows ]
> conn=098CADA0, query='close "SQL_CUR071DB260"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for
> SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE
> "codelocalite" = 62834'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
> [ fetched 1 rows ]
> conn=098CADA0, query='close "SQL_CUR071DB260"'
> conn=098CADA0, query='RELEASE _per_query_svp_'
> conn=098CADA0, query='COMMIT'
> conn=098CADA0, query='declare "SQL_CUR071DB260" cursor with hold for
> SELECT "codataremarque" FROM "geo"."view_localites_compat" WHERE
> "codelocalite" = 59158'
> conn=098CADA0, query='fetch 100 in "SQL_CUR071DB260"'
>
>
> etc etc etc ...
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Ctrl+F in MsAccess table is very slow
am 21.03.2007 17:12:21 von Richard Broersma Jr
--- David Gardner wrote:
> Also what datatype does Access think the field is? I have run into
> problems where Access will think the text datatype is a Memo field (FAQ
> 6.5 & 6.6). While on the subject of Access being weird is either your
> index field or the codelocalite field declared as int8(bigint)?
I've seen this Memo applied to non-dimentioned varchar() fields, Even one=
s that were indexed. I
haven't had any problems with slow filtering rows, but to be fair my data=
base only has 1/30 the
number of records.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: Ctrl+F in MsAccess table is very slow
am 23.03.2007 13:51:05 von Arnaud Lesauvage
David Gardner a =E9crit :
> Try this with and without the Declare/Fetch option checked. Also this
> may be a situation where you may have an index on your varchar field,
> yet Access doesn't know about it.
>=20
> Also what datatype does Access think the field is? I have run into
> problems where Access will think the text datatype is a Memo field (FAQ
> 6.5 & 6.6). While on the subject of Access being weird is either your
> index field or the codelocalite field declared as int8(bigint)?
Hi David,
Turning on or of UseDeclareFetch apparently makes no difference.
But you are right that the field is indexed and that Access doesn't know =
about it (probably because the linked table is a view, not a real table ?=
).
Apart from that, the field is a varchar and Access sees it as such, and c=
odelocalite is an int4 field, so I think everything is fine here.
Regards,
--
Arnaud
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: Ctrl+F in MsAccess table is very slow
am 23.03.2007 18:16:13 von David Gardner
I think Access not knowing if the server side fields have indexes is
common in both tables and views. Are you viewing the table using a form
or the tables?
What I often do is make a form as a sort of search tool, with a text
input box, and maybe a combo box to select the field I am searching in
then you can do something like:
sSearchItem =3D textBox.value
sSearchField =3D comboBox.value
sSQL =3D "SELECT * FROM yourTable WHERE " + sSearchFeild + " LIKE '" + _
sSearchItem =3D "';"
set recordSet =3D currentDB.openrecordset (sSQL)
Then you can put the results in a list box
Set list.Recordset =3D recordSet
This should be a bit faster than cntrl-F, not sure if it is appropriate
for the project your working on, but I always seem to be making some
kinda search form for my projects.
Arnaud Lesauvage wrote:
> David Gardner a =E9crit :
>> Try this with and without the Declare/Fetch option checked. Also this
>> may be a situation where you may have an index on your varchar field,
>> yet Access doesn't know about it.
>>
>> Also what datatype does Access think the field is? I have run into
>> problems where Access will think the text datatype is a Memo field (FA=
Q
>> 6.5 & 6.6). While on the subject of Access being weird is either your
>> index field or the codelocalite field declared as int8(bigint)?
>=20
> Hi David,
>=20
> Turning on or of UseDeclareFetch apparently makes no difference.
> But you are right that the field is indexed and that Access doesn't kno=
w
> about it (probably because the linked table is a view, not a real table=
?).
>=20
> Apart from that, the field is a varchar and Access sees it as such, and
> codelocalite is an int4 field, so I think everything is fine here.
>=20
> Regards,
> --=20
> Arnaud
>=20
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 7: You can help support the PostgreSQL project by donating at
>=20
> http://www.postgresql.org/about/donate
>=20
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Re: Ctrl+F in MsAccess table is very slow
am 23.03.2007 20:23:56 von Arnaud Lesauvage
David Gardner a =E9crit :
> I think Access not knowing if the server side fields have indexes is
> common in both tables and views. Are you viewing the table using a form
> or the tables?
Just as a table.
We are a few people working on this DB and we are used to just ctrl+f int=
o the tables to get to the record that we're looking for.
You're right that I could build a form though, but that's the kind of wor=
k I'd like to avoid ! ;)
=20
> This should be a bit faster than cntrl-F, not sure if it is appropriate
> for the project your working on, but I always seem to be making some
> kinda search form for my projects.
Yes, I think one day I'll have to do this too... Well, maybe the day has =
come !
Thanks for your help David !
Regards
--
Arnaud
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster