March 6, 2015 at 11:12 am
I have a query which sums up a couple fields from different tables, it also has a few things in the WHERE clause for what I don't want to see. When I use the query in a SSRS report the report takes a while to run. Is there a way I can optimize the query to make it run faster? Here is my current query:
SELECT
DK_TM054_SALESREPS.T054_CODE,
IsNull((Select SUM(sOH.SO_Total)
FROM SOH
LEFT JOIN SOD
ON SOD.So_Nbr = soh.ID
WHERE soH.sales_Rep_Code = DK_TM054_SALESREPS.T054_CODE
AND soh.Cust_Nbr != '116665'
AND soh.Cust_Nbr != '117350'
AND soh.Cust_Nbr != '218012'
AND soh.Cust_Nbr != '222545'
AND soh.Cust_Nbr != '227620'
AND soh.Cust_Nbr != '231180'
AND soh.Cust_Nbr != '251149'
AND soh.Cust_Nbr != '107768'
AND sod.INV_TYPE = 'FG'
AND soh.SO_Date >=(@BegDate)
AND soh.SO_Date <= (@EndDate)),0) as SO_Total,
IsNull((Select SUM(arh.INV_AMOUNT)
FROM ARH
LEFT JOIN ARID
ON ARID.Invoice_Nbr = ARH.ID
WHERE ARH.sales_Rep_Code = DK_TM054_SALESREPS.T054_CODE
AND arh.Cust_No != '116665'
AND arh.Cust_No != '117350'
AND arh.Cust_No != '218012'
AND arh.Cust_No != '222545'
AND arh.Cust_No != '227620'
AND arh.Cust_No != '231180'
AND arh.Cust_No != '251149'
AND arh.Cust_No != '107768'
AND ARID.INV_TYPE = 'FG'
AND arh.Invoice_Date >= (@BegDate)
AND arh.Invoice_Date <= (@EndDate)),0) as INV_Total
FROM DK_TM054_SALESREPS
WHERE DK_TM054_SALESREPS.T054_CODE IN (@RepCode)
ORDER BY DK_TM054_SALESREPS.T054_CODE
Any help would be very appreciated!! Thanks!!
March 6, 2015 at 1:52 pm
Your code has a lot of nonSARGable predicates. Then it is compounded by what is essentially an inline scalar function. I am not at all surprised the performance is less than stellar. In order to help we need to see some more details. Take a look at this article which explains what you need to post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply