Making join condition more exclusive causes query to hang

Sorry for the wall of SQL, but I am having some problems with the query below. It seems to never finish executing (it runs for a few minutes, then I kill it). The weird thing is that if I change the join condition for the StudentTestsPre table from TestInstances.fkSchoolYearID = (TestInstancesPre.fkSchoolYearID + 1) to TestInstances.fkSchoolYearID > TestInstancesPre.fkSchoolYearID, then the query returns insta开发者_运维百科ntly. How could using a more exclusive join condition cause my query to hang? Seems like that should make the query faster, if anything.

Any ideas?

            SELECT *
        FROM TestInstances
        INNER JOIN StudentTests on StudentTests.fkTestInstanceID = TestInstances.pkTestInstanceID
                                    AND StudentTests.pkStudentTestID IN (SELECT * FROM @tempTests)
        INNER JOIN TestInstances TestInstancesPre ON TestInstances.fkSchoolYearID = (TestInstancesPre.fkSchoolYearID + 1)
                                        AND TestInstancesPre.fkTestTypeID = 1 AND TestInstances.fkTestTypeID = 1
        INNER JOIN StudentTests StudentTestsPre on StudentTestsPre.fkTestInstanceID = TestInstancesPre.pkTestInstanceID
                                        AND StudentTests.fkStudentID = StudentTestsPre.fkStudentID
        INNER JOIN StudentScores_Subject s ON s.fkStudentTestID = StudentTests.pkStudentTestID
                                        AND s.fkTest_SubjectID IN (SELECT pkTestSubjectID FROM MM_Test_Subjects WHERE fkCSTStrandID IN (SELECT number FROM itot(@strAcceptableStrands, N','))  AND fkTestTypeID = 1)
                                        AND s.fkScoreTypeID = 3
        INNER JOIN StudentScores_Subject sPre ON sPre.fkStudentTestID = StudentTestsPre.pkStudentTestID
                                        AND sPre.fkTest_SubjectID IN (SELECT pkTestSubjectID FROM MM_Test_Subjects WHERE fkCSTStrandID IN (SELECT number FROM itot(@strAcceptableStrands, N','))  AND fkTestTypeID = 1)
                                        AND sPre.fkScoreTypeID = 3
        INNER JOIN MM_Test_PL_SS_Ranges r ON r.fkTest_SubjectID = s.fkTest_SubjectID 
                                        AND r.fkSchoolYearID = TestInstances.fkSchoolYearID 
                                        AND r.fkTestTypeID = TestInstances.fkTestTypeID
                                        AND (r.fkGradeID = StudentTests.fkGradeID OR r.fkGradeID = 99)
        INNER JOIN MM_Test_PL_SS_Ranges rPre ON rPre.fkTest_SubjectID = sPre.fkTest_SubjectID 
                                        AND rPre.fkSchoolYearID = TestInstancesPre.fkSchoolYearID 
                                        AND rPre.fkTestTypeID = TestInstancesPre.fkTestTypeID
                                        AND (rPre.fkGradeID = StudentTestsPre.fkGradeID OR rPre.fkGradeID = 99)
        INNER JOIN StudentScores_Subject s2 ON s2.fkStudentTestID = StudentTests.pkStudentTestID
                                        AND s2.fkTest_SubjectID = s.fkTest_SubjectID
                                        AND s2.fkScoreTypeID = 2
        INNER JOIN StudentScores_Subject sPre2 ON sPre2.fkStudentTestID = StudentTestsPre.pkStudentTestID
                                        AND sPre2.fkTest_SubjectID = sPre.fkTest_SubjectID
                                        AND sPre2.fkScoreTypeID = 2
        INNER JOIN Students on Students.pkStudentID = StudentTests.fkStudentID

thanks for the help!

For SO, here's the above script with alternative formatting & short aliases:

SELECT *
FROM TestInstances

  INNER JOIN StudentTests st
     ON st.fkTestInstanceID = ti.pkTestInstanceID
    AND st.pkStudentTestID IN (SELECT * FROM @tempTests)

  INNER JOIN TestInstances tiPre
     ON ti.fkSchoolYearID = (tiPre.fkSchoolYearID + 1)
    AND tiPre.fkTestTypeID = 1 AND ti.fkTestTypeID = 1

  INNER JOIN StudentTests stPre
     ON stPre.fkTestInstanceID = tiPre.pkTestInstanceID
    AND st.fkStudentID = stPre.fkStudentID

  INNER JOIN StudentScores_Subject s
     ON s.fkStudentTestID = st.pkStudentTestID
    AND s.fkTest_SubjectID IN (
          SELECT pkTestSubjectID
          FROM MM_Test_Subjects
          WHERE fkCSTStrandID IN (
                  SELECT number FROM itot(@strAcceptableStrands, N','))
            AND fkTestTypeID = 1)
    AND s.fkScoreTypeID = 3

  INNER JOIN StudentScores_Subject sPre
     ON sPre.fkStudentTestID = stPre.pkStudentTestID
    AND sPre.fkTest_SubjectID IN (
          SELECT pkTestSubjectID
          FROM MM_Test_Subjects
          WHERE fkCSTStrandID IN (
                  SELECT number FROM itot(@strAcceptableStrands, N','))
            AND fkTestTypeID = 1)
    AND sPre.fkScoreTypeID = 3

  INNER JOIN MM_Test_PL_SS_Ranges r
     ON r.fkTest_SubjectID = s.fkTest_SubjectID 
    AND r.fkSchoolYearID = ti.fkSchoolYearID 
    AND r.fkTestTypeID = ti.fkTestTypeID
    AND (r.fkGradeID = st.fkGradeID OR r.fkGradeID = 99)

  INNER JOIN MM_Test_PL_SS_Ranges rPre
     ON rPre.fkTest_SubjectID = sPre.fkTest_SubjectID 
    AND rPre.fkSchoolYearID = tiPre.fkSchoolYearID 
    AND rPre.fkTestTypeID = tiPre.fkTestTypeID
    AND (rPre.fkGradeID = stPre.fkGradeID OR rPre.fkGradeID = 99)

  INNER JOIN StudentScores_Subject s2
     ON s2.fkStudentTestID = st.pkStudentTestID
    AND s2.fkTest_SubjectID = s.fkTest_SubjectID
    AND s2.fkScoreTypeID = 2

  INNER JOIN StudentScores_Subject sPre2
     ON sPre2.fkStudentTestID = stPre.pkStudentTestID
    AND sPre2.fkTest_SubjectID = sPre.fkTest_SubjectID
    AND sPre2.fkScoreTypeID = 2

  INNER JOIN Students
     ON Students.pkStudentID = st.fkStudentID

Take a look at your execution plan. My guess is that doing the calculation in the join aka (TestInstancesPre.fkSchoolYearID + 1) is causing indexes not be used correctly. An easy way to test this would be to change your join to:

TestInstances.fkSchoolYearID = TestInstancesPre.fkSchoolYearID 

I have seen performance go way down when doing funky stuff in a join. Things like:

ON t1.column1 = ISNULL(t2.myColumn, 1) 

I believe this is because the query becomes non-sargable. Take a look at this SO post for more details on that.

By having a calculation in your comparison, it can invalidate the use of an index. This usually happens when the datatype of the calculation result is different from the datatype of the column being indexed. Sometimes the cost of calculation is large if it has to be repeated enough times (eg from lots of joins). One solution is to store the calculated value in a special column, eg:

CREATE TABLE TestInstances (
...
nextSchoolYearID int);

And use a trigger or logic to maintain nextSchoolYearID = fkSchoolYearID + 1, then use

ON TestInstances.fkSchoolYearID = TestInstancesPre.nextSchoolYearID)

Also, you have AND StudentTests.pkStudentTestID IN (SELECT * FROM @tempTests) in the first join's on clause, but the values in @tempTests are not related to either table.

Try moving that predicate to a where clause at the end, ie:

SELECT
     ...
WHERE StudentTests.pkStudentTestID IN (SELECT * FROM @tempTests)

Doing this means SELECT * FROM @tempTests will only get executed once, instead of being executed for every row combination of TestInstances and StudentTests.