Thread: ODBC Driver performance comparison

On 09/05/2014 08:53 AM, Vladimir Romanov wrote:
> Hello all!
> I do some test with ODBC driver for PosgreSql, TimesTen & MySQL. I compare
> performance on very simple request. Database always located on same PC as
> test application. Test PC - Lenovo T500, Cnetos 6.5 64, 8 Gb RAM, SSD.
> I found what PostgreSql ODBC driver is slowest in comparison.
> IMHO problems related to protocol used. I can't use SHM connection to
> server or even UNIX socket.

Actually, you can use a UNIX domain socket with psqlodbc. Just put the
path to the socket, e.g. "/tmp", in the Servername property, instead of
the hostname. That's assuming psqlodbc was compiled with libpq support,
(--with-libpq configure option), but I'm pretty sure that's the default
on all major distributions.

> perftool report - http://freepcrf.com/files/db_test_pq.pdf

Would be interesting to get the psqlodbc function names in the chart.
Any chance you could re-run the benchmark with a debug-enabled build?

It's interesting that 10.7% of the runtime seems to be spent in
__tz_convert() function, called indirectly by SQLExecute. Off the top of
my head, I don't see where that call is coming from. I wouldn't expect
SQLExecute to do much with timezones, at least when there are no
timestamp fields involved.

It might also be fairly straightforward to optimize the sprintf/sscanf
function calls. They're probably used to convert between integers and
strings, and it should be possible write some sort of fast-paths for
those conversions.

Are you measuring wall-clock time or CPU time? If it's CPU time, I'm
surprised that the I/O-related syscalls, poll(), send() and recv(), are
so high on the profile. If not, there isn't much you can do in the
client side to make them faster, as the time is simply spent waiting for
the server.

- Heikki

On 09/05/2014 02:40 PM, Heikki Linnakangas wrote:

> Are you measuring wall-clock time or CPU time? If it's CPU time, I'm
> surprised that the I/O-related syscalls, poll(), send() and recv(), are
> so high on the profile. If not, there isn't much you can do in the
> client side to make them faster, as the time is simply spent waiting for
> the server.

There's still potentially one big thing that can be done: reduce
round-trips.

For example, the PgJDBC driver sends multi-statements to the server by
writing a bunch of bind/parse/execute messsages, *then* waiting for a
response and processing each response as it receives it.

It can be worth looking at what's happening on the wire.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services