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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-odbc by date

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