Making join condition more exclusive causes query to hang

Question

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

score 4 · Answer 1 · edited May 23 '17 at 12:12

4

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.

edited May 23 '17 at 12:12

Community

1
1

answered Jun 01 '11 at 23:09

Abe Miessler

82,532
99
305
486

Hmm, TestInstances.fkSchoolYearID = TestInstancesPre.fkSchoolYearID still hangs, so there must be something more going on then just an indexing problem. Here's another weird thing, if I change the INNER JOINs on the scores and ranges to LEFT JOINs and then filter out the NULL values in the where clause, the query is fast again. My understanding is that a left join with a WHERE RightTable IS NOT NULL should be equivalent to an INNER JOIN. Am I wrong about this? – nw. Jun 01 '11 at 23:58
I would recommend comparing the execution plans and seeing if any scans are popping up when you use the more exclusive version. It's hard to say without seeing what's really going on. – Abe Miessler Jun 02 '11 at 00:01
Yep, you are absolutely right, there are multiple scans popping up in the more exclusive version, even without the +1 on the join condition... – nw. Jun 02 '11 at 00:27
There code be dead lock contention happening try putting no lock all of your queries – JStead Jun 02 '11 at 00:41
Hmmm, what do your indexes look like? – Abe Miessler Jun 02 '11 at 01:51
There are non-clustered indexes on all primary and foreign key columns involved in this query, except for @tempTests.id (which could theoretically get up to thousands of records, but has been pretty small in all my test cases). – nw. Jun 03 '11 at 01:23

Bohemian · Answer 2 · 2011-06-02T01:41:19.493

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.

Making join condition more exclusive causes query to hang

2 Answers2