I have a shown part of the database that is relevant to the question in the images below. enter image description here

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 .

The problem

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,
                                            tm.TermID ,
                                            tm.Year ,
                                            tm.TermNumber ,
                                            stu.StudentName +' '+ stu.StudentSurname As FullName,
                                            ex.IsArchived ,
                                    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

  • 3
    Post query plan, post indexes, etc. – Erik Darling Mar 28 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.