Re: Ctrl+F in MsAccess table is very slow - Mailing list pgsql-odbc

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
>