CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Spatial Index is NOT used when SUBQUERY used

Spatial Index is NOT used when SUBQUERY used

Rate This
  • Comments 7

I have found the following link to be invaluable when working with and tuning SQL Server Spatial indexes:  http://technet.microsoft.com/en-us/library/bb895265.aspx

However, the link is not as clear as it could be about the Spatial index selections made by the SQL Server query processing.  Here are a few additional tidbits that may assist you.  (Note:  Similar tips may apply to non-Spatial queries as well.)

1. The Spatial method must be on the left side of the predicate (where clause)

       col.STIntersects(@val) = 1   --    Can use the index if costing is appropriate
       1 = col.STIntersects(@val)   --    Unlikely to use index, use previous form

2. The value passed to the spatial method must be ‘constant like’

       col.STDistance(@val) = 1 * 10000   --    Can use the index if costing is appropriate
       col.STDistance(@val / 10000) = 1   --    Unlikely to use index, use previous form

3. Extension of #2 for more complex operations

/* The subquery form does not consider the index */
Select * from Spat where col2.STIntersects((select col2 from Spat where Id = 23 and col2 is not null))=1

/* Using index hint - getting an error message for this query form */
-- Msg 8622, Level 16, State 1, Line 1
-- Query processor could not produce a query plan because of the hints defined in this query.

Select * from Spat with (index(SpatIDX)) where col2.STIntersects( (select col2 from Spat where Id =23) ) = 1

/* Variable or Join forms attempt to use the index */
Declare @i geography
Set @i = (select col2 from Spat where Id =23)
Select * from Spat  where col2.STIntersects((@i))=1  order by Id

Select s1.* from Spat as s1
join Spat as s2 ON
      s1.col2.STIntersects(s2.col2) = 1
   and s2.Id = 23
order by s1.Id

As you can see the variable or join syntax is a construct the SQL Server query processing can evaluate for Spatial index usage where as the subquery is generally not considered.

Be sure to check the form of your queries to make sure the indexes are properly considered.

Bob Dorr - Principal SQL Server Escalation Engineer

Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
  • Should the second be

    2. The value passed to the spatial method must be ‘constant like’

          col.STDistance(@val) = 1 * 10000   --    Can use the index if costing is appropriate

          col.STDistance(@val) / 10000 = 1   --    Unlikely to use index, use previous form

  • The world is never an empty shell. Get out from you own cocoon and give technology and lookphonenumber.com the chance to help you conquer your needs and searches. Whether you are locating your friends and family or would like to make it big with your business, a reverse phone look up facility comes very handy and easy to use.

  • There will be no flipping of thick white pages anymore because a directory online http://www.lookphonenumber.com will just cost you comfortable mouse clicks! Since 2006, lookphonenumber.com designed its technology to bridge people with people.

  • SEO Castell serves its client with the latest marketing strategies that help achieve the Internet marketing goals. Website Optimization, SEM, SEO, SMO, Google +, Google Local, PPC Management, Brand Development are some of the key highlights of our work.

  • ALTER procedure nearest_neighbor_pompierii

    (@Fire geometry)

    as

    begin

    SELECT TOP 1  *

    FROM MA_Firestations  with(index(idx_Spatial))

    -- where Location.STDistance(@Fire) > 0                   --it ONLY works if i remove the '--' and obtain a real-line

    ORDER BY     Location.STDistance(@Fire) ASC;

    end

  • ALTER procedure Finding_Nearest_Neighbors_Within_a_Fixed_Search_Zone

    (@SearchArea geometry)

    as

    begin

    SELECT TOP 1  *

    FROM MA_Firestations    with(index(idx_Spatial))

    --this one should work without using: with(index()) , but it doesn't ...

    where Location.Filter(@SearchArea) = 1

    ORDER BY     Location.STDistance(@SearchArea) ASC;

    end

  • latest update: my table has but a few records, that's why the execution plan doesn't make use of the spatial index; when i'v switched to using a table with 1 million rows, the spatial index is used; so i guess all is well...

Page 1 of 1 (7 items)