Optimizing query

Question

Post reply

Optimizing query

surindersinghthakur

SSC Veteran

Points: 299
More actions
February 27, 2013 at 8:28 pm

#269137

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

have you tried running it through DTA?

http://msdn.microsoft.com/en-us/library/ms162812.aspx

FYI, don't implement everything it recommends look on the reports for new indexes that have the most bang.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

you could read "SUM(CRAMNT) AS CRAMNT FROM myTbl" into a variable and then just select the variable instead of running sum for each row.

surindersinghthakur SSC Veteran Points: 299 More actions · Answer 3

Jon.Morisi (2/27/2013)
you could read "SUM(CRAMNT) AS CRAMNT FROM myTbl" into a variable and then just select the variable instead of running sum for each row.

Thanks but can you please guide me how to do that, thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Please read the first article I reference below in my signature block. Follow the instructions in that article regarding what you should post and how to post it to get the best answers quickly. We really do need the DDL, sample data, and expected results if you want good, quality answers and tested code to boot.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

surindersinghthakur (2/27/2013)
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

No, because this won't run - the inner select has ORDER BY without TOP etc. At a guess, this is a cut-down version of the real query. Any suggestions you might get for improving the performance would be guesses too. Follow the instructions Lynn posted, and attach the actual execution plan as a .sqlplan file.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden