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
