asked on
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;
DatabasesSSRSMicrosoft Server OSMicrosoft SQL Server
Last Comment
Jim Horn