By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Slow prepare

P: n/a
Hi All,

I came across a weird situation today. We have a simple sql statement
with some java code thrown around it which executes in about 1-2
seconds. However when put in input parameter markers in the same sql
stmt, do a preparestatement and then bind the appropriate parameters
using setXX etc (with the same values), the statements runs in close
to 20-25 seconds.

Why should there be so much difference in the total execution times
when running through the prepare statement in java ?

I did do run the same sql through db2batch and the prepare time is
almost negligible, and the fact that it runs in 1-2 seconds without
"prepare" indicates that the query itself is not a problem.

There is one thing that I noticed which might be pertinent to this,
upon looking at the output of the statement event monitor the user_cpu
times for both executions remain same, however the system_cpu time is
way higher for prepared statements. Also there are no index physical
reads when running directly but quite a few index physical reads when
running through prepared stmt

Any insights ?

(DB2 V8.2.5 on 32bit Aix 5.2)( bufferpool data hit 89% index hit 95%,
Some sort overflows but they remain consitent in both queries and so I
dont think they are contributing to 'this' problem atleast!)

P

Feb 15 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Purple-D" <pa******@gmail.comwrote in message
news:11*********************@s48g2000cws.googlegro ups.com...
Hi All,

I came across a weird situation today. We have a simple sql statement
with some java code thrown around it which executes in about 1-2
seconds. However when put in input parameter markers in the same sql
stmt, do a preparestatement and then bind the appropriate parameters
using setXX etc (with the same values), the statements runs in close
to 20-25 seconds.

Why should there be so much difference in the total execution times
when running through the prepare statement in java ?

I did do run the same sql through db2batch and the prepare time is
almost negligible, and the fact that it runs in 1-2 seconds without
"prepare" indicates that the query itself is not a problem.

There is one thing that I noticed which might be pertinent to this,
upon looking at the output of the statement event monitor the user_cpu
times for both executions remain same, however the system_cpu time is
way higher for prepared statements. Also there are no index physical
reads when running directly but quite a few index physical reads when
running through prepared stmt

Any insights ?

(DB2 V8.2.5 on 32bit Aix 5.2)( bufferpool data hit 89% index hit 95%,
Some sort overflows but they remain consitent in both queries and so I
dont think they are contributing to 'this' problem atleast!)

P
If the distribution of data is highly skewed, then DB2 may use a different
access plan if it knows what the predicate literals are (without the
parameter markers) and can come up with a more efficient access plan.
Feb 15 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.