asked
SQL Server 2008 R2 Performance issue
Hi, we upgraded SQL server 2000 to 2008 r2, we are running the following Select statement(which is in Stored Procedure) in 2008 it takes a long time but it will not results any
thing.In Sql server 2000 it giving the result with in 20 mins. SELECT Case When Sales_PL.SBU Is NULL then '' Else Sales_PL.SBU End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Region_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Region_Name)) End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area)) End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area_Name)) End, Case When Sales_Detail.Customer Is NULL then '' Else Sales_Detail.Customer End, Case When Sales_Detail.Customer_Name Is NULL then '' Else Sales_Detail.Customer_Name End, Case When Sales_Detail.Item_Number Is NULL then '' Else Sales_Detail.Item_Number End, Case When Sales_Detail.Ship_to_State Is NULL then '' Else Sales_Detail.Ship_to_State End, Case When Sales_Detail.Product_Line Is NULL then '' Else Sales_Detail.Product_Line End, Case When Sales_Detail.OEM Is NULL then '' Else Sales_Detail.OEM End, Case When LTRIM(RTRIM(Sales_Detail.OEM_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.OEM_Name)) End, Case When Sales_Detail.Industry Is NULL then '' Else Sales_Detail.Industry End, Case When Sales_Detail.Site Is NULL then '' Else Sales_Detail.Site End, Case When Sales_Detail.Reporting_Site Is NULL then '' Else Sales_Detail.Reporting_Site End, Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End, Case When Sales_Part.Pt_Added Is NULL then '01/01/2000' Else Sales_Part.Pt_Added End, Case When Sales_Detail.Date_Shipped Is NULL then '01/01/2000' Else Sales_Detail.Date_Shipped End, Case When IA_DATES.DATE_KEY Is NULL then 0 Else IA_DATES.DATE_KEY End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area)) End, Case When LTRIM(RTRIM(Sales_Contcts.Slspsn_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Contcts.Slspsn_Name)) End, Case When Sales_Detail.Corp_ID Is NULL then '' Else Sales_Detail.Corp_ID End, Case When LTRIM(RTRIM(Sales_Detail.Corp_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Corp_Name)) End, Case When Sales_Detail.Order_No Is NULL then '' Else Sales_Detail.Order_No End, Case When Sales_Detail.Order_Line_No Is NULL then 0 Else Sales_Detail.Order_Line_No End, Case When LTRIM(RTRIM(Sales_Detail.Item_Description_1)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Item_Description_1)) End, Case When LTRIM(RTRIM(Sales_Detail.Cust_Part_No)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Cust_Part_No)) End, Case When Sales_Detail.Ship_to_Customer Is NULL then '' Else Sales_Detail.Ship_to_Customer End, Case When Sales_Detail.Ship_to_Customer_Name Is NULL then '' Else Sales_Detail.Ship_to_Customer_Name End, Case When LTRIM(RTRIM(Sales_Detail.Ship_to_Country)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Ship_to_Country)) End, Case When LTRIM(RTRIM(Sales_Detail.Ship_to_Region)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Ship_to_Region)) End, Case When Sales_Detail.Ship_to_Zipcode Is NULL then '' Else Sales_Detail.Ship_to_Zipcode End, Case When Sales_Detail.Sold_to_Zipcode Is NULL then '' Else Sales_Detail.Sold_to_Zipcode End, Case When Sales_Detail.PO_Num Is NULL then '' Else Sales_Detail.PO_Num End, Case When Sales_Detail.Invoice_Num Is NULL then '' Else Sales_Detail.Invoice_Num End, (SUM(Sales_Detail.Qty_Shipped)), (SUM(Sales_Detail.Sales_USD)), (SUM(Sales_Detail.Sales_USD)), CASE WHEN Case When LTRIM(RTRIM(Sales_Detail.Sales_Area)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area)) End = Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = 'LT' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = ',LT' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = '' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = 'NR' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = 'N' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = ',NR' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = '.NR' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End = 'CN' OR Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End IS NULL THEN ((SUM(Sales_Detail.Sales_USD)) * ISNULL(MAX(Sales_Contcts_Commission.Sales_Rep_Comm + Sales_Contcts_Commission.Design_Win_Comm),0)) ELSE ((SUM(Sales_Detail.Sales_USD)) * ISNULL(MAX(Sales_Contcts_Commission.Sales_Rep_Comm),0)) END, 0 FROM Sales_Detail LEFT OUTER JOIN Sales_Part ON LTrim(RTrim(Sales_Detail.Item_Number)) = LTrim(RTrim(Sales_Part.ItemNumber)) JOIN Sales_PL ON Sales_Detail.Product_Line = Sales_PL.PL_Key LEFT OUTER JOIN Sales_Contcts ON LTrim(RTrim(Sales_Detail.Sales_Area)) = LTrim(RTrim(Sales_Contcts.Slspsn)) LEFT OUTER JOIN Sales_Contcts_Commission ON LTrim(RTrim(Sales_Detail.Sales_Area)) = LTrim(RTrim(Sales_Contcts_Commission.Slspsn)) and (Case When Sales_PL.SBU Is NULL then '' Else Sales_PL.SBU End) = Sales_Contcts_Commission.PBU JOIN IA_DATES ON IA_DATES.CAL_DAY_DT = Sales_Detail.Date_Shipped WHERE Sales_Detail.Sales_Area NOT IN ('XX', -- SM: Removed per Tracy Kuhns on 6/18/2009 'DD','DD1','DD2','DD3','DD4','DD5', --'DN01','DN02','DN03','DN04','DN05','DN06', --'DN07','DN08','DN09','DN10', 'DN50','DN51','DN52', 'DN53') AND Sales_Detail.Date_Shipped >= '1/1/2008' GROUP BY Case When Sales_PL.SBU Is NULL then '' Else Sales_PL.SBU End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Region_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Region_Name)) End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area)) End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area_Name)) End, Case When Sales_Detail.Customer Is NULL then '' Else Sales_Detail.Customer End, Case When Sales_Detail.Customer_Name Is NULL then '' Else Sales_Detail.Customer_Name End, Case When Sales_Detail.Item_Number Is NULL then '' Else Sales_Detail.Item_Number End, Case When Sales_Detail.Ship_to_State Is NULL then '' Else Sales_Detail.Ship_to_State End, Case When Sales_Detail.Product_Line Is NULL then '' Else Sales_Detail.Product_Line End, Case When Sales_Detail.OEM Is NULL then '' Else Sales_Detail.OEM End, Case When LTRIM(RTRIM(Sales_Detail.OEM_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.OEM_Name)) End, Case When Sales_Detail.Industry Is NULL then '' Else Sales_Detail.Industry End, Case When Sales_Detail.Site Is NULL then '' Else Sales_Detail.Site End, Case When Sales_Detail.Reporting_Site Is NULL then '' Else Sales_Detail.Reporting_Site End, Case When LTRIM(RTRIM(Sales_Detail.designwin)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.designwin)) End, Case When Sales_Part.Pt_Added Is NULL then '01/01/2000' Else Sales_Part.Pt_Added End, Case When Sales_Detail.Date_Shipped Is NULL then '01/01/2000' Else Sales_Detail.Date_Shipped End, Case When IA_DATES.DATE_KEY Is NULL then 0 Else IA_DATES.DATE_KEY End, Case When LTRIM(RTRIM(Sales_Detail.Sales_Area)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Sales_Area)) End, Case When LTRIM(RTRIM(Sales_Contcts.Slspsn_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Contcts.Slspsn_Name)) End, Case When Sales_Detail.Corp_ID Is NULL then '' Else Sales_Detail.Corp_ID End, Case When LTRIM(RTRIM(Sales_Detail.Corp_Name)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Corp_Name)) End, Case When Sales_Detail.Order_No Is NULL then '' Else Sales_Detail.Order_No End, Case When Sales_Detail.Order_Line_No Is NULL then 0 Else Sales_Detail.Order_Line_No End, Case When LTRIM(RTRIM(Sales_Detail.Item_Description_1)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Item_Description_1)) End, Case When LTRIM(RTRIM(Sales_Detail.Cust_Part_No)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Cust_Part_No)) End, Case When Sales_Detail.Ship_to_Customer Is NULL then '' Else Sales_Detail.Ship_to_Customer End, Case When Sales_Detail.Ship_to_Customer_Name Is NULL then '' Else Sales_Detail.Ship_to_Customer_Name End, Case When LTRIM(RTRIM(Sales_Detail.Ship_to_Country)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Ship_to_Country)) End, Case When LTRIM(RTRIM(Sales_Detail.Ship_to_Region)) Is NULL then '' Else LTRIM(RTRIM(Sales_Detail.Ship_to_Region)) End, Case When Sales_Detail.Ship_to_Zipcode Is NULL then '' Else Sales_Detail.Ship_to_Zipcode End, Case When Sales_Detail.Sold_to_Zipcode Is NULL then '' Else Sales_Detail.Sold_to_Zipcode End, Case When Sales_Detail.PO_Num Is NULL then '' Else Sales_Detail.PO_Num End, Case When Sales_Detail.Invoice_Num Is NULL then '' Else Sales_Detail.Invoice_Num End
I like your question, because it suits well as a good example of how design issues are causing performance problems in the data retrieval process.
Did you updated your statistics after upgrade? if not upgrade your statistics using [UPDATE STATISTICS][1] . Because Query optimizer may produce poor execution plans based on outdated statistics and results in poor performance. Also check your fragmentation level of your indexes. As an additional step check out the execution plan for any missing indexes. [1]:
http://msdn.microsoft.com/en-us/library/ms187348(v=SQL.105).aspx
+1 I would accept your answer to the question, because the performance difference between the server is probably because of the statistics.
Is it the same server? You have more issues then the upgrade to SQL 2008r2. The query isn't performing well in any edition! 20 min for a query like this? You don't mention anything about environment or data load, but I would guess You can decrease the time consumed by doing it right.first of all, you have a design flaw if you need to strip any spaces. How many ltrim trim can you find? And then you are grouping on the cases and trim functions. I guess you have a lot of scans and consumes a lot of CPU
+1 definitely : bite the bullet, do a one-off clean up job now in the db and fix the interface so that it doesn't keep happening.
@Kev Abosuletly! I guess this is just one of many select statements with this kind of (nasty) logic, so the benefit of fixing the design is probably worth a lot of hard work.
@Håkan I'd be willing to bet that it takes less than 10 mins to update the lot!
@Kev It depends. :) They probably have this kind of design issue in the whole database, and a lot of code to review and test before they can put into production. But it is worth every second spent on doing it, and I agree with you, it will not take longer time to update the data then it takes to get it.
First of all, let me suggest that you try using the function `ISNULL()` instead of all those case statements. While ISNULL() will do the same thing as the case statement, it is *far* easier to read. Your next problem is that you are using functions in joins - this is not a recommended strategy as it can hinder the query optimiser from using indexes to satisfy the query. This can lead to table scans/clustered index scans (basically make SQL Server read the entire table that stores the data you need). This can cause a severe increase in I/O, Memory and CPU usage. The query also doesn't have schema qualification - this is something you should get into the habit of doing, as it does have a performance hit ([MSDN link][1]) You also have an inequality predicate in the where clause: `Sales_Detail.Date_Shipped >= '1/1/2008'` this will probably cause a table scan on your Sales_Detail table. If this table is large, then it may be quite expensive to perform this operation. For a little more help, I suggest you provide us with the table creation scripts (include any indexes) and also the execution plan of this query. The estimated execution plan can be retrieved by using the chord Ctrl-L when in SSMS - this will give you/us an idea of what is going on without having to wait for the query to complete. You will then get the graphical query plan displayed in SSMS which can be saved (right-click on the plan) and then uploaded here (it is an xml document, you just need to copy the contents and paste it in your answer here). I took the liberty of cleaning up the query to be a little easier to read - please check it to see it produces the same results and think about implementing it so you and your fellow devs have an easier time reading the code. SELECT ISNULL(Sales_PL.SBU, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Region_Name, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area_Name, ''))), ISNULL(Sales_Detail.Customer, ''), ISNULL(Sales_Detail.Customer_Name, ''), ISNULL(Sales_Detail.Item_Number, ''), ISNULL(Sales_Detail.Ship_to_State, ''), ISNULL(Sales_Detail.Product_Line, ''), ISNULL(Sales_Detail.OEM, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.OEM_Name, ''))), ISNULL(Sales_Detail.Industry, ''), ISNULL(Sales_Detail.SITE, ''), ISNULL(Sales_Detail.Reporting_Site, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))), ISNULL(Sales_Part.Pt_Added, '01/01/2000'), ISNULL(Sales_Detail.Date_Shipped, '01/01/2000'), ISNULL(IA_DATES.DATE_KEY, 0), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area, ''))), LTRIM(RTRIM(ISNULL(Sales_Contcts.Slspsn_Name, ''))), ISNULL(Sales_Detail.Corp_ID, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Corp_Name, ''))), ISNULL(Sales_Detail.Order_No, ''), ISNULL(Sales_Detail.Order_Line_No, 0), LTRIM(RTRIM(ISNULL(Sales_Detail.Item_Description_1, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Cust_Part_No, ''))), ISNULL(Sales_Detail.Ship_to_Customer, ''), ISNULL(Sales_Detail.Ship_to_Customer_Name, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Ship_to_Country, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Ship_to_Region, ''))), ISNULL(Sales_Detail.Ship_to_Zipcode, ''), ISNULL(Sales_Detail.Sold_to_Zipcode, ''), ISNULL(Sales_Detail.PO_Num, ''), ISNULL(Sales_Detail.Invoice_Num, ''), (SUM(Sales_Detail.Qty_Shipped)), (SUM(Sales_Detail.Sales_USD)), (SUM(Sales_Detail.Sales_USD)), CASE WHEN (LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area, ''))) = LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = 'LT' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = ',LT' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = '' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = 'NR' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = 'N' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = ',NR' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = '.NR' OR LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))) = 'CN') THEN ((SUM(Sales_Detail.Sales_USD)) * ISNULL(MAX(Sales_Contcts_Commission.Sales_Rep_Comm + Sales_Contcts_Commission.Design_Win_Comm), 0)) ELSE ((SUM(Sales_Detail.Sales_USD)) * ISNULL(MAX(Sales_Contcts_Commission.Sales_Rep_Comm), 0)) END, 0 FROM Sales_Detail LEFT OUTER JOIN Sales_Part ON LTRIM(RTRIM(Sales_Detail.Item_Number)) = LTRIM(RTRIM(Sales_Part.ItemNumber)) JOIN Sales_PL ON Sales_Detail.Product_Line = Sales_PL.PL_Key LEFT OUTER JOIN Sales_Contcts ON LTRIM(RTRIM(Sales_Detail.Sales_Area)) = LTRIM(RTRIM(Sales_Contcts.Slspsn)) LEFT OUTER JOIN Sales_Contcts_Commission ON LTRIM(RTRIM(Sales_Detail.Sales_Area)) = LTRIM(RTRIM(Sales_Contcts_Commission.Slspsn)) AND ISNULL(Sales_PL.SBU,'') = Sales_Contcts_Commission.PBU JOIN IA_DATES ON IA_DATES.CAL_DAY_DT = Sales_Detail.Date_Shipped WHERE Sales_Detail.Sales_Area NOT IN ('XX', -- SM: Removed per Tracy Kuhns on 6/18/2009 'DD','DD1','DD2','DD3','DD4','DD5', --'DN01','DN02','DN03','DN04','DN05','DN06', --'DN07','DN08','DN09','DN10', 'DN50', 'DN51', 'DN52', 'DN53') AND Sales_Detail.Date_Shipped >= '1/1/2008' GROUP BY ISNULL(Sales_PL.SBU, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Region_Name, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area_Name, ''))), ISNULL(Sales_Detail.Customer, ''), ISNULL(Sales_Detail.Customer_Name, ''), ISNULL(Sales_Detail.Item_Number, ''), ISNULL(Sales_Detail.Ship_to_State, ''), ISNULL(Sales_Detail.Product_Line, ''), ISNULL(Sales_Detail.OEM, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.OEM_Name, ''))), ISNULL(Sales_Detail.Industry, ''), ISNULL(Sales_Detail.SITE, ''), ISNULL(Sales_Detail.Reporting_Site, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.designwin, ''))), ISNULL(Sales_Part.Pt_Added, '01/01/2000'), ISNULL(Sales_Detail.Date_Shipped, '01/01/2000'), ISNULL(IA_DATES.DATE_KEY, 0), LTRIM(RTRIM(ISNULL(Sales_Detail.Sales_Area, ''))), LTRIM(RTRIM(ISNULL(Sales_Contcts.Slspsn_Name, ''))), ISNULL(Sales_Detail.Corp_ID, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Corp_Name, ''))), ISNULL(Sales_Detail.Order_No, ''), ISNULL(Sales_Detail.Order_Line_No, 0), LTRIM(RTRIM(ISNULL(Sales_Detail.Item_Description_1, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Cust_Part_No, ''))), ISNULL(Sales_Detail.Ship_to_Customer, ''), ISNULL(Sales_Detail.Ship_to_Customer_Name, ''), LTRIM(RTRIM(ISNULL(Sales_Detail.Ship_to_Country, ''))), LTRIM(RTRIM(ISNULL(Sales_Detail.Ship_to_Region, ''))), ISNULL(Sales_Detail.Ship_to_Zipcode, ''), ISNULL(Sales_Detail.Sold_to_Zipcode, ''), ISNULL(Sales_Detail.PO_Num, ''), ISNULL(Sales_Detail.Invoice_Num, '') [1]:
http://support.microsoft.com/kb/263889)
+1 for taking the time to really trying to format and read the complete select statement! :) I got tired of all the scrolling on the iPhone and gave up after a couple of seconds when I reallized that the code couldn't perform well, no matter of SQL edition. :D
