The problems am having table TestMArk has big data for about 10 years with about millions rows for schools in a district , however a student can be enrolled for not more than 6 years even if they repeat maybe 1-2 years maybe some have repeated a level , meaning to say data records with date lesser than 8 years from today is for former students.
However the requirement is to analyse student data so as to compare student performance based on time , subject etc.
These analyses is however done by ie c# when we have retrieved the data for that particular student. Like the grouping per subject is done by the system , maybe the student had test for math , geograghy & many more for 6 years .
Table TestMark does not contain the criteria provided for getting the data thus we based on table student conditions. which are shown below
1: Table Student , Column RegNumber
2: Table Student, Column SchoolID
SELECT enrol.StudentID, ex.TestID, ex.EnrollmentID, stu.SchoolID, tm.TermID , tm.Year , tm.TermNumber , sub.SubjectCode, sub.SubjectName, stu.RegNumber, stu.StudentName +' '+ stu.StudentSurname As FullName, ex.Mark, tes.OutOf, tes.TestTitle, ex.IsArchived , ex.IsDeleted FROM TestMark ex INNER JOIN Test tes on tes.TestID = ex.TestID INNER JOIN TeacherClassSubject tcs on tcs.TeacherClassSubjectID = tes.TeacherClassSubjectID INNER JOIN Term tm on tcs.TermID = tm.TermID INNER JOIN Class cl on tcs.ClassID = cl.ClassID INNER JOIN Subject sub on sub.SubjectID = tcs.SubjectID INNER JOIN Enrollment enrol on enrol.EnrollmentID = ex.EnrollmentID INNER JOIN Student stu on stu.StudentID = enrol.StudentID AND stu.SchoolID = @schoolID AND stu.StudentID = @studentID AND ex.IsDeleted IS NULL
The mostly like student data for the whole period at a school can not write more than 800 000 tests for the whole period to be realistic
But the thing is am having many inner join to get related data Is there a way l can optimize the following query to give better perfomance.
Also note the any column that end with ID is a primary key / foreign key.
what l think might increase perfomance
I thought of using common table expression but am not sure whether it will work because l have to get the data based on
The least l did for Table student is making SchoolID and RegNumber unique
l know millions is not a number especially in 2019 but faster retrieval of data will add best user experience to the user of the application , hence need to optimise everything