First iteration of stored procedure is slow.

**Mark A** · Aug 12 '08, 01:55 PM

Re: First iteration of stored procedure is slow.

"Patrick Finnegan" <finnegan.patri ck@gmail.comwro te in message
news:908c014a-df61-4016-92b8-e5f16cfd23f6@m4 4g2000hsc.googl egroups.com...

>
Running DB2 8.2 on aix.
>
Similar problem to this thread.
>

http://groups.google.com/group/comp.databases.ibm-db2/browse_thread/thread/7b79826d7ebf986e/0b9ffd1e725f0380?lnk=gst&q=%2B%22stored+procedure%22#0b9ffd1e725f0380

>
May have something to do with the sql package cache. The snapshots
show it's not a buffer pool issue.
>
The SP uses it's arguments instead of parameter markers in the sql.
>
eg.g i_last_name
>
SET sql_where = sql_where CONCAT ' PNAM.LAST_NAME LIKE
UPPER(REPLACE( ' ;
SET sql_where = sql_where CONCAT '''' CONCAT i_last_name CONCAT ''''
CONCAT ', '' '', '''')) AND ' ;
>
Stripping out the SQL from the SP and testing the statement from the
command line........... .....
>
PNAM.LAST_NAME LIKE UPPER(REPLACE( 'Murphy', ' ', ''))
>
>
The first time the statement runs with "Murphy" it takes 20 seconds.
Subsequent executions take 24 milleseconds. If I change Murphy to
"Finnegan" the sql takes 20 seconds. Subsequent executions take 24
milleseconds. And so on. So I am wondering if DB2 thinks the SQL
statement is brand new when the argument changes i.e. "Murphy" changes
to "Finnegan" and it recompiles the SQL statemennt.
>
Any ideas?

Your problem may be the UPPER function. DB2 will not use an index on that
column with that function (and most other functions). Try creating a
separate column in the table which is automatically generated as the UPPER
value of the LAST_NAME and search on that column (without the UPPER).

**Serge Rielau** · Aug 12 '08, 03:35 PM

Re: First iteration of stored procedure is slow.

Mark A wrote:

"Patrick Finnegan" <finnegan.patri ck@gmail.comwro te in message
news:908c014a-df61-4016-92b8-e5f16cfd23f6@m4 4g2000hsc.googl egroups.com...

>Running DB2 8.2 on aix.
>>
>Similar problem to this thread.
>>
>http://groups.google.com/group/comp....9ffd1e725f0380
>>
>May have something to do with the sql package cache. The snapshots
>show it's not a buffer pool issue.
>>
>The SP uses it's arguments instead of parameter markers in the sql.
>>
>eg.g i_last_name
>>
>SET sql_where = sql_where CONCAT ' PNAM.LAST_NAME LIKE
>UPPER(REPLAC E( ' ;
>SET sql_where = sql_where CONCAT '''' CONCAT i_last_name CONCAT ''''
>CONCAT ', '' '', '''')) AND ' ;
>>
>Stripping out the SQL from the SP and testing the statement from the
>command line........... .....
>>
> PNAM.LAST_NAME LIKE UPPER(REPLACE( 'Murphy', ' ', ''))
>>
>>
>The first time the statement runs with "Murphy" it takes 20 seconds.
>Subsequent executions take 24 milleseconds. If I change Murphy to
>"Finnegan" the sql takes 20 seconds. Subsequent executions take 24
>milleseconds . And so on. So I am wondering if DB2 thinks the SQL
>statement is brand new when the argument changes i.e. "Murphy" changes
>to "Finnegan" and it recompiles the SQL statemennt.
>>
>Any ideas?

>
Your problem may be the UPPER function. DB2 will not use an index on that
column with that function (and most other functions). Try creating a
separate column in the table which is automatically generated as the UPPER
value of the LAST_NAME and search on that column (without the UPPER).

Actually that can't be it because the expression.
Now DB2 can't use a start stop key here because it has to compute the
pattern. But why not use = instead of LIKE?

Anyway..this would explain the big time difference only if the table
were stored on a tape-recorder given the orders of magnitude involved... ;-)

Every new literal indeed means a brand new statement to DB2. So yes a
recompile happnes.
But why 20 seconds? What's the rest of the SQL statement like? Any SQL
functions, triggers, RI constraints, nested views?

When all is said and done you should use a CAST(? AS VARCHAR(...))
instead of plugging in Murphy directly. Then use the USING clause to
bind the value in.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

First iteration of stored procedure is slow.

First iteration of stored procedure is slow.

Comment

Comment