Stored procedure slower than an ad-hoc query ?!

[Nesa]

I have a stored procedure that wraps a moderately complex query over 5,
6 related tables. The performance of the procedure is unacceptably slow
as it takes on average 5-10 min to complete.
To diagnose the problem, I copied the query in db2 command editor, and
substituted the procedure parameters that appear in the query with
fixed values with intention to locate the parts that are slowing it
down. However when I run the query unmodified it returns almost
instantaneously (while calling the procedure with the same parameters
takes 10mins, and with btw no records returned).
I am expecting that a query inside a stored procedure would only run
faster then an ad-hoc one.
I am running DB2 8.2 on Linux.
Does anybody have an idea on what might be wrong there?
Thanks
Nesa

[Serge Rielau]

Nesa wrote:

I have a stored procedure that wraps a moderately complex query over 5,
6 related tables. The performance of the procedure is unacceptably slow
as it takes on average 5-10 min to complete.
To diagnose the problem, I copied the query in db2 command editor, and
substituted the procedure parameters that appear in the query with
fixed values with intention to locate the parts that are slowing it
down. However when I run the query unmodified it returns almost
instantaneously (while calling the procedure with the same parameters
takes 10mins, and with btw no records returned).
I am expecting that a query inside a stored procedure would only run
faster then an ad-hoc one.
I am running DB2 8.2 on Linux.
Does anybody have an idea on what might be wrong there?
Thanks
Nesa

Try this:
--#SET TERMINATOR @
DROP PROCEDURE ...@
CALL SET_ROUTINE_OPTS('REOPT ONCE')@
CREATE PROCEDURE ...@
CALL SET_ROUTINE_OPTS(CAST(NULL AS VARCHAR))@

Now call the procedure.
You apparently got a better plan because Db2 could exploit the exact
values for optimization.

In addition to REOPT ONCE you can experiment with REOPT ALWAYS

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Nesa]

Thanks for your reply. Unfortunately it didn't help. Tried with both
once and always but no difference on response time (at least not a
noticeable one).
Is there a tool I can use to pinpoint the problem?
Thanks
Nesa

[Serge Rielau]

Nesa wrote:

Thanks for your reply. Unfortunately it didn't help. Tried with both
once and always but no difference on response time (at least not a
noticeable one).
Is there a tool I can use to pinpoint the problem?
Thanks
Nesa

First get explains for both cases and make sure they are the same.
One more thought add the the options to 'BLOCKING ALL'.
Maybe the procedure doesn't use a blocked cursor.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Nesa wrote:

Thanks for your reply. Unfortunately it didn't help. Tried with both
once and always but no difference on response time (at least not a
noticeable one).

Differences in the execution plans are most likely the main issue here.
What you have to remember is that the DB2 optimizer has better information
if you provide exact values. For example, if you say "col > ?" then DB2
does not know how many rows might qualify for this predicate. But if you
say "col > 10000", then the estimate will be much more accurate (assuming
up-to-date statistics).

Also note that there is no reason why a stored procedure running a single
query would be any faster than running the query ad-hoc at the CLP. At
most you could save the compilation time. But the actual execution has to
be performed in both cases.

You can also try to use "db2batch" (or snapshots) to narrow things down a
bit more.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Been there myself. :)

Place the exact values in the PROCEDURE itself and see how it runs. If
it runs slowly, there is a real issue. It should run just as quickly.

Then, start removing the parameters one by one and replace them with
the variables. See when it slows down. Usually, it is just one
parameter that ends up slowing it down. Check the optimizer's plan for
that specific clause.

Sometimes it simply optimnizes for an assumed number of records. Add
OPTIMIZE FOR x ROWS to the end of the query in the PROCEDURE or DYNAMIC
RESULT SETS x to the PROCEDURE declaration itself for better
performance, x being the actual numbewr of records returned right now.
Or use 1, or use 32000 to see what happens.

B.