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

On 12/21/2010 09:29 AM, Andriy Rysin wrote:
> 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

One to thing look at is whether you have logging turned on in ODBC, this
really slows things down. In any case I have never found ODBC to be
particularly fast in comparison to other interfaces.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com