Spatial Index is NOT used when SUBQUERY used

I have found the following link to be invaluable when working with and tuning SQL Server Spatial indexes:  https://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

Comments

• Anonymous
  December 12, 2013
  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

• Anonymous
  January 22, 2014
  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.

• Anonymous
  January 30, 2014
  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.

• Anonymous
  January 30, 2014
  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.

• Anonymous
  May 04, 2014
  The comment has been removed

• Anonymous
  May 05, 2014
  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

• Anonymous
  May 06, 2014
  The comment has been removed