January 15, 2004 at 10:21 am
Hi can someone help me make the following code in a stored proc run a bit faster.
Select A.intClassID,
A.intSubjectID,
A.strYear,
A.intGroupID,
A.strClassDescription,
B.strStaffName,
B.strForename,
B.strSurname,
C.strSubject,
Count(D.intCourseDescID) as intDefaultCount,
0 as selected,
IsNull(x.intErowsGenerated,0) as intErowCount,
IsNull(y.intStudents,0) as intStudentCount
From dbo.tblClass A Left Outer Join
dbo.tblStaff B On A.intStaffID = B.intStaffID Left Outer Join
dbo.tlkpSubjects C On A.intSubjectID = C.intSubjectID Left Outer Join
dbo.tlkpCourseDescription D On A.intClassID = D.intClassID and D.strDefaultRecord = '1' and D.intYearID = @intAcademicYear and D.intPeriodID = @intPeriod Left Outer Join
(Select E.intClassID, Count(E.intErowID) as intErowsGenerated
From tblErow E
Where E.intPeriod = @intPeriod and E.intYear = @intAcademicYear
Group By E.intClassID) x On x.intClassID = A.intClassID Left Outer Join
(Select F.intClassID, Count(F.intStudentClassID) as intStudents
From trelStudentClasses F
Group By F.intClassID) y On y.intClassID = A.intClassID
Where A.intAcademicYear = @intAcademicYear and
A.bErowRequired = 1 and
A.strClassDescription Is Not Null
Group By A.intClassID,
A.intSubjectID,
A.strYear,
A.intGroupID,
A.strClassDescription,
B.strStaffName,
B.strForename,
B.strSurname,
C.strSubject,
x.intErowsGenerated,
y.intStudents
Order By C.strSubject,
A.strClassDescription,
B.strStaffName
The problem I have is with the following two fields and the code that calculates them.
IsNull(x.intErowsGenerated,0) as intErowCount,
IsNull(y.intStudents,0) as intStudentCount
When I added them, it slowed right down.
I can’t just do a left outer join on their parent tables because the totals they then return are wrong.
Can anyone shed some light here. Is more information required.
Thanks in advance.
January 15, 2004 at 3:29 pm
Yeah using complex dynamic tables will do that to a query.
I think you would see a huge difference if you separated out the dynamic tables into separate queries that you insert into temp tables and then join on the temp tables.
Otherwise, my suggestion would be to take a look at teh estimated execution plan in Query Analyzer and take a look at what parts are slowing yout down.
January 16, 2004 at 12:31 am
Try this, I hope it will help:
Separate the columns in the SELECT into two groups:
1. Columns that are used in joins (mainly id's).
2. Columns that can be obtained by lookup (descriptions etc.).
Now, write and test the core SQL (that does the GROUP BY), using only the first group of columns. If proper indexes are in place, this should run fast enough. Then, use the core SQL as a subquery and join it to all lookup tables to obtain the columns from the second group. I suspect that the GROUP BY clause with all that descriptions became too large.
Regards,
Goce Smilevski.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply