This week's book giveaway is in the Android forum.
We're giving away four copies of Learning Java by Building Android Games and have Jean-Marcel Belmont on-line!
See this thread for details.
Win a copy of Learning Java by Building Android Games this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

Connection.prepare statement performance.  RSS feed

 
Ranch Hand
Posts: 996
9
IBM DB2 Java Netbeans IDE Spring
  • 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 ?
 
author & internet detective
Marshal
Posts: 38650
675
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: 996
9
IBM DB2 Java Netbeans IDE Spring
  • 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...
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!