April 1, 2010 at 5:42 am
Hello,
I need help for improving the performance of a query. Please find attached the execution plan.
Please guide me to tune this query.
Thank you in Advance.
Regards
Yatish
April 1, 2010 at 6:02 am
Please post query, table definitions and index definitions, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2010 at 6:13 am
Hello,
Please find the query code blow, I tried to divide the code block in two part and checked the execution
the first part I tried to collect all history data
-- Set The User Selected date
DECLARE @FirstDate SMALLDATETIME
DECLARE @NextDate SMALLDATETIME
SET @FirstDate = convert(officechar, @fromDate,101) + ' 0:00'
SET @NextDate = convert(officechar, @toDate,101) + ' 23:59'
DECLARE @MasterSubjectId int
Select @MasterSubjectId = SubjectClassId
FROM SubjectClasses
WHERE IsmasterSubjectClass = 1
DECLARE @monno INT
SELECT @monno = DATEDIFF(month, @FirstDate, @NextDate)
declare @months table
(
Mon int,
date Datetime
)
IF @monno < 25
BEGIN
Insert into @months Values(0, @FirstDate)
DECLARE @i INT
SET @i = 1
WHILE (@i <= @monno)
BEGIN
Insert into @months Values(@i, DATEADD(mm,@i,@FirstDate))
SET @i = @i + 1
END
END
;WITH OldHistory AS
(
SELECT StudentId
FROM fnGetStudentsWithMultiofficeSupportForHistoryChanges(@loginToken,@AreaID,@IsNodeMultioffice)
)
SELECTOH1.StudentHistoryId,
OH1.StudentId,
OH1.Classid,
OH1.StudentIsLost ,
OH1.closedate,
OH1.StudentChangeDate,
(CASE OP.[action]
WHEN 'D' THEN 0
ELSE op.Fees
END
) AS Fees
INTO #StudtysHistory
FROM
(
SELECTOH1.StudentHistoryId,
OH1.StudentId,
OH1.Classid,
OH1.StudentIsLost ,
OH1.closedate,
OH1.StudentChangeDate
FROM StudentHistory OH1
JOIN OldHistory OH ON OH.StudentId = OH1.StudentId
WHERE OH.StudentId NOT IN
(
SELECT OH1.StudentId
FROM StudentHistory OH1
JOIN OldHistory OH ON OH.StudentId = OH1.StudentId
WHERE OH1.StudentChangeDate < @FirstDate AND OH1.StudentIsLost = 'Y'
)
) OH1
JOIN StudentHistoryLog OHL ON OH1.StudentHistoryId = OHL.StudentHistoryId
JOIN StudentSubjectHistory OP ON OHL.StudentSubjectHistoryId = OP.StudentSubjectHistoryId
JOIN Subjects P ON OP.Subjectid = P.Subjectid AND P.SubjectClassid = @MasterSubjectId
OPTION(Maxdop 1 );
CREATE NONCLUSTERED INDEX IX_ChangeDate ON #StudtysHistory (StudentIsLost) Include (ClassId ,StudentChangeDate , StudentHistoryId)
WITH (SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,MAXDOP =1) ON [PRIMARY]
This took 25+ sec
and the next code block which is taking more time than this
;WITH StudTemp AS
(
SELECT StudentHistoryId,StudentId, closedate,StudentChangeDate,isnull(Fees,0) AS Fees
FROM #StudtysHistory
)
SELECT M.Mon,M.date,
'# Studs' as Studtys,
Count(O.StudentId) AS StudCount
FROM @months M
OUTER Apply
(
SELECT DISTINCT OH.StudentId
FROM #StudtysHistory OH
JOIN Class ST on OH.Classid = ST.Classid AND ST.isvisible = 'Y'
WHERE OH.StudentIsLost = 'N'
AND OH.StudentChangeDate < DATEADD(mm,M.Mon + 1,@FirstDate)
AND
(
OH.ClassId <> 6 OR
(
OH.ClassId = 6 AND ( closedate >= DATEADD(mm,M.Mon,@FirstDate) AND closedate < DATEADD(mm,M.Mon + 1 ,@FirstDate) )
)
)
AND OH.StudentHistoryId =
(
SELECT MAX(OH1.StudentHistoryId) AS StudentHistoryId
FROM StudTemp OH1
WHERE OH1.StudentChangeDate < DATEADD(mm,M.Mon + 1 ,@FirstDate) AND OH1.StudentId = OH.StudentId
)
AND Fees > 0
) O
GROUP BY M.Mon,M.date
UNION ALL
SELECT M.Mon,M.date,
'# Marks' as Studtys,
Sum(O.Fees) AS Fees
FROM @months M
OUTER Apply
(
SELECT OH.StudentId, isnull(OH.Fees,0) AS Fees
FROM StudTemp OH
JOIN Class ST on OH.Classid = ST.Classid AND ST.isvisible = 'Y'
WHERE OH.StudentIsLost = 'N'
AND OH.StudentChangeDate < DATEADD(mm,M.Mon + 1,@FirstDate)
AND
(
OH.ClassId <> 6 OR
(
OH.ClassId = 6 AND ( closedate >= DATEADD(mm,M.Mon,@FirstDate) AND closedate < DATEADD(mm,M.Mon + 1 ,@FirstDate) )
)
)
AND OH.StudentHistoryId =
(
SELECT MAX(OH1.StudentHistoryId) AS StudentHistoryId
FROM StudTemp OH1
WHERE OH1.StudentChangeDate < DATEADD(mm,M.Mon + 1 ,@FirstDate) AND OH1.StudentId = OH.StudentId
)
) O
GROUP BY M.Mon,M.date
DROP TABLE #StudtysHistory
April 1, 2010 at 6:28 am
Table definitions and index definitions please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2010 at 7:24 am
Try taking the results of
SELECT StudentId
FROM fnGetStudentsWithMultiofficeSupportForHistoryChanges(@loginToken,@AreaID,@IsNodeMultioffice)
into a temptable and joining to that instead.
What does the plan look like with that ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply