DB2 9.7 Prepared Statement with Parameters running Slow

[reetesh123]

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.

 

[fredericofonseca]

Question. is this statement called from a web page, or is it from a application that could allow for the statement to be prepared just once and executed many times?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[reetesh123]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[reetesh123]

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();

 

[reetesh123]

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

 

[MarcLodge]

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

 

[reetesh123]

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.