Query Performance - Nested SELECT statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Performance - Nested SELECT statements

Author

Topic

piercey
Starting Member

1 Post

Posted - 2005-12-14 : 06:52:45

Hi List,

I'm having performance problems with the following query. I have inserted the READPAST hinst which have helped a bit. But being relatively new to SQL I can't think of any other way to optimize the performance of my query.

Thanks.

SELECT TOP 100 PERCENT
[Year Created] AS [Year],
[Company Family] AS [Company Family],
[Month Created] AS [Month],
COUNT([Incident ID]) AS Total,
COUNT([Actual Response Date]) AS [Total Logged And Responded To],
(SELECT COUNT(*) AS [Total] FROM dbo.RPT_Helper_IncidentSLASummary RPT2 WITH (READPAST)
WHERE dbo.RPT_Helper_IncidentSLASummary.[Month Created] = Month(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Year Created] = Year(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Company Family] = RPT2.[Company Family])
AS [Total Responded To],
SUM([Met Respond-By SLA]) AS [Logged and Responsed To Within SLA],
(SELECT COUNT(*) AS [Total] FROM dbo.RPT_Helper_IncidentSLASummary RPT2 WITH (READPAST)
WHERE dbo.RPT_Helper_IncidentSLASummary.[Month Created] = Month(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Year Created] = Year(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Company Family] = RPT2.[Company Family]
AND RPT2.[Actual Response Date] <= dateadd(mi, 1, RPT2.[Promised Response Date]))
AS [Response Within SLA],
COUNT([Actual Resolved Date]) AS [Total Logged And Resolved],
(SELECT COUNT(*) AS [Total] FROM dbo.RPT_Helper_IncidentSLASummary RPT2 WITH (READPAST)
WHERE dbo.RPT_Helper_IncidentSLASummary.[Month Created] = Month(RPT2.[Actual Resolved Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Year Created] = Year(RPT2.[Actual Resolved Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Company Family] = RPT2.[Company Family])
AS [Total Resolved],
SUM([Met Fix-By SLA]) AS [Logged and Resolved Within SLA],
(SELECT COUNT(*) AS [Total] FROM dbo.RPT_Helper_IncidentSLASummary RPT2 WITH (READPAST)
WHERE dbo.RPT_Helper_IncidentSLASummary.[Month Created] = Month(RPT2.[Actual Resolved Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Year Created] = Year(RPT2.[Actual Resolved Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Company Family] = RPT2.[Company Family]
AND RPT2.[Actual Resolved Date] <= dateadd(mi, 1, RPT2.[Promised Resolve Date]))
AS [Resolved Within SLA],
CONVERT(real, SUM([Met Respond-By SLA])) / CONVERT(real, COUNT([Incident ID])) * 100 AS [% Response Within SLA],
CONVERT(real, SUM([Met Fix-By SLA])) / CONVERT(real, COUNT([Incident ID])) * 100 AS [% Resolved Within SLA]
FROM dbo.RPT_Helper_IncidentSLASummary WITH (READPAST)
GROUP BY [Company Family], [Year Created], [Month Created]
ORDER BY [Year Created], [Month Created]

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 07:41:57

Hi piercey, Welcome to SQL Team!

Try removing some of the nested stuff - e.g. replace


(SELECT COUNT(*) AS [Total] FROM dbo.RPT_Helper_IncidentSLASummary RPT2 WITH (READPAST)
WHERE dbo.RPT_Helper_IncidentSLASummary.[Month Created] = Month(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Year Created] = Year(RPT2.[Actual Response Date])
AND dbo.RPT_Helper_IncidentSLASummary.[Company Family] = RPT2.[Company Family])
AS [Total Responded To],

with

SUM(
    CASE WHEN [Month Created] = Month([Actual Response Date])
              AND [Year Created] = Year([Actual Response Date])
         THEN 1
         ELSE 0
         END
   ) AS [Total Responded To],

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources