September 11, 2013 at 5:05 am
Hi All, can anyone help shed some light on the following issue...
SQL Server 2008 SP.
My SP returns data almost instantly if the date range I supply has data on all possible dates within the date range. If however, I put a date range in that includes one or more days without date, it grinds to a halt.
For example, if I run it so the date range is a monday to friday and each of these days has data, the date returns instantly. However if I run it from say, thursday to saturday (and the saturday has no data), the SP takes about 15 seconds to run. If I scale up the date range to about 6 months, there may be many days with no data - suddenly the query is taking 4 to 5 minutes to run! I have indexes set up on all the joining columns and the date field. Sp below (apologies that I can't get it to format into a nice layout here)...
ALTER PROCEDURE [dbo].[sp07319_UT_Reporting_Surveyors_by_Spans_Surveyed]
(@StartDate DATETIME,
@EndDate DATETIME,
@CircuitFilter VARCHAR(MAX),
@VoltageFilter VARCHAR(MAX),
@SurveyorFilter VARCHAR(MAX))
AS
SET NOCOUNT ON
SELECTA.[intStaffID] AS [StaffID],
ISNULL(A.[strForename], '') AS [Forename],
ISNULL(A.[strSurname], '') AS [Surname],
ISNULL(A.[strSurname], '') + ': ' + ISNULL(A.[strForename], '') AS [SurveyorName],
ISNULL(Y.[SurveyedSpanCount], 0) AS [SurveyedSpanCount],
ISNULL(Y.[SurveyedSpanClear], 0) AS [SurveyedSpanClear],
ISNULL(Y.[SurveyedSpanNotClear], 0) AS [SurveyedSpanNotClear],
ISNULL(Z.[ToBeSurveyedSpanCount], 0) AS [ToBeSurveyedCount],
ISNULL(X.[AllocatedSpanCount], 0) AS [AllocatedSpanCount]
FROM[tblStaff] A
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [AllocatedSpanCount]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) X ON A.[intStaffID] = X.[SurveyorID]
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [SurveyedSpanCount],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 1 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanClear],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 0 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanNotClear]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate
AND ISNULL(A.[SurveyStatusID], 0) > 0
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) Y ON A.[intStaffID] = Y.[SurveyorID]
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [ToBeSurveyedSpanCount]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate
AND ISNULL(A.[SurveyStatusID], 0) = 0
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) Z ON A.[intStaffID] = Z.[SurveyorID]
WHERE(@SurveyorFilter = '' OR A.[intStaffID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@SurveyorFilter)))
AND ISNULL(A.[IsUtilityArbSurveyor], 0) = 1
ORDER BY [Surname] ASC,
[Forename] ASC
September 11, 2013 at 5:33 am
It will be good if you use BETWEEN to specify date ranges... I have made some changes in your SP and placed BETWEEN in where condition..
Please run and let us know if your sp is still raking same time as it was taking before...
ALTER PROCEDURE [dbo].[sp07319_UT_Reporting_Surveyors_by_Spans_Surveyed]
(@StartDate DATETIME,
@EndDate DATETIME,
@CircuitFilter VARCHAR(MAX),
@VoltageFilter VARCHAR(MAX),
@SurveyorFilter VARCHAR(MAX))
AS
SET NOCOUNT ON
SELECTA.[intStaffID] AS [StaffID],
ISNULL(A.[strForename], '') AS [Forename],
ISNULL(A.[strSurname], '') AS [Surname],
ISNULL(A.[strSurname], '') + ': ' + ISNULL(A.[strForename], '') AS [SurveyorName],
ISNULL(Y.[SurveyedSpanCount], 0) AS [SurveyedSpanCount],
ISNULL(Y.[SurveyedSpanClear], 0) AS [SurveyedSpanClear],
ISNULL(Y.[SurveyedSpanNotClear], 0) AS [SurveyedSpanNotClear],
ISNULL(Z.[ToBeSurveyedSpanCount], 0) AS [ToBeSurveyedCount],
ISNULL(X.[AllocatedSpanCount], 0) AS [AllocatedSpanCount]
FROM[tblStaff] A
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [AllocatedSpanCount]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] BETWEEN @StartDate AND @EndDate
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) X ON A.[intStaffID] = X.[SurveyorID]
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [SurveyedSpanCount],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 1 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanClear],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 0 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanNotClear]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] BETWEEN @StartDate AND @EndDate
AND ISNULL(A.[SurveyStatusID], 0) > 0
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) Y ON A.[intStaffID] = Y.[SurveyorID]
LEFT OUTER JOIN (SELECTB.[SurveyorID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [ToBeSurveyedSpanCount]
FROM[UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHEREB.[SurveyDate] BETWEEN @StartDate AND @EndDate
AND ISNULL(A.[SurveyStatusID], 0) = 0
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID]) Z ON A.[intStaffID] = Z.[SurveyorID]
WHERE(@SurveyorFilter = '' OR A.[intStaffID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@SurveyorFilter)))
AND ISNULL(A.[IsUtilityArbSurveyor], 0) = 1
ORDER BY [Surname] ASC,
[Forename] ASC
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 11, 2013 at 5:35 am
Hi, I originally had between clauses on the SP but I removed them as I thought that might be the issue but it's the same regardless?
Any other ideas?
Thanks.
September 11, 2013 at 5:38 am
Just ran EXEC sp_updatestats and the problem has gone away! 😀
September 11, 2013 at 6:15 am
Charlottecb (9/11/2013)
Just ran EXEC sp_updatestats and the problem has gone away! 😀
Excellent!
You might get better performance from this query by preaggregating some of those tables feeding the three subqueries, something like this:
SELECT
B.[SurveyorID],
A.[SurveyStatusID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [SurveyedSpanCount],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 1 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanClear],
ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 0 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanNotClear]
INTO #Temp
FROM [UT_Surveyed_pole] A
INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID]
INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID]
INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID]
WHERE B.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate
--AND ISNULL(A.[SurveyStatusID], 0) > 0
AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter)))
AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter)))
GROUP BY B.[SurveyorID], A.[SurveyStatusID]
SELECT
A.[intStaffID] AS [StaffID],
ISNULL(A.[strForename], '') AS [Forename],
ISNULL(A.[strSurname], '') AS [Surname],
ISNULL(A.[strSurname], '') + ': ' + ISNULL(A.[strForename], '') AS [SurveyorName],
ISNULL(Y.[SurveyedSpanCount], 0) AS [SurveyedSpanCount],
ISNULL(Y.[SurveyedSpanClear], 0) AS [SurveyedSpanClear],
ISNULL(Y.[SurveyedSpanNotClear], 0) AS [SurveyedSpanNotClear],
ISNULL(Z.[ToBeSurveyedSpanCount], 0) AS [ToBeSurveyedCount],
ISNULL(X.[AllocatedSpanCount], 0) AS [AllocatedSpanCount]
FROM [tblStaff] A
LEFT OUTER JOIN (
SELECT
[SurveyorID],
ISNULL(SUM([SurveyedSpanCount]), 0) AS [SurveyedSpanCount],
ISNULL(SUM([SurveyedSpanClear]), 0) AS [SurveyedSpanClear],
ISNULL(SUM([SurveyedSpanNotClear]), 0) AS [SurveyedSpanNotClear]
FROM #Temp
WHERE [SurveyStatusID] > 0
GROUP BY [SurveyorID]
) Y
ON A.[intStaffID] = Y.[SurveyorID]
LEFT OUTER JOIN (
SELECT
[SurveyorID],
ISNULL(SUM([ToBeSurveyedSpanCount]), 0) AS [ToBeSurveyedSpanCount]
FROM #Temp
WHERE [SurveyStatusID] IS NULL OR [SurveyStatusID] = 0
GROUP BY B.[SurveyorID]
) Z
ON A.[intStaffID] = Z.[SurveyorID]
LEFT OUTER JOIN (
SELECT
[SurveyorID],
ISNULL(SUM([AllocatedSpanCount]), 0) AS [AllocatedSpanCount]
FROM #Temp
GROUP BY [SurveyorID]
) X
ON A.[intStaffID] = X.[SurveyorID]
WHERE (
@SurveyorFilter = ''
OR A.[intStaffID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@SurveyorFilter))
)
AND A.[IsUtilityArbSurveyor] = 1
ORDER BY [Surname] ASC,
[Forename] ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2013 at 6:59 am
Hi ChrisM,
Many thanks for taking the time to do this - I'll try out your version to see how much quicker it performs.:-D
September 11, 2013 at 7:14 am
Charlottecb (9/11/2013)
Hi ChrisM,Many thanks for taking the time to do this - I'll try out your version to see how much quicker it performs.:-D
You're welcome. With no sample data to run against, you may have to do a little tweaking.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply