Stored Proc Performance Improvement

[db2udbgirl]

I have coded a stored procedure which is planned to run only once in
production to process historic data from DB2 8.2 database.

It accesses data from a partitioned table and based on some condition
it would insert the result to another table and update the same row
(using updatable cursor). Right now it takes around 2 minutes to
process 10000 rows of data so when I did a test run against 3 Million
row it took around 30 hours to complete.

But I feel it as a concern as it has to run against 50 million rows of
data in prod and that means it would run for 500 hours. Well I think
that I'm missing something which causes the performance impact in my
stored procedure.

I ran explain against the 4 cursors which I'm using and all are using
index scan. Also statistics are also updated and it is 100% accurate.

The cursors which I'm using are as below
DECLARE cur1 CURSOR WITH HOLD FOR
SELECT col1,col2,col3
FROM
smt.order_detai l
WHERE
ORDER_CSI = 1
AND
DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=blue]
>=IN_LAST_RUN_T IMESTAMP[/color]
FOR UPDATE OF col4,col5,col6;

DECLARE cur2 CURSOR WITH HOLD FOR
SELECT col1,col2,col3
FROM
smt.order_detai l
WHERE
ORDER_CSI in (7,8,9)
AND
DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=blue]
>=IN_LAST_RUN_T IMESTAMP[/color]
FOR UPDATE OF col4,col5,col6;

DECLARE cur3 CURSOR WITH HOLD FOR
SELECT col1,col2,col3
FROM
smt.order_detai l
WHERE
ORDER_CSI = 10
AND
DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=blue]
>=IN_LAST_RUN_T IMESTAMP[/color]
FOR UPDATE OF col4,col5,col6;



DECLARE cur4 CURSOR WITH HOLD FOR
SELECT
col1,col2,col3, col4,col5,col6, col7
FROM
smt.order_detai l_lookup
WHERE
VIN_ATTRB_CD = IN_VIN_ENCODING _ATTRB_CD with ur;

Can you give me any tips please.

[Brian Tkatch]

Re: Stored Proc Performance Improvement

[I am not an experienced DB2 user, so these are just comments.]

The queries look fine to me.

Is there a specific rerason you are using a CURSOR and not a simple
INSERT and UPDATE? Bulk processing is generally handled much the
database itself, without having to deal with those pesky CURSORs.

Further, the problem could arise from the amount of records being
worked on, given the amount of logs it has to keep (for a possible
ROLLBACK). IIRC, there's something about something not being flushed
until the end of the trasnsaction, so the more records, the slower it
is.

In that case, i'd suggest working on it in sets, with a COMMIT every
some records.

B.

[db2udbgirl]

Re: Stored Proc Performance Improvement

Since I need to work on the individual data level I need to use cursor
and I cannot do a direct update. Also I'm commiting once for every 500
rows inserted/updated.
Thanks for your comments

[Eugene F]

Re: Stored Proc Performance Improvement

If chenging the implementation of your proc is not a case, try to get
database system snapshots while your proc is running to determine where
is the performance bottleneck. The DB2 SQL stored procedure profiler
tool would be helpful too. Also you may check wiht this article
http://www-128.ibm.com/developerwork...06arocena.html.

[Serge Rielau]

Re: Stored Proc Performance Improvement

Eugene F wrote:[color=blue]
> If chenging the implementation of your proc is not a case, try to get
> database system snapshots while your proc is running to determine where
> is the performance bottleneck. The DB2 SQL stored procedure profiler
> tool would be helpful too. Also you may check wiht this article
> http://www-128.ibm.com/developerwork...06arocena.html.
>[/color]
Actually a lot of the tricks mentioned by Gustavo aren't needed anymore
since he has incorporated them into the SQL PL "Optimizer" .
E.g. CASE statement, chaining SET statements... All taken care of
automagically in V8.2

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

[Serge Rielau]

Re: Stored Proc Performance Improvement

db2udbgirl wrote:[color=blue]
> Since I need to work on the individual data level I need to use cursor
> and I cannot do a direct update. Also I'm commiting once for every 500
> rows inserted/updated.
> Thanks for your comments
>[/color]
You say this is a "partitione d table" I presume you refer to a DPF
environment?
Funneling the batch process row by row through the coordinator
serializes everything.
Instead ONLY select the rows for the local partition.
Then connect to each note separately and start the batch.
This way the procedure operates on local data and scales linear with the
number of nodes.
If you have CPU to burn you can even subdivide further using modulo.
I'm had good experience at a customer running two instances of a
processing heavy batch process data partition.

IFF you use global temporary tables make sure they are created in a
tablespace local to the partition (use dynamic DDL to do that).

Cheers
Serge

PS: Feel free to drop me a line.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[db2udbgirl]

Re: Stored Proc Performance Improvement

Yes, I'm using DPF on UDB 8.2 with 8 Logical DB2 partitions on AIX 5.3.
My AIX box configuration are as below

/home/user > bindprocessor -q
The available processors are: 0 1 2 3 4 5 6 7
So I have 8 CPU's in my machine and the main memory is as below

/home/user > lsattr -El mem0
goodsize 24576 Amount of usable physical memory in Mbytes False
size 24576 Total amount of physical memory in Mbytes False

This particular table is partitioned across 7 logical partitions. Some
questions after reading your post
1. How can I select rows only from a local partition?
2. How can I restrict my stored procedure to work only on data which is
on local partition?
Thanks for your comments so far.

[Serge Rielau]

Re: Stored Proc Performance Improvement

db2udbgirl wrote:[color=blue]
> Yes, I'm using DPF on UDB 8.2 with 8 Logical DB2 partitions on AIX 5.3.
> My AIX box configuration are as below
>
> /home/user > bindprocessor -q
> The available processors are: 0 1 2 3 4 5 6 7
> So I have 8 CPU's in my machine and the main memory is as below
>
> /home/user > lsattr -El mem0
> goodsize 24576 Amount of usable physical memory in Mbytes False
> size 24576 Total amount of physical memory in Mbytes False
>
> This particular table is partitioned across 7 logical partitions. Some
> questions after reading your post
> 1. How can I select rows only from a local partition?
> 2. How can I restrict my stored procedure to work only on data which is
> on local partition?
> Thanks for your comments so far.
>[/color]
add this predicate to the WHERE clause of the driving cursor.
DBPARTITIONNUM (T.pk) = CURRENT DBPARTITIONNUM

Assuming that the rest of your logic partitions naturally that should
suffice.

Given that this is a heavy batch process you want to get db2exfmt output
for all statements and ensure you don't have any TQs that are avoidable.

The easiest ay to to that is to CALL SET_ROUTINE_OPT S('EXPLAIN YES')
recreate the SQL procedure
and then run db2exfmt -d <db> -o <outfile> -1 (<-- this is a "one")
(make sure you have run the sqllib/misc/EXPLAIN.DDL CLP script of course.

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

[db2udbgirl]

Re: Stored Proc Performance Improvement

Can you please confirm whether my understanding is correct
[1]. Update the 3 cursors which access the partitioned table as below
DECLARE cur1 CURSOR WITH HOLD FOR
SELECT col1,col2,col3
FROM
smt.order_detai l
WHERE
DBPARTITIONNUM (VIN) = CURRENT DBPARTITIONNUM AND
ORDER_CSI = 1 AND
DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=blue]
>=IN_LAST_RUN_T IMESTAMP[/color]
FOR UPDATE OF col4,col5,col6;

2) Drop/Create the procedure
3)
Call the procedure individually from each partitions as below
export DB2NODE=1
db2 terminate
db2 connect to db_name
db2 "call proc(8,'abc')"

Can you please confirm whether this what you meant ?

[Serge Rielau]

Re: Stored Proc Performance Improvement

db2udbgirl wrote:[color=blue]
> Can you please confirm whether my understanding is correct
> [1]. Update the 3 cursors which access the partitioned table as below
> DECLARE cur1 CURSOR WITH HOLD FOR
> SELECT col1,col2,col3
> FROM
> smt.order_detai l
> WHERE
> DBPARTITIONNUM (VIN) = CURRENT DBPARTITIONNUM AND
> ORDER_CSI = 1 AND
> DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=green]
>> =IN_LAST_RUN_TI MESTAMP[/color]
> FOR UPDATE OF col4,col5,col6;
>
> 2) Drop/Create the procedure
> 3)
> Call the procedure individually from each partitions as below
> export DB2NODE=1
> db2 terminate
> db2 connect to db_name
> db2 "call proc(8,'abc')"
>
> Can you please confirm whether this what you meant ?
>[/color]
Confirmed. That's what I meant.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[sethwai@gmail.com]

Re: Stored Proc Performance Improvement

Since you are usng essentially the same query 3 times why not hande it
with a single pass through the table

DECLARE cur1 CURSOR WITH HOLD FOR
SELECT col1,col2,col3
FROM
smt.order_detai l
WHERE
ORDER_CSI in (1,7,8,9,10)
AND
DWH_EFCTV_TIMST M >=IN_LAST_RUN_T IMESTAMP OR DWH_UPD_TIMSTM[color=blue]
>=IN_LAST_RUN_T IMESTAMP[/color]

FOR UPDATE OF col4,col5,col6;

Then use logic in the stored procedure to determine how to update each
row. This might help.

Lew