asked on

Cursor Rowcount and query optimization in Db2

I could not find the db2 substitute for Oracle's %rowcount. Am using DB2 v 8.
I have a complex "search" query. I am using cursors here. I need to count the total number of records returned by the cursor and while opening the cursor I need to limit the number of rows between a given range( given thru input parameters). Everything is working fine. however the performance is too low.
The total time taken for the execution of the query is about 20 secs. I need to improve the performance asap.

currently what im doing is that counting the entire cursor using count(*) and again declaring the cursor and returning the result sets.

I am not sure whether there exists any alternate, better approach for the same requirement in db2 as I am new to db2.

Can any one suggest me? Thanks in advance.

Kent Olsen

Hi Premalatha,

Depending on the underlying O/S and exact DB2 product, you may be able to do this with the OLAP extensions. The syntax is a bit different than you're accustomed to, but incredibly powerful.

SELECT row_number () over (), *
FROM mytable....

Good Luck,
Kent

Premalatha

ASKER

I tried row_number () over (), * in the below cursor . not working I doubt whether i have used it at right place. Can u enumerate?


 DECLARE RS1 CURSOR WITH RETURN FOR
                           SELECT * FROM (SELECT RTRIM(A.CODESYS) AS CODESYS,
                                                 RTRIM(A.IND_FED_FUNC_EMSN) AS IND_FED_FUNC_EMSN,
                                                 RTRIM(A.IND_FED_FUNC_BOARD) AS IND_FED_FUNC_BOARD,
                                                 RTRIM(A.IND_FED_CHPP_DEF) AS IND_FED_CHPP_DEF,
                                                 RTRIM(A.IND_FED_LPP_DEF) AS IND_FED_LPP_DEF,
                                                 RTRIM(D. IND_FED_CHHP) AS IND_TRAN_CHHP,
                                                 RTRIM(D.IND_FED_PHPP) AS IND_TRAN_PHPP,
                                                 RTRIM(A.IND_FED_PIECE_ASMB) AS IND_FED_PIECE_ASMB,
                                                 RTRIM(A.CODEFUNC) AS CODEFUNC,
                                                 RTRIM(B.DTE_MODL_YR) AS DTE_MODL_YR,
                                                 RTRIM(E.DES_MODL) AS DES_MODL,
                                                 RTRIM(B.CDE_PEER) AS CDE_PEER,
                                                 RTRIM(C.NUM_PIECE) AS NUM_PIECE,
                                                 RTRIM(B.CODEVEH_TYP) AS CODEVEH_TYP,
                                                 RTRIM(A.DES_FED_FUNC_EXT) AS DES_FED_FUNC_EXT,
                                COALESCE(C.AMT_FED_PIECE_RETL,0) AS AMT_FED_PIECE_RETL,
                                COALESCE(C.TXT_FED_PIECE_COM,'') AS TXT_FED_PIECE_COM,
                                COALESCE(D.CDE_STME_GRP1,'')||COALESCE(D.CDE_GRP2,'') AS OPCODE,
                                replace(substr(char(CAST(D.QTY_FED_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.') AS QTY_FED_LBR_TME_UN,
                                COALESCE(D.TXT_FED_LBR_COM,'') AS TXT_FED_LBR_COM,
                                COALESCE(D.CODEPIECE_CST,'') AS CODEPIECE_CST,
                                ROWNUMBER() OVER (ORDER BY B.DTE_MODL_YR DESC) AS RN
                           FROM  GUEST.FED_VEH_GRP B, GUEST.FED_FUNC A, GUEST.FED_MODL_YR_PIECE C, GUEST.FED_MODL_FUNC_PIECE D,
                               GUEST.VEH_PEER_REF E
                           WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND C.DTE_MODL_YR = D.DTE_MODL_YR AND
                                D.DTE_MODL_YR = A.DTE_MODL_YR AND A.DTE_MODL_YR = E.DTE_MDL_YR AND B.CDE_PEER = D.CDE_PEER AND
                                B.CODEVEH_TYP = D.CODEVEH_TYP AND A.CODEFUNC = D.CODEFUNC AND C.NUM_PIECE = D.NUM_PIECE
                                AND B.CDE_PEER = E.CDE_MODL_SERIES || E.CDE_MODL_ENGINE
                                AND A.DTE_MODL_YR BETWEEN V_DTE_MODL_YR_FROM AND V_DTE_MODL_YR_TO
                                AND D.CODEFUNC BETWEEN V_CODEFUNC_FROM AND V_CODEFUNC_TO AND
                                B.CDE_PEER BETWEEN V_CDE_PEER_FROM AND V_CDE_PEER_TO AND
                                C.NUM_PIECE BETWEEN V_NUM_PIECE_FROM AND V_NUM_PIECE_TO
                                AND D.QTY_FED_LBR_TME_UN = 0)
                                AS RES WHERE RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;
                                OPEN RS1;

Open in new window

Kent Olsen

What didn't work? Did you get an error message or unexpected result?

Premalatha

ASKER

Am not getting the result set in the output. Getting an empty row

DECLARE RS1 CURSOR WITH RETURN FOR
                           SELECT * FROM (SELECT row_number () over (), RTRIM(A.CODESYS) AS CODESYS,
                                                 RTRIM(A.IND_FED_FUNC_EMSN) AS IND_FED_FUNC_EMSN,
                                                 RTRIM(A.IND_FED_FUNC_BOARD) AS IND_FED_FUNC_BOARD,
                                                 RTRIM(A.IND_FED_CHPP_DEF) AS IND_FED_CHPP_DEF,
                                                 RTRIM(A.IND_FED_LPP_DEF) AS IND_FED_LPP_DEF,
                                                 RTRIM(D. IND_FED_CHHP) AS IND_TRAN_CHHP,
                                                 RTRIM(D.IND_FED_PHPP) AS IND_TRAN_PHPP,
                                                 RTRIM(A.IND_FED_PIECE_ASMB) AS IND_FED_PIECE_ASMB,
                                                 RTRIM(A.CODEFUNC) AS CODEFUNC,
                                                 RTRIM(B.DTE_MODL_YR) AS DTE_MODL_YR,
                                                 RTRIM(E.DES_MODL) AS DES_MODL,
                                                 RTRIM(B.CDE_PEER) AS CDE_PEER,
                                                 RTRIM(C.NUM_PIECE) AS NUM_PIECE,
                                                 RTRIM(B.CODEVEH_TYP) AS CODEVEH_TYP,
                                                 RTRIM(A.DES_FED_FUNC_EXT) AS DES_FED_FUNC_EXT,
                                COALESCE(C.AMT_FED_PIECE_RETL,0) AS AMT_FED_PIECE_RETL,
                                COALESCE(C.TXT_FED_PIECE_COM,'') AS TXT_FED_PIECE_COM,
                                COALESCE(D.CDE_STME_GRP1,'')||COALESCE(D.CDE_GRP2,'') AS OPCODE,
                                replace(substr(char(CAST(D.QTY_FED_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.') AS QTY_FED_LBR_TME_UN,
                                COALESCE(D.TXT_FED_LBR_COM,'') AS TXT_FED_LBR_COM,
                                COALESCE(D.CODEPIECE_CST,'') AS CODEPIECE_CST,
                                ROWNUMBER() OVER (ORDER BY B.DTE_MODL_YR DESC) AS RN
                           FROM  GUEST.FED_VEH_GRP B, GUEST.FED_FUNC A, GUEST.FED_MODL_YR_PIECE C, GUEST.FED_MODL_FUNC_PIECE D,
                               GUEST.VEH_PEER_REF E
                           WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND C.DTE_MODL_YR = D.DTE_MODL_YR AND
                                D.DTE_MODL_YR = A.DTE_MODL_YR AND A.DTE_MODL_YR = E.DTE_MDL_YR AND B.CDE_PEER = D.CDE_PEER AND
                                B.CODEVEH_TYP = D.CODEVEH_TYP AND A.CODEFUNC = D.CODEFUNC AND C.NUM_PIECE = D.NUM_PIECE
                                AND B.CDE_PEER = E.CDE_MODL_SERIES || E.CDE_MODL_ENGINE
                                AND A.DTE_MODL_YR BETWEEN V_DTE_MODL_YR_FROM AND V_DTE_MODL_YR_TO
                                AND D.CODEFUNC BETWEEN V_CODEFUNC_FROM AND V_CODEFUNC_TO AND
                                B.CDE_PEER BETWEEN V_CDE_PEER_FROM AND V_CDE_PEER_TO AND
                                C.NUM_PIECE BETWEEN V_NUM_PIECE_FROM AND V_NUM_PIECE_TO
                                AND D.QTY_FED_LBR_TME_UN = 0)
                                AS RES WHERE RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;
                                OPEN RS1;

Open in new window

Premalatha

ASKER

Sorry. Ignore my prev reply. Actually im not getting any error. however the performance is the same ( taking too long to execute)

Premalatha

ASKER

Already I am retrieving the rownumber() in my original query....

Kent Olsen

The query needs a bit of cleaning up, but that shouldn't the cause of the performance issue.

How many rows are being returned?
How long does it take to run?
Are all of the columns used in the JOIN operations indexed?

Kent

momi_sabag

how many rows are in each table?
what are the keys of each table?

Premalatha

ASKER

The query returns 3512 rows.
It takes around 20 secs to run.
All the columns used in the Join are indexed
One of the table ( transaction table) has 3700 rows. Another have 700 rows. Remaining others have only few rows around 40....

momi_sabag

how long does this takes you

SELECT row_number () over (), RTRIM(A.CODESYS) AS CODESYS,
RTRIM(A.IND_FED_FUNC_EMSN) AS IND_FED_FUNC_EMSN,
RTRIM(A.IND_FED_FUNC_BOARD) AS IND_FED_FUNC_BOARD,
RTRIM(A.IND_FED_CHPP_DEF) AS IND_FED_CHPP_DEF,
RTRIM(A.IND_FED_LPP_DEF) AS IND_FED_LPP_DEF,
RTRIM(D. IND_FED_CHHP) AS IND_TRAN_CHHP,
RTRIM(D.IND_FED_PHPP) AS IND_TRAN_PHPP,
RTRIM(A.IND_FED_PIECE_ASMB) AS IND_FED_PIECE_ASMB,
RTRIM(A.CODEFUNC) AS CODEFUNC,
RTRIM(B.DTE_MODL_YR) AS DTE_MODL_YR,
RTRIM(E.DES_MODL) AS DES_MODL,
RTRIM(B.CDE_PEER) AS CDE_PEER,
RTRIM(C.NUM_PIECE) AS NUM_PIECE,
RTRIM(B.CODEVEH_TYP) AS CODEVEH_TYP,
RTRIM(A.DES_FED_FUNC_EXT) AS DES_FED_FUNC_EXT,
COALESCE(C.AMT_FED_PIECE_RETL,0) AS AMT_FED_PIECE_RETL,
COALESCE(C.TXT_FED_PIECE_COM,'') AS TXT_FED_PIECE_COM,
COALESCE(D.CDE_STME_GRP1,'')||COALESCE(D.CDE_GRP2,'') AS OPCODE,
replace(substr(char(CAST(D.QTY_FED_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.') AS QTY_FED_LBR_TME_UN,
COALESCE(D.TXT_FED_LBR_COM,'') AS TXT_FED_LBR_COM,
COALESCE(D.CODEPIECE_CST,''

Kent Olsen

I think that this will work much better if the query is structured to first join the smaller tables, then the increasingly larger ones. That will promote better usage of the indexes.

Can you post the row count of each table (total rows, not just the target rows).

Kent

momi_sabag

sorry
it doesn't make sense
did you try to create a descending index on DTE_MODL_YR ?

Premalatha

ASKER

SELECT COUNT(*) FROM FED_VEH_GRP

1
-----------
533

1 record(s) selected.

SELECT COUNT(*) FROM FED_FUNC

1
-----------
75

1 record(s) selected.

SELECT COUNT(*) FROM FED_MODL_YR_PIECE

1
-----------
875

1 record(s) selected.

SELECT COUNT(*) FROM FED_MODL_FUNC_PIECE

1
-----------
3512

1 record(s) selected.

SELECT COUNT(*) FROM VEH_PEER_REF

1
-----------
1189

1 record(s) selected.

I have no dba rights......;-(

Kent Olsen

And this query takes 20 seconds?

What's the underlying hardware and O/S?

Kent

Premalatha

ASKER

I am using Windows XP

Kent Olsen

Unless you've got duplicated keys (resulting in a huge cartesian) there's no way that this should take anywhere close to 20 seconds.

Let's break this query apart and rebuild it step by step. Actually, let's pare it down. Can you run the query below?

Kent

DECLARE RS1 CURSOR WITH RETURN FOR
SELECT 
  row_number () over () as RN, 
  RTRIM(A.CODESYS) AS CODESYS,
  RTRIM(A.IND_FED_FUNC_EMSN) AS IND_FED_FUNC_EMSN,
  RTRIM(A.IND_FED_FUNC_BOARD) AS IND_FED_FUNC_BOARD,
  RTRIM(A.IND_FED_CHPP_DEF) AS IND_FED_CHPP_DEF,
  RTRIM(A.IND_FED_LPP_DEF) AS IND_FED_LPP_DEF,
  RTRIM(D. IND_FED_CHHP) AS IND_TRAN_CHHP,
  RTRIM(D.IND_FED_PHPP) AS IND_TRAN_PHPP,
  RTRIM(A.IND_FED_PIECE_ASMB) AS IND_FED_PIECE_ASMB,
  RTRIM(A.CODEFUNC) AS CODEFUNC,
  RTRIM(B.DTE_MODL_YR) AS DTE_MODL_YR,
  RTRIM(E.DES_MODL) AS DES_MODL,
  RTRIM(B.CDE_PEER) AS CDE_PEER,
  RTRIM(C.NUM_PIECE) AS NUM_PIECE,
  RTRIM(B.CODEVEH_TYP) AS CODEVEH_TYP,
  RTRIM(A.DES_FED_FUNC_EXT) AS DES_FED_FUNC_EXT,
  COALESCE(C.AMT_FED_PIECE_RETL,0) AS AMT_FED_PIECE_RETL,
  COALESCE(C.TXT_FED_PIECE_COM,'') AS TXT_FED_PIECE_COM,
  COALESCE(D.CDE_STME_GRP1,'')||COALESCE(D.CDE_GRP2,'') AS OPCODE,
  replace(substr(char(CAST(D.QTY_FED_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.') AS QTY_FED_LBR_TME_UN,
  COALESCE(D.TXT_FED_LBR_COM,'') AS TXT_FED_LBR_COM,
  COALESCE(D.CODEPIECE_CST,'') AS CODEPIECE_CST
FROM GUEST.FED_VEH_GRP B
INNER JOIN GUEST.FED_FUNC A 
   ON A.DTE_MODL_YR = B.DTE_MODL_YR
INNER JOIN GUEST.FED_MODL_YR_PIECE C 
   ON A.DTE_MODL_YR = C.DTE_MODL_YR 
INNER JOIN GUEST.FED_MODL_FUNC_PIECE D
   ON A.DTE_MODL_YR = D.DTE_MODL_YR
  AND B.CDE_PEER = D.CDE_PEER 
  AND B.CODEVEH_TYP = D.CODEVEH_TYP 
  AND A.CODEFUNC = D.CODEFUNC 
  AND C.NUM_PIECE = D.NUM_PIECE
INNER JOIN GUEST.VEH_PEER_REF E
   ON B.CDE_PEER = E.CDE_MODL_SERIES || E.CDE_MODL_ENGINE
WHERE A.DTE_MODL_YR BETWEEN V_DTE_MODL_YR_FROM AND V_DTE_MODL_YR_TO
  AND D.CODEFUNC BETWEEN V_CODEFUNC_FROM AND V_CODEFUNC_TO 
  AND B.CDE_PEER BETWEEN V_CDE_PEER_FROM AND V_CDE_PEER_TO 
  AND C.NUM_PIECE BETWEEN V_NUM_PIECE_FROM AND V_NUM_PIECE_TO
  AND D.QTY_FED_LBR_TME_UN = 0;
--  AND RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;

Open in new window

Premalatha

ASKER

Yes . I executed the above query and got 11133 rows in the result set.......

Premalatha

ASKER

and it is executing fast . performance is good ..........

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Premalatha

ASKER

I am getting the same number of rows..11133

but the required number of rows = 3512 ( as per the old query) The execution time is also slow ..!!

Kent Olsen

I wouldn't expect much performance degradation scanning 11,000 rows to select 3,500. Odd.

What are the values of V_REC_START_RANGE and V_REC_END_RANGE?

Kent

Premalatha

ASKER

Actually selection of all the rows execute faster. I am counting the rows prior to the declaration of cursor by count(*) which takes more time.
The range is from 1 to 2147483647 ( Integer Upper limit value)

Kent Olsen

I assume that that is for testing purposes and you'll really modify the query to select only a specific range of rows?

Premalatha

ASKER

No...Its for an real time application which is about to be run in production this year end...

The actual front end application is in Java . There is a search screen where the stored procedure is called each time the user hits "search" button and the purpose of range values - The entire result set cannot be displayed in a single page . So every time the user switches between pages, the stored procedure is called and the number of records for that page alone is returned .

Premalatha

ASKER

actually, i tried by giving the same size to the From and To variables similar to that in the database. It works 90% better now...
ie., Initially i declared varchar(100) for the V_NUM_PIECE_FROM variable. now i changed it to char(24), the field size of num_piece in the table.

Premalatha

ASKER

Actually the drill down method helped me. Thanks a lot...!
:-)