# 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.

## Other articles

### Over 1000 XEvents in SQL Server 2016 CTP2. Here are the new ones.

Extended events has firmly established itself as the premier diagnostic feature in SQL Server and SQL Server 2016 brings along more events to correspond to

Explore

### Taking the Azure SQL Database row-level security preview for a spin

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of

Explore

### Azure SQL Database V12 Preview – Spatial Fully Functional

Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly,

Explore

### Not a “me-too announcement” blog on Azure SQL Database V12 preview

In general, I usually hate “me too” announcement blog posts. Over the years, I’ve considered it less than useful to simply repeat “Product XXX released

Explore

### AzureML: What components are used by the sample experiments?

A few months ago, I embarked on a project to learn more about data mining, machine learning and, as a prerequisite, statistics. I was tired

Explore

### Book Review: Chris Webb’s “Power Query for Power BI and Excel”

I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like

Explore

## Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.