We have monitored a report , run as a stored procedure, which reads some 800,000 rows
from a main cursor, and inserts a
report line into a table for each row read.
The procedure is created as:
CREATE PROCEDURE Test_Report ()
LANGUAGE SQL
DECLARE timestamp1 TIMESTAMP;
DECLARE timestamp2 TIMESTAMP;
DECLARE v_insert_count INTEGER;
DECLARE c_commit_number INTEGER DEFAULT 100;
........
We have noticed from statement monitoring that
1) a statement such as
SET timestamp1 = timestamp2;
results in a section in the stored procedure
VALUES (VARCHAR (COALESCE(RTRIM(TSTMP_DD_MMM_YYYY(TIMESTAMP(:H00180
:H00181
))),''),30)) into :H00138
:H00139
which is showing in the monitor as (on average) Exec Time: 0.003000 seconds; an
additional 40 minutes on the overall elapsed time when processed 800k times.
2) a statement such as
IF MOD(v_insert_count, c_commit_number) = 0 THEN
results in a section in the stored procedure
SELECT CASE WHEN(MOD(:H00192 :H00193
, :H00016
:H00017 ) = 0 ) THEN 1 ELSE 0 END into :H00001 FROM
(VALUES 1) AS X
which is showing in the monitor as (on average) Exec Time: 0.005320 seconds; an
additional 70 minutes on the overall elapsed time.
Recoding these statements so that they do not appear as separate DB2 SP sections has
resulted in the predictable elapsed time
savings.
Has anybody experience of this type of overhead and perhaps got a list of guidelines
on code to be avoided, for performance
reasons, in DB2 SQL stored procedures?
Thanks for any pointers,
Graham.
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
