At 09:20 PM 5/27/2008, [EMAIL PROTECTED] wrote:
Date: Tue, 27 May 2008 09:29:56 -0700
From: "Richard Broersma" <[EMAIL PROTECTED]>
To: "Sebastian Rychter" <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Subject: Re: Extremely Low performance with ODBC
Message-ID:
<[EMAIL PROTECTED]>
On Mon, May 26, 2008 at 9:34 PM, Sebastian Rychter
<[EMAIL PROTECTED]> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
