Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robayb
Starting Member
7 Posts |
 Posted - 2014-05-28 : 15:08:29
|
| I have adopted some very complex queries in my new job. The query below is an example of a slow running query that runs every day. I am wondering why the previous author is dropping tables and creating indexes in the query when the columns don't change. I have noticed that this is a common practice in his other queries too! Also all So my question is for general recommendations on how you would optimize this query for starters:DROP TABLE US_ProductSales_Last91 DROP TABLE US_ProductSales_AvailDays_Max91 DROP TABLE ADS_US_SKU_LAST91 DROP TABLE ADS_US_VC SELECT IL.Sku, SUM(IL.[Quantity Shipped]) AS Units, SUM(IL.[Extended Cost]) AS COGS, SUM(IL.[Quantity Shipped] * IL.[LN-REPL-COST]) AS InvValue INTO US_ProductSales_Last91 FROM IL INNER JOIN I ON IL.[Invoice Date] = I.[Invoice Date] AND IL.[Invoice Nbr] = I.[Invoice Nbr] WHERE (IL.[Invoice Date] > DATEADD(d, - 92, { fn CURDATE() })) AND (IL.[Line Type Code] = 'P') AND (I.[Order Type] IN ('S', 'C')) GROUP BY IL.Sku HAVING (IL.Sku <> '') CREATE INDEX SKULast91 ON US_ProductSales_Last91 (SKU ASC) SELECT SKU, Days INTO US_ProductSales_AvailDays_Max91 FROM (SELECT SKU, 91 AS Days FROM REC_MINDATE WHERE (MinRecDate < DATEADD(d, - 90, { fn CURDATE() }))UNION SELECT SKU, 7 AS Days FROM REC_MINDATE AS REC_MINDATE_2 WHERE (MinRecDate > DATEADD(d, - 8, { fn CURDATE() })) UNION SELECT SKU, DATEDIFF(d, MinRecDate, { fn CURDATE() }) AS Days FROM REC_MINDATE AS REC_MINDATE_1 WHERE (MinRecDate BETWEEN DATEADD(d, - 90, { fn CURDATE() }) AND DATEADD(d, - 8, { fn CURDATE() }))) AS derivedtbl_1 CREATE INDEX DaysBySKU ON US_ProductSales_AvailDays_Max91 (SKU) SELECT US_ProductSales_Last91.Sku, US_ProductSales_AvailDays_Max91.Days, US_ProductSales_Last91.Units, US_ProductSales_Last91.COGS, US_ProductSales_Last91.InvValue, CAST(Units AS float) / US_ProductSales_AvailDays_Max91.Days AS Units_Daily, COGS / US_ProductSales_AvailDays_Max91.Days AS COGS_Daily, InvValue / US_ProductSales_AvailDays_Max91.Days AS InvValue_Daily INTO ADS_US_SKU_LAST91 FROM US_ProductSales_Last91 LEFT OUTER JOIN US_ProductSales_AvailDays_Max91 ON US_ProductSales_Last91.Sku = US_ProductSales_AvailDays_Max91.SKU ORDER BY US_ProductSales_Last91.Sku CREATE INDEX SalesBySKU ON ADS_US_SKU_LAST91 (SKU) SELECT IL.[Vendor Nbr], SUM(IL.[Extended Cost] / 91) AS COGS, SUM(CASE WHEN [Quantity Shipped] <> 0 THEN [Quantity Shipped] * [LN-REPL-COST] / 91 ELSE 0 END) AS InvValue INTO ADS_US_VC FROM IL INNER JOIN I ON IL.[Invoice Date] = I.[Invoice Date] AND IL.[Invoice Nbr] = I.[Invoice Nbr] WHERE (IL.[Invoice Date] > DATEADD(d, - 92, { fn CURDATE() })) AND (IL.[Line Type Code] = 'P') AND (I.[Order Type] IN ('S', 'C')) GROUP BY IL.[Vendor Nbr] |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-29 : 01:47:29
|
| One best practise is to avoid DDL and DML statements in the same transactions - forcing a recompilationJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
|
|
|
|
|
Best Practices