Re: Slow query : join between jet and postgresql table

Arnaud Lesauvage wrote:
> 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 ?

Seems yes as long as you turn on the UseDeclareFetch option.

> How can I speed up this query ?

Please try to turn off the UseDeclareFetch option though I'm not
sure about the effect.

regards,
Hiroshi Inoue