SQL Query Performance

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

mburk1968Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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

0
mburk1968Author Commented:
Thank you both. Staging the data after midnight works perfectly as does the two separate datasets.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.