Re: Extremely Low performance with ODBC - Mailing list pgsql-sql

At 09:20 PM 5/27/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Tue, 27 May 2008 09:29:56 -0700
>From: "Richard Broersma" <richard.broersma@gmail.com>
>To: "Sebastian Rychter" <srychter@anvet.com.ar>
>Cc: pgsql-sql@postgresql.org
>Subject: Re: Extremely Low performance with ODBC
>Message-ID: 
><396486430805270929y6bc3cfcidba5a38592e433e9@mail.gmail.com>
>
>On Mon, May 26, 2008 at 9:34 PM, Sebastian Rychter
><srychter@anvet.com.ar> wrote:
>
> > I'll keep on looking for any other differences on different logs.. 
> I think I
> > already searched all the Postgresql forums I know and no one ever 
> reported
> > something like that before.

I've been following this discussion with interest - years ago I was 
responsible for a high performance web system that used ODBC in the 
data layer (not quite an oxymoron there). But I haven't touched ODBC 
since 2001! (Be warned)..

With performance times that slow it seems like there are a couple of 
possibilities that I can think of. To narrow down the choices:

How many rows does your query return? If it's returning a lot try 
running the exact same query but with a "limit 10" on the end - does 
that take just as long to run or does it come back quickly?

Here are some other ideas:

1) The Pg ODBC driver is broken and is looping internally in some wrong 
way, when constructing your result set. This seems a little unlikely 
but totally possible. The groups that Richard recommends can probably 
help on this end.

2) Your ODBC DSN is misconfigured. This seems more likely to me. You 
may already be an expert with ODBC and have it correctly configured, in 
which disregard this. But the Pg ODBC interface has a lot of unique 
options and any one or combination could be spiking your results speed. 


Have you played with the various config options in the advanced window 
of "odbcad32.exe" when creating a data source with a Pg ODBC driver?

For example, try toggling "Keyset query optimization" or "Use 
Declare/Fetch" - Also, does the "cache size" setting impact your query 
speed at all? There are a number of checkboxes here that seem like they 
might affect your situation. Be sure all the logging is turned off when 
testing performance.

Also, "updatable cursors" is turned on by default (on my copy of Pg 
ODBC) - that seems "wrong" and you might try your search with it turned 
off. Also "server side prepare" is turned off on my copy, and this also 
seems wrong - any diff with it turned on?

Another idea: when you run the query and it takes a long time, is the 
CPU spiked on your client machine or on the SQL Server or neither (if 
they're on the same machine, look in Task Manager to see which process 
is eating CPU). If it's not spiked anywhere, then it's possible that 
your problem is with a network socket timeout or something similar. 
Maybe use wire shark or something to watch your network traffic to dig 
deeper if this seems possible.

Others may have a more clear idea as to what these various ODBC options 
are for, but in my distant memories, fiddling with ODBC settings when 
doing performance tuning can make a big, big difference in how an 
application performs.

My whole goal in this regard was to find the settings in ODBC that 
caused ODBC to do as absolutely little as possible. Just receive raw 
sql, pipe it to my server and hand it back to me in a memory data 
structure. All the data structure parsing was done by my middleware 
connection wrapper to ODBC and none was done by ODBC itself. Once I got 
to that point, I was able to get some decent performance out of ODBC.

I hope some of these ideas helps! Feel free to write back on or off 
list.

Sincerely,

Steve