Can we improve it?

Question

Hi,

Can someone help me in optimizing below query, is there any other way where I can improve this query

SELECT A.Col1, COUNT(B.POST_DT) AS OD_NO
        FROM (SELECT Col1, col3, Col2,
                SUM(CASE WHEN Col2> 0 THEN GROSS_AMOUNT ELSE DUEAMNT END) AS DUEAMNT,
                SUM(CRAMNT) AS CRAMNT FROM myTbl
                WHERE POST_DT< someDate  AND Col2=0
                    GROUP BY Col1, col3, Col2
                    HAVING SUM(DUEAMNT) > SUM(CRAMNT)
                    ORDER BY Col1) A,
    myTbl B
    WHERE A.col3=B.col3
    AND (CASE WHEN B.Col2> 0 THEN B.GROSS_AMOUNT ELSE B.DUEAMNT END)>0
    GROUP BY A.Col1


TIA

---

I am learning .Net, so corrections/suggestions to my posts are highly appreciated. ----------------------------------------------- surinder singh

Answer 1

I think that query can be simplified to this equivalent query: 

with A as
(
 SELECT Col1, col3
 FROM myTbl
 WHERE POST_DT < someDate  
 GROUP BY Col1, col3
 HAVING SUM(DUEAMNT) > SUM(CRAMNT) 
)
SELECT A.Col1, COUNT(B.POST_DT) AS OD_NO 
FROM  A
join myTbl B 
  on  A.col3=B.col3 
where (B.Col2 > 0 AND B.GROSS_AMOUNT > 0) or B.DUEAMNT > 0
GROUP BY A.Col1

which is at least simpler. No idea if it runs better.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Could you try this -

SELECT     A.Col1,
           COUNT(B.POST_DT) AS OD_NO
FROM       (SELECT  Col1                   ,
                    Col3                   ,
                    Col2                   ,
                    SUM(DUEAMNT) AS DUEAMNT,
                    SUM(CRAMNT)  AS CRAMNT
           FROM     myTbl
           WHERE    POST_DT < someDate
           AND      Col2   = 0
           GROUP BY Col1,
                    col3,
                    Col2
           HAVING   SUM(DUEAMNT) > SUM(CRAMNT)
           )                AS A
           INNER JOIN myTbl AS B
           ON         A.col3=B.col3
WHERE      (B.Col2             > 0
            AND B.GROSS_AMOUNT > 0)
		   OR
           (B.Col2        <= 0
            AND B.DUEAMNT > 0)
GROUP BY   A.Col1

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 3

You need to take a look at the execution plan and create indexes as required to ensure that the query is optimized. Optimization not only involves the way query is written but also the table and index design.

Regards,

---

Phaneendra Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 4

It is unlikely that a rewrite of this query will yield a better performance.

If this query needs better performance, then you should probably focus on indexes.

Also, you could inspect the query plan, and check if the estimates match the actual rows per step. If not, then splitting the query in two (where the first query would store the derived table's results in a temporary table) might solve that problem and improve performance.

So (1) what is the current performance, (2) how many rows does the derived table select, how many rows does myTbl have, and how many rows does the query return and (3) what does the query plan look like?

---

Gert-Jan