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

(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+?

> 2) SQLBindParameter() got sensitive to the data types when I bind NULL
> parameter (before it didn't really care - SQL_CHAR worked for any column)
> 3) the speed didn't improve much (the change was about 1-2%)
>
> Andriy