SQL Query Performance

mburk1968
mburk1968 used Ask the Experts™
on
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;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Nothing big comes to mind, but offhand without digging too deep into the T-SQL...
  • Might not be a bad idea to regularly stage this data in a table so the SSRS report can just call that table.
  • Can lose the ORDER BY if the SSRS report will do its own sorting.
  • Do you REALLY need an SSRS report to contain 37,557 rows?  That'd be about a thousand pages?
  • If there are really two sets of data in play, summary and detail, then it would be an excellent idea to create two separate datasets, with the detail accepting a parameter of whatever the CustomerID is to the summary.  That way when the SSRS report renders it only renders the summary set at first, and then afterwards only the detail for whatever CustomerID is clicked.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The GROUP BY is big overhead, but I don't see a way to remove it that would necessarily be accurate.

But I'd definitely try doing the last LEFT OUTER JOIN outside of the huge GROUP BY:

SELECT  QRY1.* ,
        C.projection_amt ,
        C.is_International

FROM (

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

) AS QRY1

LEFT OUTER JOIN [dbo].[KLL Customer Projections] C ON C.customer = QRY1.customer

ORDER BY QRY1.customer;

Open in new window

Author

Commented:
Thank you both. Staging the data after midnight works perfectly as does the two separate datasets.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
(added info)

If there are really two sets of data in play, summary and detail, then it would be an excellent idea to create two separate datasets, with the detail accepting a parameter of whatever the CustomerID is to the summary.  Then create a subreport for the detail set, and connect via parameters the ID to the main report. That way when the SSRS report renders it only renders the summary set at first, and then afterwards only the detail for whatever CustomerID is clicked.  Guessing there are other improvements to be had that haven't been discussed here, to include posting the execution plan of the query and making improvements such as Scott's comment above.

Good luck.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial