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:
Turn autocommit off
Prepare the insert sql
Fetch row from source table
Bind all columns for insert statement
Perform insert, repeat 3)
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?
