Re: odbc vs. libpq performance

Hi,

Sorry to resend, just hoping someone could take a look and let me know if this is an issue with the PostgreSQL ODBC driver.

Thanks!

Alejandro

Hi,

I have a simple program that inserts data into a single table (see details below).  When I have the program connect to the 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.

At first I thought that auto-commit was enabled in ODBC, but I went back and made sure to set auto-commit off and performance remained the same:

“SQLSetConnectAttr(handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);”

So I’m wondering if I’m not setting the auto-commit property correctly, or if I’m doing something wrong with ODBC (perhaps not using the right settings), or if there is an issue in the PostgreSQL ODBC driver.

I searched the archives and found one possibly related issue: http://www.postgresql.org/message-id/f147cdfff1924e52926ef90d26971476@EXCH2013.mustinformatique.fr.  However, I tried the solution they proposed there, but it didn’t improve my issue.

Any help is greatly appreciated!  Thanks!

Alejandro

Important details:

·         About my program

o   Written in C/C++

o   Compiled with GCC 4.4.7

o   The program basically spawns a given number of threads, each one establishes its own connection to the DB and then performs 100 transactions

o   Each transaction simply consists of performing 25 inserts into a single table

o   The program uses prepared statements

o   The program can connect via ODBC or via libpq

o   Both the program and the DB run on the same system, but connect via TCP

·         PostgreSQL version: “PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit”

·         PostgreSQL installation: followed the YUM installation instructions for Red Hat here: https://wiki.postgresql.org/wiki/YUM_Installation

·         No important changes to postgresql.conf file

·         OS:

o   Red Hat 6.3

o   Linux 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

·         Connection:

o   Using unixODBC 2.2.14

o   PostgreSQL ODBC driver: 09.03.0300

o   My DSN configuration:

§  Description = My test DB

§  Driver = <path to PostgreSQL ODBC driver>

§  Trace = No

§  TraceFile =

§  Servername = localhost

§  Database = mytestdb

§  Port = 5432

§  UseServerSidePrepare = 1

§  ReadOnly = No

§  RowVersioning = No

§  ShowSystemTables = No

§  ShowOidColumn = No

§  FakeOidIndex = No

§  ConnSettings =