Skip site navigation (1) Skip section navigation (2)

Ctrl+F in MsAccess table is very slow

From: Arnaud Lesauvage <arnaud(dot)lesauvage(at)laposte(dot)net>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Ctrl+F in MsAccess table is very slow
Date: 2007-03-21 11:54:10
Message-ID: 46011CE2.1000701@laposte.net (view raw or flat)
Thread:
Lists: pgsql-odbc
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 ...



Responses

pgsql-odbc by date

Next:From: David GardnerDate: 2007-03-21 15:55:38
Subject: Re: Ctrl+F in MsAccess table is very slow
Previous:From: noreplyDate: 2007-03-21 09:52:50
Subject: [ psqlodbc-Bugs-1001827 ] Error when updating a TIMESTAMP column with NULL using ADODB

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group