question

jayakrishnan.r avatar image
jayakrishnan.r asked

How to reduce the execution time of sql query

Dear Programmers Below is an sql query that i created, this query takes more tham 4 minutes for execution. is there any way to reduce the execution time of my query SELECT * , ( SELECT SUM(dbo.tblOutletStockReturnToWareDetails.retQty) AS RetnQty FROM dbo.tblOutletStockReturnToWareDetails INNER JOIN dbo.tblOutletStockReturnToWareMaster ON dbo.tblOutletStockReturnToWareDetails.refNo = dbo.tblOutletStockReturnToWareMaster.refNo WHERE ( dbo.tblOutletStockReturnToWareMaster.Status = 'W' ) AND ( dbo.tblOutletStockReturnToWareDetails.ProCode = A.Procode ) ) AS RetnQty , ( SELECT SUM(dbo.tblSubstoreStockReturnToWareDetails.retQty) AS RetnQty FROM dbo.tblSubstoreStockReturnToWareDetails INNER JOIN dbo.tblSubstoreStockReturntoWareMaster ON dbo.tblSubstoreStockReturnToWareDetails.refNo = dbo.tblSubstoreStockReturntoWareMaster.refNo WHERE ( dbo.tblSubstoreStockReturntoWareMaster.Status = 'W' ) AND ( dbo.tblSubstoreStockReturnToWareDetails.ProCode = A.Procode ) ) AS SubRetnQty , ( SELECT SUM(Qty) FROM tblSalesTable WHERE outletCode = '1' AND ProCOde = A.Procode ) AS OLCURSTOCK , ( SELECT SUM(Qty) FROM VIEWORDEROUTLETPIPEQTY WHERE outletCode = '1' AND Sts = 'W' AND ProCode = A.ProCode ) AS OLCURPIPESTOCK , ( SELECT rolQty FROM tblProLocation WHERE outCode = '1' AND Procode = A.Procode ) AS ProROL , ( SELECT rolStatus FROM tblProLocation WHERE outCode = '1' AND Procode = A.Procode ) AS ProROLSts , ( SELECT maxQty FROM tblProLocation WHERE outCode = '1' AND Procode = A.Procode ) AS ProMaxQty , ( SELECT SUM(PDBalanceStock) FROM tblPurchaseDetailsTemp WHERE ProCode = A.Procode ) AS PURCHASEPIPELINEQTY1 , ( SELECT SUM(PDBalanceStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.Procode AND PMSts = 'W' ) AS PURCHASEPIPELINEQTY2 , ( SELECT SUM(PDBalanceStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.Procode AND PMSts = 'A' ) AS BALANCEQTY , ( SELECT SUM(PDStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.ProCode ) AS TOTALPURCHASE , ( SELECT TOP 1 [PDPRate] FROM tblPurchaseDetails WHERE ProCode = A.ProCode ORDER BY PDID DESC ) AS PRATE , ( SELECT SUM(Qty) FROM tblSalesTable WHERE ProCode = A.ProCode ) AS COUNTERSTOCK , ( SELECT SUM(EdtQty) FROM tblItemIssueDetailsSubStore WHERE ProCode = A.ProCode ) AS SUBSTORESTOCK , ( SELECT SUM(Qty) FROM AViewOrderROL WHERE ProCode = A.ProCode AND issueDate >= '2013-04-17 12:16:26 PM' AND issueDate <= '2013-05-17 12:16:33 PM' ) AS SALESQTY , ( SELECT SUM(Qty) FROM AViewOrderROLSubstore WHERE ProCode = A.ProCode AND issueDate >= '2013-04-17 12:16:26 PM' AND issueDate <= '2013-05-17 12:16:33 PM' ) AS COUNTERSALESQTY , ( SELECT TOP ( 100 ) PERCENT SUM(dbo.tblBillDetails.Qty) FROM dbo.tblBillMaster INNER JOIN dbo.tblBillDetails ON dbo.tblBillMaster.BillDispNumber = dbo.tblBillDetails.BillNo WHERE ( dbo.tblBillDetails.Pcode = A.Procode ) AND ( dbo.tblBillMaster.Series = 'Cash' ) AND ( dbo.tblBillMaster.OutletCode = '1' ) GROUP BY dbo.tblBillDetails.Pcode ) AS CASHSALES , ( SELECT TOP ( 100 ) PERCENT SUM(dbo.tblBillDetails.Qty) FROM dbo.tblBillMaster INNER JOIN dbo.tblBillDetails ON dbo.tblBillMaster.BillDispNumber = dbo.tblBillDetails.BillNo WHERE ( dbo.tblBillDetails.Pcode = A.Procode ) AND ( dbo.tblBillMaster.Series = 'Credit' ) AND ( dbo.tblBillMaster.OutletCode = '1' ) GROUP BY dbo.tblBillDetails.Pcode ) AS CREDITSALES , ( SELECT TOP ( 100 ) PERCENT SUM(dbo.tblBillDetails.Qty) FROM dbo.tblBillMaster INNER JOIN dbo.tblBillDetails ON dbo.tblBillMaster.BillDispNumber = dbo.tblBillDetails.BillNo WHERE ( dbo.tblBillDetails.Pcode = A.Procode ) AND ( dbo.tblBillMaster.OutletCode = '1' ) GROUP BY dbo.tblBillDetails.Pcode ) AS TOTALSALES FROM dbo.VIEWPURCHASEORDERROL AS A WHERE ProStatus = 'Active' AND rolStatus = 'True' AND OutCode = '1' ORDER BY ProName Any suggestions are highly thankfull Thank you in advance
execution-count
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Reformatted code to make it easier to see what's going on, using http://www.format-sql.com
0 Likes 0 ·
ruancra avatar image
ruancra answered
Check the execution plan for this query to determine what can be done to improve performance.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jayakrishnan.r avatar image jayakrishnan.r commented ·
Dear ruancra, Can you please tell me how it can be checked or if it is difficult can you please share any usefull link to find that
0 Likes 0 ·
jayakrishnan.r avatar image jayakrishnan.r commented ·
Thank You.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Looking at it a bit more closely, now I can read it, it might be better to try replacing the three SUMs from the BillDetails table with a single run through that table: SELECT a.*, bd.* FROM ...the rest of your FROM list LEFT JOIN ( SELECT SUM(CASE WHEN tblBillmaster.Series='Cash' THEN BilLDetails.Qty ELSE 0 END) AS CashSales, SUM(CASE WHEN tblBillmaster.Series='Credit' THEN BilLDetails.Qty ELSE 0 END) AS CreditSales, SUM(BillDetails.Qty) AS TotalSales FROM tblBillMaster Inner join tblbilldetails... ) AS bd At least that way you're only getting one pass through the Billmaster/BillDetails JOIN. You can apply the same technique on the `VIEWPURORDERPURCHASEQTY`
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jayakrishnan.r avatar image jayakrishnan.r commented ·
Thank You ThomasRushton..... Let me have a try on your suggestion
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
What can't be seen here is what that view is doing. Does it contain tables only? Or, is it calling other views in a nested fashion? If the latter, that's a common code smell. You should not attempt to use SQL Server objects like .NET/Java/Whatever objects. They don't work the same way. If there are nested views, break them out and rewrite to access only the tables you need, directly. Also, look at the execution plan ([details on how to do that here][1]) and check the first operator. Look for the Reason for Early Termination. See if it says timeout. If so, your query needs to be simplified for the optimizer to resolve (possibly another indication of nested views). [1]: http://www.red-gate.com/community/books/sql-server-execution-plans-ed-2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.