Re: Performance-problem

I've had the exact same issue. I also debugged it until I found out 
exactly what the problem was. I didn't have the time to sit down and fix 
it, though.

When you open a non-readonly cursor, the ODBC performs a select that has 
the oid and ctid fields added in. This is done to bypass the fact that 
postgresql has no updateable cursors support.

When you try to actually get the information from the query, each line 
you ask for is another select! The performance hit I get compared to the 
same query running over SQL Server is 1:10 (sounds familiar?).

The solution:
If you can afford it - make the query readonly. This will make the ODBC 
use a regular cursor, and the problem is gone.
If you cannot afford it - the driver will need to be fixed.

What I thought for a fix - add the oid and ctid as before, but make that 
a readonly cursor. If and when an update request arrives, do the proper 
select as usual.

            Shachar

Steve Jorgensen wrote:

> Are you sure the ODBC driver is the culprit in the problem you are having? 
> Could it be the speed of the query, or the speed of the network over which 
> you are connecting, perhaps?  It would help if you describe your issue in 
> more detail.
>
> On Wednesday, March 24, 2004 1:59 AM, Maximilian Fuhrer 
> [SMTP:m.fuehrer@xxxxxxxxxxxxxx] wrote:
>  
>
> > Hallo,
> > if have a problem with the performance of the ODBC-driver.
> > i need i 10x faster.
> >
> > ODBC driver version :psqlodbc-07_03_0200
> > PostgreSQL database version : 7.3
> >
> > yours faithfully,
> > Maximilian Fuhrer
> > << File: ATT00288.html >> 
> >    


--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match