SQL Server Performance

Slow Query To Calculate Averages

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by asaacks@yahoo.com, Mar 31, 2008.

  1. asaacks@yahoo.com New Member

    I have written a query which calculates the average attendance of students for modules. The basic query takes 4 seconds to run. Problem is that I need to filter out the inactive modules for inactive semesters. This I have done with a join. As soon as I put that in, the query takes over a minute ! I have tried using an exists clause and views, all to no avail. I have underlined the problematic join. Please help...select
    distinctr
    .UNISANumber,sm.semestermoduleid ,r.Flbbox,UPPER(c.Firstname) AS FirstName,UPPER(c.lastname) as LastName,mt.ModuleTypeDescription
    ,mt.ModuleTypeId,m.ModuleName,a.registrationid,r.registrationstatusid,
    round(CONVERT(FLOAT,ISNULL((select COUNT(*) from attendance at
    where --semestermoduleid = @SemesterModuleID and attendancestatusid = 1
    and at.registrationid = a.registrationidgroup by registrationid),0))
    /
    --ALL DATES REGISTRATION TAKENCONVERT (FLOAT,(select COUNT(*) from attendance aa
    where a.registrationid = aa.registrationid
    group by registrationid)) *100,0) AS 'Attendance'from Registration r
    left outer join Attendance a on r.RegistrationId=a.RegistrationID --and r.registrationstatusid = 2
    inner join Contact c on r.UnisaNumber=c.UnisaNumber --and r.registrationstatusid = 2
    inner join SemesterModule sm on sm.SemesterModuleID=r.SemesterModuleID Inner
    Join Semester s on sm.SemesterId=s.SemesterId and s.semesterstatusid = 4Inner Join Module m on sm.ModuleId=m.ModuleId
    Inner Join ModuleType mt on r.ModuleTypeId=mt.ModuleTypeId
    inner join
    (select *from Registration reg
    where round (CONVERT(FLOAT,ISNULL((select COUNT(*) from attendance at
    where --semestermoduleid = @SemesterModuleID and attendancestatusid = 1
    and at.registrationid = reg.registrationidgroup by registrationid ),0))
    /
    --ALL DATES REGISTRATION TAKEN
    CONVERT (FLOAT,(select COUNT(*) from attendance aa
    where reg.registrationid = aa.registrationid group by registrationid )) *100,0) <80) AS RegTemp
    ON RegTemp.RegNumber = r.RegNumber where
    r.registrationstatusid = 2
    order
    by LastName,FirstName
  2. Adriaan New Member

    I presume the underlined code is what you added?
    Inner Join Semester s on sm.SemesterId=s.SemesterId and s.semesterstatusid = 4
    In that case, there's probably an index missing either on the SemesterId column of the SemesterModule column, or on the SemesterStatusId column on the Semested table - or on both.
    You might try if moving this join to the WHERE clause might help -
    WHERE sm.SemesterId IN (SELECT SemesterId FROM semester s WHERE s.semesterstatusid = 4)
    - but the indexes are more likely to have an effect.
  3. asaacks@yahoo.com New Member

    The Semester Table has a primary key of SemesterID and the SemesterModule table has a primary key of SemesterModuleID. I have made SemesterModuleID and RegistrationStatusID indexes in the Registration table, but still no joy ...
  4. Adriaan New Member

    You're repeating the calculation of whatever-it-is-you're-calculating in the SELECT clause, for each row from the Registration table.
    From what I can tell, the calculation is based on a grouping on RegistrationId, and should be done in a derived table (in the FROM clause) instead of in a subquery (in the SELECT clause). That should reduce the amount of calculations.
    Perhaps before the added join, SQL was able to see the repetitive nature of the subquery, and with the added join it could no longer recognize that.
  5. asaacks@yahoo.com New Member

    I have used the temporary table approach which evolved into using a derived table. I think this is what you were getting at before. In any event, the query takes 4 seconds as it should !!
    SELECT
    * FROM(
    SELECT DISTINCT -- top 3000 r.UnisaNumber
    ,sm.SemesterModuleID ,r.FLBBox
    ,UPPER(c.FirstName) AS FirstName,UPPER(c.LastName) AS LastName
    ,mt.ModuleTypeDescription ,mt.ModuleTypeID
    ,m.ModuleName ,a.RegistrationID
    ,r.RegistrationStatusID ,'Exception Report: Attendance > ' +convert(varchar(10),@percentage) + ' %' AS Title
    , sm.SemesterID-- , @LiveSemester AS LiveSemester
    ,ROUND(CONVERT(FLOAT, ISNULL --Actual Attendance((SELECT COUNT(*) AS Expr1
    FROM dbo.Attendance AS at
    WHERE (AttendanceStatusID = 1) AND (RegistrationID = a.RegistrationID)
    GROUP BY RegistrationID), 0)) / CONVERT(FLOAT,
    (SELECT COUNT(*) AS Expr1 --Full AttendanceFROM dbo.Attendance AS aa
    WHERE (a.RegistrationID = RegistrationID)GROUP BY RegistrationID)) * 100, 0) AS Attendance
    ,ROUND(CONVERT(FLOAT, ISNULL
    ((SELECT SUM(CONVERT(int, at.Credits)) AS Expr1 -- Tests done
    FROM dbo.AssignedTestHomework AS at INNER JOINdbo.TestHomework AS th ON th.TestHomeworkID = at.TestHomeworkID AND th.IsTest = 1
    WHERE (at.RegistrationID = a.RegistrationID)
    GROUP BY at.RegistrationID), 0)) / CONVERT(FLOAT,
    (SELECT COUNT(*) * 100 AS Expr1 -- All Tests
    FROM dbo.AssignedTestHomework AS aa INNER JOINdbo.TestHomework AS th ON th.TestHomeworkID = aa.TestHomeworkID AND th.IsTest = 1
    WHERE (a.RegistrationID = aa.RegistrationID)GROUP BY aa.RegistrationID)) * 100, 0) AS Test
    ,ROUND(CONVERT(FLOAT, ISNULL
    ((SELECT COUNT(*) AS Expr1 -- Actual Homework
    FROM dbo.AssignedTestHomework AS at INNER JOINdbo.TestHomework AS th ON th.TestHomeworkID = at.TestHomeworkID AND th.IsTest = 2
    WHERE (at.IsDone = 1) AND (at.RegistrationID = a.RegistrationID)
    GROUP BY at.RegistrationID), 0)) / CONVERT(FLOAT,
    (SELECT COUNT(*) AS Expr1 -- All Homework
    FROM dbo.AssignedTestHomework AS aa INNER JOINdbo.TestHomework AS th ON th.TestHomeworkID = aa.TestHomeworkID AND th.IsTest = 2
    WHERE (a.RegistrationID = aa.RegistrationID)
    GROUP BY aa.RegistrationID)) * 100, 0) AS Homework--INTO TempFROM
    dbo.Registration AS r LEFT OUTER JOINdbo
    .Attendance AS a ON r.RegistrationID = a.RegistrationID INNER JOINdbo.Contact AS c ON r.UnisaNumber = c.UnisaNumber INNER JOIN dbo
    .SemesterModule AS sm ON sm.SemesterModuleID = r.SemesterModuleID INNER JOINdbo
    .Module AS m ON sm.ModuleID = m.ModuleID INNER JOINdbo
    .ModuleType AS mt ON r.ModuleTypeID = mt.ModuleTypeID WHERE r.RegistrationStatusID = 2 AND r.regnumber in
    (SELECT reg.regnumberFROM dbo.Registration AS reg
    WHERE (ROUND(CONVERT(FLOAT, ISNULL((SELECT COUNT(*) AS Expr1
    FROM dbo.Attendance AS at
    WHERE (AttendanceStatusID = 1) AND (RegistrationID = reg.RegistrationID)
    GROUP BY RegistrationID), 0)) / CONVERT(FLOAT,(SELECT COUNT(*) AS Expr1
    FROM dbo.Attendance AS aa
    WHERE (reg.RegistrationID = RegistrationID)GROUP BY RegistrationID)) * 100, 0) < @Percentage))
    ) AS TEMP1WHERE
    SemesterID = (SELECT SemesterID FROM Semester WHERE SemesterStatusID = 4)ORDER
    BY LastName, FirstName

Share This Page