INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DB2 9.7 Prepared Statement with Parameters running Slow

DB2 9.7 Prepared Statement with Parameters running Slow

DB2 9.7 Prepared Statement with Parameters running Slow

(OP)
All,
I am having a strange problem. All my queries are running 3 times slower when run using a prepared statement having parameters.
Same query works 3 times faster when i embed those parameters in the query itself while making the prepared statement.

I tested this with a simple JAVA jdbc connection class.
Following is the code for making connection and prepared statement.

Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection conn = DriverManager.getConnection(url, uname, psswrd);

PreparedStatement psmt =conn.prepareStatement(query);
psmt.setTimestamp(1,new Timestamp(startTimeCal.getTimeInMillis()));
psmt.setTimestamp(2,new Timestamp(endTimeCal.getTimeInMillis()));

ResultSet rs = psmt.executeQuery();

I don't know if this helps, but i tried running the query in two modes with TRACE_ALL enabled in connection. I couldn't understand much from the trace file but following are the execution completion log traces from the file for the two modes:

With parameter:
jccTime:2010-11-17-09:03:33.091Thread:mainPreparedStatement@1572e449 executeQuery () returned com.ibm.db2.jcc.t4.j@d8d9850
jccSystemMonitor:stop core: 14066.455489999998ms | network: 13989.78416ms | server: 13986.041000000001ms

With Parameter embedded in the query:
jccTime:2010-11-17-09:01:34.313Thread:mainPreparedStatement@29e97f9f executeQuery () returned com.ibm.db2.jcc.t4.j@4c5e176f
jccSystemMonitor:stop core: 3846.488966ms | network: 3762.8420109999997ms | server: 3758.717ms

It is not specific to a particular query, its happening for all. Is there any way i can debug this?

I cannot understand this behaviour. Any insight on this is really appreciated.
 

RE: DB2 9.7 Prepared Statement with Parameters running Slow

(OP)
This is from a Java main class that i created for testing.

RE: DB2 9.7 Prepared Statement with Parameters running Slow

what are the SQL statements you are issuing on your testing?

In any case...
if you are doing SQL statements that are going to be executed only once per connection to the database, preparing the statement will normally be slower then executing it directly.
If it is going to be issued several times per connection, then prepare it only the first time it has to be used.

In most cases you will be better off creating a Stored Procedure and calling it, as this will have a lower overhead even if you don't prepare the call to the SP

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: DB2 9.7 Prepared Statement with Parameters running Slow

(OP)
I am not counting the time taken for preparing the PreparedStatement. The problem is there is difference in time for executing query when we parameterize the Prepared Statement and When we embed the parameters in the query of the Prepared Statement.

Time difference is in running following line of code:
ResultSet rs = psmt.executeQuery();

RE: DB2 9.7 Prepared Statement with Parameters running Slow

(OP)
I am comparing query execution time of a Prepared Statement when it is parameterized and when the parameters are embedded in the query.

RE: DB2 9.7 Prepared Statement with Parameters running Slow

reetesh123,

Check that the variables you are using in the parameterised version (the slow one) match the actual DB2 column definitions. It's possible that if you are using the wrong column type (decimal instead of integer etc.) then DB2 might take longer as it has to convert first.

Marc

RE: DB2 9.7 Prepared Statement with Parameters running Slow

(OP)
I doubt that this is the case because i checked this for a query with a String parameter and the datatype in the DB for the column is Varchar. Still the results were slow with String parameter.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close