Re: odbc vs. libpq performance - Mailing list pgsql-odbc

I've continued looking into this and I discovered something: I figured out how to successfully set the
SQL_ATTR_AUTOCOMMITattribute to off.  If you set it prior to establishing the connection, this setting is ignored and
itdefaults to on.  If you set it after establishing the connection, then it does recognize the setting and it works as
specified.

The ODBC documentation specifies that this attribute can be set either before or after establishing the connection.  If
it'snot accepting the setting before establishing the connection, is this an issue in the PostgreSQL ODBC driver? 

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Vilches, Alejandro
Sent: Tuesday, July 14, 2015 3:20 PM
To: hlinnaka@iki.fi; 'pgsql-odbc@postgresql.org'
Subject: Re: [ODBC] odbc vs. libpq performance

Hi Heikki,

Sorry for taking so long to reply, I was away for a while and came back just recently.

So, before addressing your questions, I discovered something interesting: when connecting via ODBC, transactions are
beingcommitted automatically (even though I'm setting SQL_ATTR_AUTOCOMMIT to SQL_AUTOCOMMIT_OFF).  This would explain
thepoor performance I've been seeing.   

To address your questions:
I'm attaching my little test program connecting via ODBC.  To run it you'll need to create the table (which is pretty
simpleas you'll see from the insert statement).  You'll also need to set the connection parameters.  Finally, you can
playwith the number of threads to try to get different performance results.  To compile you'll need "-lodbc -lpthread
-std=c++0x".

So, I'm wondering: am I just setting the auto commit parameter incorrectly or is it possible that there is a bug
(eitherin unixODBC or the PostgreSQL ODBC driver)? 

I also ran my tests with "log_min_duration_statement = 0" as you suggested.  When connecting via ODBC I do see that
eachinsert statement is taking longer (compared to when I connect via libpq). 

Let me know if you have any questions or if there is any other information I could provide.

Thanks!
Alejandro

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas
Sent: Thursday, April 16, 2015 12:15 AM
To: Vilches, Alejandro; 'pgsql-odbc@postgresql.org'
Subject: Re: [ODBC] odbc vs. libpq performance

On 04/16/2015 03:30 AM, Vilches, Alejandro wrote:
> I have a simple program that inserts data into a single table (see details below).  When I have the program connect
tothe DB via ODBC, performance is significantly slower compared to when I have it connect via libpq.  I was able to
achieve~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver. 

I'd suggest setting log_min_duration_statement = 0 in the server's config file. Then repeat the test and look at the
logfile to see where the time is spent. If that gives no clue, I'm happy to take a look, if you can reproduce it with a
smalltest program and post it to a list. 

- Heikki