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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy