Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection.prepare statement performance.

 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I connect to an IBM db2 for I using jdbc driver supplied by jtopen. I noticed that preparestatement method requires a lot of time to be performed (about 200 msec), while on a db2 LUW it's about ten times faster.
What can I check ? Is anybody that experienced a similar issue ?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ideas to check:
1) Does this happen every time or just the first time you run the statement? (test with a connection pool size of 1 to ensure you are actually using the same connection on subsequent attempts) If so, that's good as you can pre-load at startup.
2) Does the slowness happen if you use a really simple query. select * from table where col = 'foo' for example? If not, the driver could be having trouble with the actual SQL.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne, thanks for your quick replace. So, the slowness is detected even for the most simplest queries, just like the one you posted as example,and,generally, the first time that are prepared. Successive prepare are much faster, because the DBMS somehow caches them and save prepare time. The problem is that PS are cached at connection level, so in a Java EE environment - I use websphere , but it doesn't matter much - using a get - use -close pattern somehow inficiates this caching benefit. Websphere provides a prepared statement cache at connection pool level, so things aren't so bad, but the main question is:why does a prepare instruction take so long ? The awkward thing is that if I try and use IBM driver for db2 for Linux (or windows) it is very fast.
So you can reply "why don't just use it?"... Well, mainly because I'm not sure that XA transactions are supported when db2 LUW driver is used with db2 for I; second, I am not sure that syntax and semantics of the two drivers is really the same...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic