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@hm-software.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/