Optimizing a query

• matzj

  Old Hand

  Points: 388

  More actions

  March 6, 2015 at 11:12 am

  #150328

  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!!

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  March 6, 2015 at 1:52 pm

  #1782282

  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/%5B/url%5D

  _______________________________________________________________

  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/