Skip site navigation (1) Skip section navigation (2)

pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbc driver

From: Andriy Rysin <Andriy(dot)Rysin(at)sas(dot)com>
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbc driver
Date: 2010-12-21 17:29:27
Message-ID: 3D66EBA5D043E34AADB194828BD4468F1F0714B1@MERCMBX03D.na.SAS.com (view raw or flat)
Thread:
Lists: pgsql-odbc
I have written little test program which takes rows from table in one database and inserts them into same table in PostgreSQL 9.0.
It can do the inserts either via jdbc or via odbc (09_00_0200 and unixODBC 2.3.0). The pattern is fairly simple:
1)      Turn autocommit off
2)      Prepare the insert sql
3)      Fetch row from source table
4)      Bind all columns for insert statement
5)      Perform insert, repeat 3)
6)      When done, commit

The problem is that jdbc performs pretty well (on par with Oracle DB) but odbc inserts are about twice as slow: ~70sec vs ~32 for jdbc. My test was ~70,000 rows and table has 14 columns: NUMBERs, DATEs and small VARCHARs, 1 numeric primary key and two non-uniqu varchar indeces - nothing fancy. I measure just the insert timing so select from source does not impact the benchmark. The test was repeated multiple times with pretty consistent results.

Can anybody confirm this or have a hint on where to look for the source of the problem?
Thanks
Andriy

Responses

pgsql-odbc by date

Next:From: Richard BroersmaDate: 2010-12-21 18:14:18
Subject: Comparing Enumerated Types between JDBC and ODBC drivers
Previous:From: Jon ErdmanDate: 2010-12-14 18:22:14
Subject: Re: Driver version numbers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group