Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Re-write queries to minimize the effect of POWER and SUM.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Mon Aug 04, 2014 1:49 pm    Post subject: Re-write queries to minimize the effect of POWER and SUM.
Reply with quote

Hi,

I'm trying to optimize the following queries to minimize the effect of POWER and SUM function in CPU consumption for the following queries but not getting a way to do it. Can someone please suggest a good way to write them:

Query 1:

Code:
SELECT COL3, COL2, AANTAL_FONDSPOSITIE, COL4,DESCRIPTION AS A
  FROM (SELECT ROW_NUMBER() OVER ( ORDER BY C.COL3 , C.COL2 , C.COL4 ASC) AS RUNNING_NO,
  C.COL3, C.COL2, SUM(C.NOMINAL_AANTAL) AS AANTAL_FONDSPOSITIE,
  C.COL4, D.OMSCHRIJVING
          FROM
               (SELECT A.COL1, A.COL3, A.COL2, POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL,
  A.COL4, A.COL6, A.BOOK_TIME
                  FROM (
                      SELECT MAX(COL5) AS BOOK_TIME,
                        COL1, COL3, COL2, COL4,
                        COL6
                        FROM UU01.VERSION_TABLE
                        WHERE COL2 = '123456'
    AND COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999'
                        GROUP BY COL6, COL1,
                          COL3, COL2, COL4) AS A,
                       UU01.VERSION_TABLE AS B
                  WHERE A.COL3 = B.COL3 AND A.COL2 = B.COL2
    AND A.COL4 = B.COL4 AND B.COL5 = A.BOOK_TIME
    AND B.COL2 = '123456'
    AND B.COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999') AS C,
               UU01.SOME_CONFIG_TABLE AS D
          WHERE C.COL3 = D.COL3
          GROUP BY C.COL3, C.COL2, C.COL4,
            D.DESCRIPTION
          FETCH FIRST 10 ROWS ONLY) AS FL
  WHERE RUNNING_NO BETWEEN 1 AND 10;


Query 2:
Code:
SELECT COL3, COL2, AANTAL_FONDSPOSITIE, COL4,
  OMSCHRIJVING
  FROM (SELECT ROW_NUMBER() OVER ( ORDER BY RK.COL3 , RK.COL2 , RK.COL4 ASC) AS RUNNING_NO,
  RK.COL3, RK.COL2, SUM(RK.NOMINAL_AANTAL) AS AANTAL_FONDSPOSITIE,
  RK.COL4, L.DESCRIPTION
          FROM (SELECT A.COL1, A.COL3, A.COL2, POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL,
  A.COL4, A.COL6, A.BOOK_TIME
                  FROM (
                      SELECT MAX(COL5) AS BOOK_TIME,
                        COL1, COL3, COL2, COL4,
                        COL6
                        FROM UU01.VERSION_TABLE
                        WHERE COL2 = '123456'
                        GROUP BY COL6, COL1,
                          COL3, COL2, COL4) AS A,
                       UU01.VERSION_TABLE AS B, UU01.CONFIG_TABLE_1 AS C,
                       UU01.CONFIG_TABLE_2 AS E, UU01.CONFIG_TABLE_3 AS F
                  WHERE C.REKENINGNUMMER = A.COL1 AND C.REKENINGNUMMER_UP = E.REKENINGNUMMER
    AND E.CONDITIE_NUMMER = 90 AND F.STUURGEGEVEN_ID = 1400
    AND F.SERV_CONC_ID = SUBSTR(E.WAARDE_TEKST, 1, 4) AND F.WAARDE_TEKST = 'XXXX'
    AND A.COL3 = B.COL3 AND A.COL2 = B.COL2
    AND A.COL4 = B.COL4 AND B.COL5 = A.BOOK_TIME
    AND A.COL1 = B.COL1 AND B.COL2 = '123456') AS RK,
               UU01.SOME_CONFIG_TABLE AS L
          WHERE RK.COL3 = L.COL3
          GROUP BY RK.COL3, RK.COL2, RK.COL4,
            L.DESCRIPTION
          FETCH FIRST 10 ROWS ONLY) AS FL
  WHERE RUNNING_NO BETWEEN 1 AND 10; 
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Aug 04, 2014 9:51 pm    Post subject:
Reply with quote

Query 1:
1) Is below piece is extra/redundant?
Code:
                 UU01.VERSION_TABLE AS B
                  WHERE A.COL3 = B.COL3 AND A.COL2 = B.COL2
    AND A.COL4 = B.COL4 AND B.COL5 = A.BOOK_TIME
    AND B.COL2 = '123456'
    AND B.COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999'

and replace by in single select?
Code:

SELECT MAX(COL5) AS BOOK_TIME,
                        COL1, COL3, COL2, COL4,
                        COL6,power(B.COL7, B.COL8) AS NOMINAL_AANTAL
                        FROM UU01.VERSION_TABLE
                        WHERE COL2 = '123456'
    AND COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999'
                        GROUP BY COL6, COL1,
                          COL3, COL2, COL4


2) Instead of Between you can use < and > condition.
3) and most importantly did you talk you your DBA and got any explain results ? if not then that is the first step to get it and then investigate from there to know which part of the query is expensive also check when did last reorg happen on these tables.
Back to top
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Wed Aug 06, 2014 6:52 pm    Post subject:
Reply with quote

Thanks.

The query as written is giving the expected results so I think the part is not redundant however it can be written better for sure. In the process, I'm trying to see if I can somehow construct it better as POWER and MAX functions are being used. This query took around 2 minutes of CPU (both queries) just for 15K rows in table.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Aug 07, 2014 12:34 am    Post subject:
Reply with quote

Quote:
The query as written is giving the expected results so I think the part is not redundant however it can be written better for sure.


You are just speculating the things here but can you please put some efforts to asnwer all the 4 points that I have mentioned, did you try once?
Back to top
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Thu Aug 07, 2014 12:26 pm    Post subject:
Reply with quote

Rohit - I don't want to sound rude but you're unnecessarily killing the thread.

The original query is written by someone else and I'm trying to get it corrected. And off hand, the original query is bad because:

  • there is no garantee that the result will always be as per "order by". If it's there or not, it does not matter.
  • the original coder has limit the result twice : by "fetch first 10" AND "RUNNING_NO between 1 and 10", that's plain useless.
  • I've EXPLAIN results with me but they are totally useless from a query re-write per se. DBA said, the query took time because the POWER and SUM functions are used, Period. That's a technical correct answer but I can not make much use of it.
Now with all this, I've re-written the query but I'm stuck how to make use of POWER and SUM in the same SELECT for the same column: the below is re-write for first query:

Quote:
SELECT COL3, COL2, AANTAL_FONDSPOSITIE, COL4,DESCRIPTION, SUM(C.NOMINAL_AANTAL) AS AANTAL_FONDSPOSITIE, OMSCHRIJVING
FROM UU01.VERSION_TABLE A
WHERE COL2 = '123456'
AND COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999'
AND book_time = (select max(col5), POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL,
FROM UU01.VERSION_TABLE B
WHERE A.COL3 = B.COL3
AND A.COL2 = B.COL2
AND A.COL4 = B.COL4 AND B.COL5 = A.BOOK_TIME
FETCH FIRST 10 ROWS ONLY


The use of POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL, above is not correct and I'm not sure how can I correct it to align with the original query's results/s; and this is where I'd need help.

To answer your questions:
- The part is redundant, you're correct.
- "< >" and between - both are just fine, not much difference.
- I've already listed the response from DBA.

I think you had only 3 points and not 4? Please let me know if I missed something.
Back to top
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Thu Aug 07, 2014 1:50 pm    Post subject:
Reply with quote

And this is the message from the query you suggest:

Code:
COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID. SQLCODE=-122, SQLSTATE=42803, DRIVER=4.8.86

power(B.COL7, B.COL8) are as part of select but is not in group by clause.

Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Aug 07, 2014 8:31 pm    Post subject:
Reply with quote

Quote:
Rohit - I don't want to sound rude but you're unnecessarily killing the thread.


I am not a decision maker to kill anyone's thread. You are in help here so take advice from other's , if you like it keep it else move on to other options.

However below points you should have listed while posting your original thread,
Quote:
1)there is no garantee that the result will always be as per "order by". If it's there or not, it does not matter.
When you know it does not matter then simply remove the order by clause
Quote:
2)the original coder has limit the result twice : by "fetch first 10" AND "RUNNING_NO between 1 and 10", that's plain useless.

This looks like written as per the requirement (that time what requested) and now if this is no more valid then you need to mention the new requirement.
Quote:
I've EXPLAIN results with me but they are totally useless from a query re-write per se. DBA said, the query took time because the POWER and SUM functions are used, Period. That's a technical correct answer but I can not make much use of it.


When you know you have to rewite your query then please list the new requirement and try. But Rewitting the query won't always reduce CPU than making proper indexing, cardinality of the joins.

Quote:
The use of POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL, above is not correct and I'm not sure how can I correct it to align with the original query's results/s; and this is where I'd need help.


When you determined this is not correct anymore then why can't you remove it

Quote:
I think you had only 3 points and not 4? Please let me know if I missed something.


How about last reorg on this table?

Quote:
COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID. SQLCODE=-122, SQLSTATE=42803, DRIVER=4.8.86

power(B.COL7, B.COL8) are as part of select but is not in group by clause.


You don't put these col7 and col8 in group by.

Quote:
Now with all this, I've re-written the query but I'm stuck how to make use of POWER and SUM in the same SELECT for the same column: the below is re-write for first query:


Code:
SELECT COL3, COL2, AANTAL_FONDSPOSITIE, COL4,DESCRIPTION, SUM(C.NOMINAL_AANTAL) AS AANTAL_FONDSPOSITIE, OMSCHRIJVING
FROM UU01.VERSION_TABLE A
WHERE COL2 = '123456'
AND COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999'
AND book_time = (select max(col5), POWER(B.COL7, B.COL8) AS NOMINAL_AANTAL,
FROM UU01.VERSION_TABLE B
WHERE A.COL3 = B.COL3
AND A.COL2 = B.COL2
AND A.COL4 = B.COL4 AND B.COL5 = A.BOOK_TIME
FETCH FIRST 10 ROWS ONLY


Where is GROUP BY in your query?

However I did not provide you any query bt a thought before. But you may wants refer to what I was telling,
Code:
 SELECT COL3, COL2, AANTAL_FONDSPOSITIE, COL4,DESCRIPTION AS A
  FROM (SELECT ROW_NUMBER() OVER ( ORDER BY a.COL3 , a.COL2 , a.COL4 ASC) AS RUNNING_NO,
  a.COL3, a.COL2, SUM(a.NOMINAL_AANTAL) AS AANTAL_FONDSPOSITIE,
  a.COL4, D.OMSCHRIJVING
          FROM
               (SELECT A.COL1, A.COL3, A.COL2, POWER(a.COL7, a.COL8) AS NOMINAL_AANTAL,
                A.COL4, A.COL6,max(col5)
                   from UU01.VERSION_TABLE AS a     
                WHERE B.COL2 = '123456'
                  AND B.COL6 BETWEEN '2014-01-17 23:59:59.999999' AND '2014-07-01 23:59:59.999999') AS A,
               UU01.SOME_CONFIG_TABLE AS D
          WHERE a.COL3 = D.COL3
          GROUP BY a.COL1, a.COL2, a.COL3, a.COL4, a.COL6,
            D.DESCRIPTION
          FETCH FIRST 10 ROWS ONLY) AS FL
  WHERE RUNNING_NO BETWEEN 1 AND 10;
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm
No new posts how to DYNAMICALLY write sort cond to... mohamedmubee COBOL Programming 13 Fri Aug 26, 2016 11:46 pm
No new posts Compare 2 files & write Indicator... nagendrapgupta SYNCSORT 5 Sun May 15, 2016 10:42 am
No new posts DFSORT to match two files and write John F Dutcher DFSORT/ICETOOL 6 Thu May 12, 2016 5:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us