I have a displayed part of the database that is relevant to the question in the images below.
The problems are having a table Testmark has been bulky data for about 10 years, with about millions of lines for schools in a district. However, a student can only be registered for 6 years, even if he or she repeats maybe one to two years; less than 8 years from today is reserved for former students.
However, students' data must be analyzed to compare their performance with time, subject, etc.
However, these analyzes are done for example when we have recovered the data relating to this student. Since the subject grouping is done by the system, the student may have taken a math test, geography test and many more for 6 years.
Table Testmark does not contain the criteria provided to get the data so we based on the 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.année, tm.TermNumber, sub.SubjectCode, sub.SubjectName, stu.RegNumber, stu.StudentName + & # 39; & # 39; + stu.StudentSurname As FullName, ex.Marque, tes.OutOf, tes.TestTitle, ex.IsArchived, ex.IsDeleted From TestMark ex INNER JOIN Test your tes.TestID = ex.TestID INNER JOIN tcs TeacherClassSubject 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 Membership Enrollment 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
To be realistic, data on essentially similar students for the entire period in a school can not write more than 800,000 tests for the entire period.
But the thing is to have a lot of internal join to get related data
Is it possible to optimize the following query for better performance?
Also note that any column ending with ID is a foreign key / primary key.
what I think could increase performance
I thought about using common table expression but I'm not sure that it will work because I have to get the data based on
The least I have done for the student table is SchoolID and RegNumber unique
I know millions This is not a number, especially in 2019, but the faster recovery of data will add a better user experience to the user of the application, hence the need for more. optimize everything.