On hinting spatial indexes and query complexity

After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least hintable) by breaking up the query. Warning. Protracted code example follows. 

If the spatial query is somewhat complex, it's useful to write it out step by step, so we start with the query written out in steps. We're using three tables, a county table, highway table, and POI (points of interest) table. Without pondering the necessity of clipping to the county boundary twice, here's the original logic. 

–Find all banks within 2km of I66 as it runs through Fairfax County
DECLARE @g geography
DECLARE @h geography
DECLARE @i geography
DECLARE @j geography
DECLARE @k geography
SELECT @g = geog from va where fips = 51059 — Fairfax County
SELECT @h = geog from us_hwys where route_num = 'I66' — I66 Hwy
SELECT @i = @h.STIntersection(@g)– I66 within county
SELECT @j = @i.STBuffer(2000)– buffer around I66
SELECT @k = @j.STIntersection(@g)– clip buffer to county boundary
SELECT geog from POI
  WHERE geog.STIntersects(@k)=1 and Description = 'bank'

We thought that it might be better for the overall query performance to write this as a single SQL query, so, doing simple substitution, one subquery at a time, we come up with these two equivalent query sets at the end of the process:  

— A. Almost there… 2-query process
DECLARE @k geography
select @k =
((SELECT geog from us_hwys where route_num = 'I66').STIntersection(
  (SELECT geog from va where fips = 51059)
)).STBuffer(2000).STIntersection(
  (SELECT geog from va where fips = 51059)
)
— uses the spatial index without a hint, total elapsed time for both steps 2 seconds
SELECT geog from POI — with (index(spatial_idx_2))
where geog.STIntersects(@k)=1 and Description = 'bank'
go

— B. Done… 1 query
— But, doesn't use the spatial index
— Total elapsed time, 48 minutes 53 seconds!
SELECT geog from POI  — with (index(spatial_idx_2))  hint doesn't work
where geog.STIntersects(
  ((SELECT geog from us_hwys where route_num = 'I66').STIntersection(
    (SELECT geog from va where fips = 51059)
  )).STBuffer(2000).STIntersection(
    (SELECT geog from va where fips = 51059)
  )
)=1 and Description = 'bank'

The resulting query is too complex for the query processor to even "think of" a plan that uses the spatial index. Even hinting doesn't work. The simpler STIntersects query using the 2-query process can be hinted, but we don't need the hint. The query processor is smart enough to use the spatial index with the simplified version. Oh…

So the moral of the story is not only to be on the lookout for index-hinting opportunities, but that sometimes, if the query is too complex the query processor won't take the hint. The error in this case was:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

In cases like this, it may be necessary to break the query into pieces in order to apply the hint. In this case, it happens with an STIntersects() that used four subqueries, the exact "complexity point" may vary with the query itself.

Of course, in this case, because of the repeating "clipping" steps (clip to a particular county), we can rewrite the query to use common table expressions. This not only makes it easier to read but, by removing the redundant subquery, the query processor decides to use the index with the hint.

WITH GetCounty as (SELECT geog from va where fips = N'51059'),
     GetHighway as(SELECT geog from us_hwys where route_num = 'I66')
SELECT POI.geog from POI with (index(spatial_idx_2)),  GetCounty C,  GetHighway H
WHERE POI.geog.STIntersects(
  H.geog.STIntersection(C.geog).STBuffer(2000).STIntersection(C.geog)
)=1 and Description = 'bank'

So watch out for introduction of excess complexity. Hope this was helpful.