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

Re: Performance-problem

From: Shachar Shemesh <psql(at)shemesh(dot)biz>
To: "jorgens(at)coho(dot)net" <jorgens(at)coho(dot)net>
Cc: "'pgsql-odbc(at)postgresql(dot)org'" <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Performance-problem
Date: 2004-03-27 07:49:48
Message-ID: 4065321C.8010105@shemesh.biz (view raw or flat)
Thread:
Lists: pgsql-odbc
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(dot)fuehrer(at)hm-software(dot)de] 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/


In response to

pgsql-odbc by date

Next:From: Katherine GoodwinDate: 2004-03-29 12:11:48
Subject: Problem with unixODBC
Previous:From: Steve JorgensenDate: 2004-03-26 21:01:18
Subject: Re: Performance-problem

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