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

On 12/22/2010 2:29 PM, Hiroshi Inoue wrote:
> (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.
Thanks Hiroshi, that was it!
I actually tried it before but I guess just didn't specify it right that
time.

So for poor chaps like me trying to get odbc performance to match that
one of jdbc here are two parameters you want to have in odbc.ini:
Protocol = 7.4-1
UseServerSidePrepare = 1

Regards
Andriy