Обсуждение: Slow query : join between jet and postgresql table

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

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