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

--_000_3D66EBA5D043E34AADB194828BD4468F1F0714B1MERCMBX03DnaS AS_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have written little test program which takes rows from table in one datab=
ase 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 o=
dbc inserts are about twice as slow: ~70sec vs ~32 for jdbc. My test was ~7=
0,000 rows and table has 14 columns: NUMBERs, DATEs and small VARCHARs, 1 n=
umeric primary key and two non-uniqu varchar indeces - nothing fancy. I mea=
sure just the insert timing so select from source does not impact the bench=
mark. 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


--_000_3D66EBA5D043E34AADB194828BD4468F1F0714B1MERCMBX03DnaS AS_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



>

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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 smal=
l
> VARCHARs, 1 numeric primary key and two non-uniqu varchar indeces =96
> nothing fancy. I measure just the insert timing so select from source
> does not impact the benchmark. The test was repeated multiple times wit=
h
> 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=20
really slows things down. In any case I have never found ODBC to be=20
particularly fast in comparison to other interfaces.

--=20
Adrian Klaver
adrian.klaver@gmail.com

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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)

Andriy

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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

regards,
Hiroshi Inoue


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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)"
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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

(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


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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.

Andriy


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

(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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbcdriver

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


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc