Thread: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbc driver
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:
- Turn autocommit off
- Prepare the insert sql
- Fetch row from source table
- Bind all columns for insert statement
- Perform insert, repeat 3)
- 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
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.klaver@gmail.com
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
(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
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
(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
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
(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
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