Обсуждение: Ctrl+F in MsAccess table is very slow

Поиск
Список
Период
Сортировка

Ctrl+F in MsAccess table is very slow

От
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
exactmatch). 

The commlog is quite surprising : Access starts by fetching rows 10 by 10, then 1 by 1 (even though FETCH=100 in my
driveroptions). 

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',user='xxxx',passwd='xxxxx'
          onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='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;SERVER=xxxx;PORT=5432;UID=xxxx;PWD=xxxxxxxxxxxxxxxx;CA=d;A6=;A7=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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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 ...

Re: Ctrl+F in MsAccess table is very slow

От
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',user='xxxx',passwd='xxxxx'
>
>
> onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='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;SERVER=xxxx;PORT=5432;UID=xxxx;PWD=xxxxxxxxxxxxxxxx;CA=d;A6=;A7=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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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","coderegion","region","codedepartement","departement","codearrondissement","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

От
Richard Broersma Jr
Дата:
--- David Gardner <david.gardner@yucaipaco.com> 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 ones that were indexed.  I
haven't had any problems with slow filtering rows, but to be fair my database only has 1/30 the
number of records.

Re: Ctrl+F in MsAccess table is very slow

От
Arnaud Lesauvage
Дата:
David Gardner a écrit :
> 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)?

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
aview, not a real table ?). 

Apart from that, the field is a varchar and Access sees it as such, and codelocalite is an int4 field, so I think
everythingis fine here. 

Regards,
--
Arnaud

Re: Ctrl+F in MsAccess table is very slow

От
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 = textBox.value
sSearchField = comboBox.value
sSQL = "SELECT * FROM yourTable WHERE " + sSearchFeild + " LIKE '" +  _
    sSearchItem = "';"

set recordSet = currentDB.openrecordset (sSQL)

Then you can put the results in a list box

Set list.Recordset = 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 écrit :
>> 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)?
>
> 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
> codelocalite 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

От
Arnaud Lesauvage
Дата:
David Gardner a écrit :
> 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 into the tables to get to the record that we're
lookingfor. 
You're right that I could build a form though, but that's the kind of work I'd like to avoid ! ;)

> 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