Slow Query To Calculate Averages

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

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.

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 ...

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.

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