odbc vs. libpq performance
От | Vilches, Alejandro |
---|---|
Тема | odbc vs. libpq performance |
Дата | |
Msg-id | 9CE034E149417949A58AA9A4FA7E1C5584A610A3@ORSMSX109.amr.corp.intel.com обсуждение исходный текст |
Ответы |
Re: odbc vs. libpq performance
("Vilches, Alejandro" <alejandro.vilches@intel.com>)
|
Список | pgsql-odbc |
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 =
В списке pgsql-odbc по дате отправления: