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 Answer 1

1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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