troubleshooting Question

SQL Query Performance

Avatar of mburk1968
mburk1968Flag for United States of America asked on
DatabasesSSRSMicrosoft Server OSMicrosoft SQL Server
4 Comments1 Solution132 ViewsLast Modified:
I have the following query that executes in SSMS in 1 minute 28 sec. However when I attempt to run it in SSRS it times out. I have increased the Time out to 600 and it still fails. I would use query analyzer to place some indexes however this is our ERP database and each time they work on the DB they restore the database as it was deployed meaning indexes, tables, views, etc. are dropped and I have to rescript them.

The query returns 37557 rows. It began to time out in SSRS when I added the following Join LEFT OUTER JOIN [dbo].[KLL Customer Projections] C ON C.customer = ORD.customer

This is a summary amount that needs added.  Specifically this column C.projection_amt , So at the summary level I have 40 customers. Each with a sales projection amount. So part of my issue is that I'm linking thousands of records to 1

Is there anyway to tweak this query so that it runs more efficiently?
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  ORD.customer ,
        zzxcustr.cust_name ,
        ORD.Style_Season ,
        zzxseasr.seas_name ,
        ORD.style ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.dimension ,
        ORD.MAINLBL ,
        ORD.SCALEFIN ,
        ORD.CLASS ,
        ORD.TYPE ,
        ORD.slsperson1 ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear
                                         - 2 )
                                       AND ( ORD.line_status = 'I' )
                                     )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END)) AS Year1_Ext_Gross_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear
                                         - 1 )
                                       AND ( ORD.line_status = 'I' )
                                     )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END)) AS Year2_Ext_Gross_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear )
                                       AND ( ORD.line_status = 'I' )
                                     )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END)) AS Cur_Year_Ext_Gross_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                                     AND ( ORD.line_status = 'O'
                                           OR ORD.line_status = 'P'
                                         )
                                     AND ( ORD.total_qty <> 0 )
                                     AND ( ORD.conf_type_cascaded = 'A' )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END)) AS Cur_Year_Confir_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                                     AND ( ORD.line_status = 'O'
                                           OR ORD.line_status = 'P'
                                         )
                                     AND ( ORD.total_qty <> 0 )
                                     AND ( ORD.conf_type_cascaded = 'B' )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END)) AS Cur_Year_Bulk_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear )
                                       AND ( ORD.line_status = 'I' )
                                     )
                                THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                                ELSE ( 0 )
                           END
            + CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                        AND ( ORD.line_status = 'O'
                              OR ORD.line_status = 'P'
                            )
                        AND ( ORD.total_qty <> 0 )
                        AND ( ORD.conf_type_cascaded = 'A' )
                   THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                   ELSE ( 0 )
              END
            + CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                        AND ( ORD.line_status = 'O'
                              OR ORD.line_status = 'P'
                            )
                        AND ( ORD.total_qty <> 0 )
                        AND ( ORD.conf_type_cascaded = 'B' )
                   THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                   ELSE ( 0 )
              END)) AS Cur_Year_Booking_Sales ,
        C.projection_amt ,
        C.is_International
FROM    [KLL All Order Detail With Style Data] ORD
        INNER JOIN zzxcustr ON ORD.customer = zzxcustr.customer
        LEFT OUTER JOIN zzxdiscr ON ORD.discount = zzxdiscr.discount
        INNER JOIN zzxseasr ON zzxseasr.division = ORD.division
                               AND zzxseasr.season = ORD.Style_Season
		LEFT OUTER JOIN [dbo].[KLL Customer Projections] C ON C.customer = ORD.customer
WHERE   ( ORD.division = 'KLL' )
        AND ( ORD.customer IN ( @Customers ) )
GROUP BY ORD.customer ,
        zzxcustr.cust_name ,
        ORD.Style_Season ,
        zzxseasr.seas_name ,
        ORD.style ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.dimension ,
        ORD.MAINLBL ,
        ORD.SCALEFIN ,
        ORD.CLASS ,
        ORD.TYPE ,
        ORD.slsperson1 ,
        C.projection_amt ,
        C.is_International
HAVING  ( SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear
                                           - 2 )
                                         AND ( ORD.line_status = 'I' )
                                       )
                                  THEN ( ORD.total_qty * ORD.price
                                         * ORD.co_rate )
                                  ELSE ( 0 )
                             END
              + CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear - 1 )
                            AND ( ORD.line_status = 'I' )
                          ) THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                     ELSE ( 0 )
                END + CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear )
                                  AND ( ORD.line_status = 'I' )
                                )
                           THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                           ELSE ( 0 )
                      END
              + CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                          AND ( ORD.line_status = 'O'
                                OR ORD.line_status = 'P'
                              )
                          AND ( ORD.total_qty <> 0 )
                          AND ( ORD.conf_type_cascaded = 'A' )
                     THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                     ELSE ( 0 )
                END
              + CASE WHEN ( (YEAR(ORD.end_date) = @CurYear) )
                          AND ( ORD.line_status = 'O'
                                OR ORD.line_status = 'P'
                              )
                          AND ( ORD.total_qty <> 0 )
                          AND ( ORD.conf_type_cascaded = 'B' )
                     THEN ( ORD.total_qty * ORD.price * ORD.co_rate )
                     ELSE ( 0 )
                END)) > '0.00' )
        AND ( ORD.Style_Season IN ( @Season ) )
        --AND ( ORD.SCALEFIN IN ( @Size_Scale ) )
        --AND ( ORD.MAINLBL IN ( @Main_Label ) )
        AND ( ORD.CLASS IN ( @Class ) )
        AND ( ORD.TYPE IN ( @Type ) )
ORDER BY ORD.customer;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros