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;
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.
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.
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.
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;
