x

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
more ▼

asked Jan 18 '11 at 09:09 PM in Default

rajeshthumuluri gravatar image

rajeshthumuluri
11 1 1 1

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.
Jan 19 '11 at 02:34 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Jan 18 '11 at 11:15 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

+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.
Jan 19 '11 at 02:27 AM Kev Riley ♦♦
@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.
Jan 19 '11 at 02:32 AM Håkan Winther
@Håkan I'd be willing to bet that it takes less than 10 mins to update the lot!
Jan 19 '11 at 02:44 AM Kev Riley ♦♦
@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.
Jan 19 '11 at 03:56 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Jan 19 '11 at 12:17 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

mmmm, thats much more readable !

+1 - good advice too.
Jan 19 '11 at 12:22 AM Fatherjack ♦♦
+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
Jan 19 '11 at 12:23 AM Håkan Winther
@Fatherjack - it is better than before! I still stand by Hakan's statement of cleaning the data in the database instead.
Jan 19 '11 at 12:27 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jan 18 '11 at 09:17 PM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

+1 I would accept your answer to the question, because the performance difference between the server is probably because of the statistics.
Jan 19 '11 at 12:50 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x562
x241

asked: Jan 18 '11 at 09:09 PM

Seen: 3130 times

Last Updated: Jan 19 '11 at 02:43 AM