Hi list !
I have a problem with a query in Access2000, which takes very long to run.
It queries a linked Jet table (in an Access2000 backend) joined to a linked ODBC
table (in a PostgreSQL backend).
Postgresql's version is 8.2, PsqlODBC is 8.02.0200.
The query itself is very basic :
SELECT * FROM myAccessTable AS A INNER JOIN myPsqlTable AS P ON A.field1 =
P.field2 WHERE A.condition = <something>;
The field1 on the Access table has an index (non-unique).
The Postgresql Table is more complex : it is a view that I link via ODBC.
In this view, the field2 is unique, and is recognized by Access2000 as such
(Access creates a Primary Key on this field).
Running EXPLAIN ANALYZE on this view without conditions take ~500ms.
If I add a condition on field 2, it runs in ~0.3ms.
This view is linked via VBA code using this connection string :
ODBC;
DRIVER={PostgreSQL Unicode};
SERVER=myserver;
PORT=5432;
DATABASE=mydb;
UID=xxx;
PWD=xxx;
UseDeclareFetch=1;
Fetch=1000;
TrueIsMinus1=1;
BoolsAsChar=0;
TextAsLongVarchar=1;
RowVersioning=1;
There are not a lot of rows returned (~5000).
The first rows appear quite fast (~5 seconds), but if I want to go to the end of
the result set, it takes some minutes.
During this time, If I check PostgreSQL's status, it looks like for each line,
it declares a cursor (DECLARE SQL000blabla with hold for "SELECT field2,
myview.field2 FROM myview WHERE field2 = 'somevalue'"), and then FETCHes the result.
This postgresql backend takes 100% of one CPU. IO usage is null, memory usage is
quite low (~50MB).
Is this normal behaviour ?
How can I speed up this query ?
Thanks a lot for your help on this !
--
Arnaud