Query optimize

prem18 · #1 (**permalink**) 11-17-08, 10:58

Can anyone suggest me to optimize the below query? Its taking around 20 sec for 3500 rows

Code:

                           SELECT * FROM (SELECT A.CDE_ECR_VEMS,
                                                 A.IND_ECR_VEMSS_EMSN,
                                                 A.IND_ECR_VEMSS_CARB,
                                                 A.IND_ECR_CHPP_DEF,
                                                 A.IND_ECR_LPP_DEF,
                                                 D. IND_ECR_CHHP AS IND_TRAN_CHHP,
                                                 D.IND_ECR_PHPP AS IND_TRAN_PHPP,
                                                 A.IND_ECR_PART_ASMB,
                                                 A.CDE_ECR_VEMSS,
                                                 B.DTE_MODL_YR,
                                                 E.DES_MODL,
                                                 B.CDE_BAUM,
                                                 C.NUM_PART,
                                                 B.CDE_ECR_VEH_TYP,
                                                 A.DES_ECR_VEMSS_EXT,
                                                 COALESCE(C.AMT_ECR_PART_RETL,0) AS AMT_ECR_PART_RETL,
                                                 COALESCE(C.TXT_ECR_PART_COM,'') AS TXT_ECR_PART_COM,
                                                 COALESCE(D.CDE_STME_DSGN_GRP,'')||COALESCE(D.CDE_STME_OPER,'') AS OPCODE,
                                                 replace(substr(char(CAST(D.QTY_ECR_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.')
                                                                                                                AS QTY_ECR_LBR_TME_UN,
                                                 COALESCE(D.TXT_ECR_LBR_COM,'') AS TXT_ECR_LBR_COM,
                                                 COALESCE(D.CDE_ECR_PART_CST,'') AS CDE_ECR_PART_CST,
                                                 ROWNUMBER() OVER (ORDER BY B.DTE_MODL_YR DESC) AS RN
                           FROM  ECRUSER.ECR_VEH_GRP B, ECRUSER.ECR_VEMSS A, ECRUSER.ECR_MODL_YR_PART C, ECRUSER.ECR_MODL_VEMSS_PART D,
                           ECRUSER.VEH_BAUM_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_BAUM = D.CDE_BAUM AND
                           B.CDE_ECR_VEH_TYP = D.CDE_ECR_VEH_TYP AND A.CDE_ECR_VEMSS = D.CDE_ECR_VEMSS AND C.NUM_PART = D.NUM_PART
                           AND B.CDE_BAUM = 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.CDE_ECR_VEMSS BETWEEN V_CDE_ECR_VEMSS_FROM AND V_CDE_ECR_VEMSS_TO AND
                           B.CDE_BAUM BETWEEN V_CDE_BAUM_FROM AND V_CDE_BAUM_TO AND
                           C.NUM_PART BETWEEN V_NUM_PART_FROM AND V_NUM_PART_TO)
                           AS RES WHERE RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;

stolze · #2 (**permalink**) 11-18-08, 10:34

Since you didn't provide any details on which version of DB2 you are using on which platform, we cannot say anything besides common-sense things. You should have a look at the access plan to see whether index scans or table(space) scans are used. Also, have you collected statistics, etc. So the regular DB2 performance tuning rules apply, and you can find those in the manuals.

prem18 · #3 (**permalink**) 11-18-08, 13:13

Am using DB2 v8.

Actually I the requirement is to see whether any row exists for the cursor ( given above in my post) .
if exists then proceed to open the cursor for retrieving resultsets.

Actually I am doing count(*) over the whole query and then repeating the query to retrieve result sets. I admit that its a stupid program !!! but i couldnt find any clues in online materials or tutorials for

1.checking existence of a cursor and then 2. open the resultsets.
Please advise for any better way to meet the reqt.

I found your replies for the similar case ..
http://www.mydatabasesupport.com/for...69803-how-get-
row-number-cursor.html

prem18 · #4 (**permalink**) 11-18-08, 13:24

Sorry. Not only i need to check the existence of rows in the cursor but also I need the rowcount to return it to the user.

stolze · #5 (**permalink**) 11-18-08, 16:56

Executing the query twice will potentially give you wrong results anyway. The only reliable approach is use RR isolation level (which avoids phantom reads), which is a good way to reduce concurrency of the system. So why bother with the exact count in the first place? I think the discussion you cited sums up the issues quite nicely (if not completely). You should either rework your design - or ask your teacher what the purpose of the question should be in case it is an exercise.

What I don't understand is the part where you want to "check existence of a cursor and then open result sets". You can get (opened) cursors returned from stored procedures, and there are documented ways how to access them. Likewise, when you execute a query, you get a cursor over the result set. (Existence of rows referenced by the cursor can be checked by fetching from the cursor.) Aside from that, you should probably explain in detail what you want to do and also why.

p.s: Google is only giving you an estimate on the number of hits. There is rarely the need for a precise count up front.

prem18 · #6 (**permalink**) 11-18-08, 17:12

I could not find the db2 substitute for Oracle's %rowcount.
In this complex "search" query. I am using cursors. I need to count the total number of records returned by the cursor(- for the purpose of returning to the end user) 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

stolze · #7 (**permalink**) 11-18-08, 17:29

Let's try to get some terminology straight. A cursor is just like an iterator. It operates on a result set. What you want to know is the total number of rows in the result set, i.e. the total number of rows the cursor can be positioned on. The cursor doesn't return anything here - you can use it by positioning the cursor on a row in the result set and then fetch this single row.

Oracle's rowcount must materialize the result set too, or it will not be able to give you a reliable count of the number of rows in the result set. The reason is simply that the system does its table/index scans and returns the rows being found as it goes along. Basically, that's a traditional pipeline/queue approach - you only know all you got once the either end is done adding new things. Of course, there are situations where the result set has to be materialized anyway, for example when an ORDER BY occurs and you don't combine it with a FETCH FIRST or some other windowing operation. Any such windowing operation (like you are doing as well) allows the system to only remember the first "n" rows and forget everything else. Thus, you wouldn't necessarily get a reliable row count either.

Regarding your performance question (you seem to be jumping back and forth between performance and semantical question without relating how they tie together!), I can only repeat what I said before here and in some other thread: have a look at the access plan to figure out what the system is doing. Make sure your system is properly tuned (run the DB2 Configuration Advisor to get a good base line), create any necessary indexes, collect statistics... the whole works. We cannot be more specific simply because you don't provide more details.

(Also, you should try to scale-down your query to the bare minimum that still exhibits the slow performance but also reduces complexity.)

Peter.Vanroose · #8 (**permalink**) 11-19-08, 12:50

Quote:

Originally Posted by prem18

...
B.CDE_BAUM = E.CDE_MODL_SERIES || E.CDE_MODL_ENGINE
...

This could potentially be the culprit of an inefficient access path, certainly when it does a lot of filtering: a "concat" in a predicate makes it non-indexable, hence the amount of work for your query will be comparable (and even a bit higher) than for the query where this predicate is removed.
I.e.: not the "3500" is relevant, but rather the number of rows returned when the above predicate would be removed.
All this should be clear from running EXPLAIN, assuming you have all normal statistics info available.

Internet Services

Web Development

Digital Marketing

Consumer Tech