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

(2010/12/23 2:30), Andriy Rysin wrote:
> On 12/21/2010 10:51 PM, Hiroshi Inoue wrote:
>> (2010/12/22 12:42), Andriy Rysin wrote:
>>> On 12/21/2010 6:49 PM, Hiroshi Inoue wrote:
>>>> (2010/12/22 5:22), Andriy Rysin wrote:
>>>>> On 12/21/2010 2:10 PM, Adrian Klaver wrote:
>>>>>> 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.
>>>>> well, the tracing is turned off, but I also found two things:
>>>>> 1) the DB server has a bit lower CPU load when using ODBC driver
>>>>> (30% vs
>>>>> 35% with jdbc) and the machine running the program has a bit higher
>>>>> CPU
>>>>> load with (6-7% vs 5% with jdbc)
>>>>> 2) pg_stat_activity shows proper prepared statement when using jdbc,
>>>>> something like:
>>>>> insert into my_table (col1, col2) values ($1, $2)
>>>>> but when I use ODBC driver the statement looks like a non-prepared
>>>>> one:
>>>>> insert into my_table (col1, col2) values (2024443, E'MYTEXT1')
>>>>>
>>>>> I wander if pgsql odbc driver does not support prepared statement
>>>>> (or I
>>>>> need to turn some flag on)
>>>>
>>>> Do you call SQLPrepare() for the query?
>>>> And are you turning on the *Server side prepare* option?
>>> Thanks Hiroshi,
>>> I do call SQLPrepare(), SQLBindParameter() and SQLExecut() but I did not
>>> set the Server side prepare option. After I did set the option to "1" I
>>> got this:
>>> 1) the statement in pg_stat_activity looks like "EXECUTE
>>> "_PLAN0x2c9afaee80"(10195,'86765865'...)" - still not like it should
>>> "insert into my_table (col1, col2) values ($1, $2)"
>>
>> Hmm, are you setting the Protocol to 7.4+?
> Ah, thanks, that was it, I changed the protocol to 7.4 and now I see
> proper server-side prepared statements in pg_stat_activity (for some
> reason still none in pg_prepared_statements). And with this I got about
> 10% speedup for my inserts, but it's still twice as slow as jdbc: 68sec
> vs 31sec for jdbc for 75,000 rows insert.
>
> When I turn on logging I see these statements:
> ...
> conn=0x2c9b058650, query='BEGIN'
> SSendExecuteRequest: plan_name=_PLAN0x2c9b05fde0 count=0
> conn=0x2c9b058650, query='SAVEPOINT _EXEC_SVP_0x2c9b05fde0'
> SendExecuteRequest: plan_name=_PLAN0x2c9b05fde0 count=0
> ...
>
> I am wandering if this SAVEPOINT for each insert (even though it's one
> big transaction) is what causing the slowdown.

Please set the *Level of rollback on errors* option to Transaction.

regards,
Hiroshi Inoue