• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1880
  • Last Modified:

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.
0
Premalatha
Asked:
Premalatha
  • 14
  • 9
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
PremalathaAuthor Commented:
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

0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
PremalathaAuthor Commented:
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

0
 
PremalathaAuthor Commented:
Sorry. Ignore my prev reply. Actually im not getting any error. however the performance is the same ( taking too long to execute)
0
 
PremalathaAuthor Commented:
Already I am retrieving the rownumber() in my original query....

0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
momi_sabagCommented:
how many rows are in each table?
what are the keys of each table?
0
 
PremalathaAuthor Commented:
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....



0
 
momi_sabagCommented:
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,''
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
momi_sabagCommented:
sorry
it doesn't make sense
did you try to create a descending index on DTE_MODL_YR ?
0
 
PremalathaAuthor Commented:

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......;-(
0
 
Kent OlsenData Warehouse Architect / DBACommented:

And this query takes 20 seconds?

What's the underlying hardware and O/S?



Kent
0
 
PremalathaAuthor Commented:
I am using Windows XP
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
PremalathaAuthor Commented:
Yes . I executed the above query and got 11133 rows in the result set.......

0
 
PremalathaAuthor Commented:
and it is executing fast . performance is good ..........
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  Now let's wrap it with an outer query to get the desired rows.


DECLARE RS1 CURSOR WITH RETURN FOR
SELECT *
FROM
(
  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
) t0
WHERE RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;
                             

Open in new window

0
 
PremalathaAuthor Commented:
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 ..!!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
0
 
PremalathaAuthor Commented:
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)

0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
PremalathaAuthor Commented:
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 .


0
 
PremalathaAuthor Commented:
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.


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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 14
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now