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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both. Staging the data after midnight works perfectly as does the two separate datasets.
(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.
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.
But I'd definitely try doing the last LEFT OUTER JOIN outside of the huge GROUP BY:
Open in new window