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

Slow query : join between jet and postgresql table

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Slow query : join between jet and postgresql table
Date: 2007-01-04 14:02:52
Message-ID: 459D090C.4000405@freesurf.fr (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2007-01-05 00:16:21
Subject: Re: Slow query : join between jet and postgresql table
Previous:From: Erika MarlowDate: 2007-01-02 18:00:09
Subject: Re: error message "column "oid" doesn't exist"

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