1

Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if possible.

Here is my SQL:

        PRINT CHAR(13) + 'Distance (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK INDEX(IX_location))
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

And here are the results:

Distance (No Hint)

(2206 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 295 ms.

Distance + Gender (No Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3323 ms,  elapsed time = 9183 ms.

Distance + Gender (Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 307 ms.
1

It would appear bitwise not in p1.isMale = ~p2.isMale causes the optimiser to pick wrong. Try p1.isMale <> p2.isMale instead

  • Thanks! Very interesting that the optimizer treats those expressions differently given that field is a BIT. – Jake Braun Aug 1 '14 at 17:39
  • I found it weird too. Essentially it's exactly the same expression – MickyT Aug 1 '14 at 22:40

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.