Spatial index hints don't work in SQL Server 2008?

Question

Using the following

SELECT *  FROM dbo.GRSM_WETLAND_POLY     CROSS APPLY (SELECT TOP 1 Name, shape                   
FROM GRSM.dbo.GRSM_Trails --WITH(index S319_idx))                  
WHERE GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL
                  ORDER BY GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) ASC) fnc

runs very slow on 134 rows (56 seconds), however, with the index hint
uncommented, it returns

Msg 8635, Level 16, State 4, Line 3
The query processor could not produce
a query plan for a query with a spatial index hint. Reason: Spatial indexes do
not support the comparator supplied in the predicate. Try removing the index
hints or removing SET FORCEPLAN.

Execution plan shows the filter cost at 98%, it's querying against 1400 rows
in the other table, so the total cost is 134 * 1400 individual seeks, which is
where the delay is. On their own, the spatial indexes in each table perform
great, with no fragmentation, 99% page fulness, and use medium for all 4 grid
levels with 16 cells per object. Changing the spatial index properties on either
table had no effect on performance.

Documentation suggests that spatial index hints can only be used in queries
in SQL Server 2012, but surely there's a work around for this?

Answer 1

Hi there,

Spatial index hints definitely work in SQL Server 2008 - what documentation have you seen that suggests otherwise?

The problem with your query is that it looks like you're trying to implement the new nearest-neighbour query pattern that was only introduced in SQL Server 2012 (which requires the additional STDistance() IS NOT NULL predicate) - this won't work in SQL Server 2008/R2.

In fact, it looks like you're following the exact query pattern I gave in my blog post: http://alastaira.wordpress.com/2012/02/22/updating-a-sql-server-table-with-nearest-neighbours-optimised-for-sql-server-2012-sql-azure/ - as per the title of that post, this solution is optimised for SQL Server 2012/Azure. As I wrote there, if you're using SQL Server 2008/R2 you'll have to use one of the other patterns for identifying nearest neighbours, such as those described here: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

What you’re trying to use is a new SQL Server 2012 feature called the nearest neighbor optimization.

 

In SQL Server 2008 and 2008 R2 the only predicates that can use the spatial index (and therefore can use a spatial index hint) are enumerated here: http://technet.microsoft.com/en-us/library/bb895373(v=sql.105).aspx (geometry) and here http://technet.microsoft.com/en-us/library/bb933796(v=sql.105).aspx (geography). So the comparator that error message is complaining about is “Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL” because spatial index only supports STDistance with “<” or “<=” but not “IS NULL” (until SQL Server 2012 with this specific optimization).

 

See http://sqlskills.com/BLOGS/BOBB/post/The-nearest-neighbor-optimization-in-SQL-Server-Denali.aspx for a description. The workaround that I reference in this post is here: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx.

 

Hope this helps, Bob

Answer 3

Thanks for the replies! Now I appear to be having begginers-syntax issues...and I've tried every permutation of populating the table/column values I can think of in your example, and still receive the same error. The tables and columns DO exist, and are recognzied by other queries...

use xxxx

DECLARE @start FLOAT = 1000;  
WITH NearestPoints AS
(   
SELECT TOP(1) WITH TIES Name, TLU_TRAILS.SHAPE.STDistance(TBL_LOCATIONS.SHAPE) AS dist   
FROM Numbers JOIN TLU_TRAILS WITH(INDEX(S13_idx))    
ON dbo.TLU_TRAILS.Shape.STDistance(TBL_LOCATIONS.SHAPE) < @start*POWER(2,Numbers.n)   
ORDER BY n)SELECT TOP(1) * FROM NearestPoints
ORDER BY n, dist

Msg 344, Level 16, State 1, Line 8
Remote function reference 'dbo.TLU_TRAILS.Shape.STDistance' is not allowed, and the column name 'dbo' could not be found or is ambiguous.

Answer 4

There’s a couple of SQL problems in there. You can’t use “dbo.” in an ON clause and can’t use TBL_LOCATIONS.SHAPE anywhere in the query without referring to that table as a table to select from in the FROM clause. So, let’s approach it step-at-a-time. Because there’s a few different SQL constructs in there...

 

Looks like you are trying to apply Isaac’s algorithm to the original problem that you stated. So let’s state your original T-SQL statement as “for each wetland (location), I’d like to return the closest trail”.

 

Since I don’t have your original data, I’ll start by defining the simplest tables that I can that look the same (pardon the weird mixed case in some of the examples):

 

create table numbers (n int);

create table tlu_trails (id int primary key, name char(10), shape geography);

create table tbl_locations (id int primary key, shape geography);

create spatial index s13_idx on tlu_trails(shape); /* shorter definition because using the autogrid spatial index in SQL2012 */

go

 

Caveat: These all compiled for me and ran on the *empty* tables with incident, let me know of any of the get the “can’t produce plan” error.

Now, Issac’s formula starts with a single geography (@x), to get the nearest neighbor. So, for starters, let’s do one location.

 

DECLARE @x geography;

SELECT @x = shape FROM TBL_LOCATIONS where id = 1;  /* do only one location with id of 1 */

DECLARE @start FLOAT = 1000;

WITH NearestPoints AS

(  

SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  

FROM Numbers

JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   

ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  

ORDER BY n

)

SELECT TOP(1) * FROM NearestPoints

ORDER BY n, dist

go

 

To use this with CROSS APPLY, we can make the one-location case into a multi-statement table-valued function. CROSS APPLY will run that function N times, once for each location.

 

create function dbo.get_nearest(@x geography)

returns @t table (

  n int,

  name varchar(10),

  dist float)

as

begin

DECLARE @start FLOAT = 1000;

WITH NearestPoints AS

(  

SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  

FROM Numbers

JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   

ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  

ORDER BY n

)

insert into @t

SELECT TOP(1) * FROM NearestPoints

ORDER BY n, dist;

return;

end

go

 

And use the function with CROSS APPLY, to obtain the nearest trail to each location:

SELECT * FROM TBL_LOCATIONS

CROSS APPLY dbo.get_nearest(TBL_LOCATIONS.shape);

go

 

If you want the whole thing inlined (which usually produces better performance, but may not in this case because of the spatial indexing consideration), you can use a derived table instead of a CTE:

 

-- Original one-location case with derived table

DECLARE @x geography;

SELECT @x = shape FROM TBL_LOCATIONS where id = 1;

DECLARE @start FLOAT = 1000;

SELECT TOP(1) * FROM

(  

SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  

FROM Numbers

JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   

ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  

ORDER BY n

)

AS NearestPoints

ORDER BY n, dist

go

 

And use that (derived table) with CROSS APPLY:

DECLARE @start float = 1000;

SELECT * FROM TBL_LOCATIONS

CROSS APPLY

(

SELECT TOP(1) * FROM

(  

SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(TBL_LOCATIONS.Shape) AS dist  

FROM Numbers

JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   

ON TLU_TRAILS.Shape.STDistance(TBL_LOCATIONS.Shape) < @start*POWER(2,Numbers.n)  

ORDER BY n

)

AS NearestPoints

ORDER BY n, dist

) as a

go

 

Or, for clarity, make the derived table version into an inline table-valued function (which means its inlined, its part of the plan of, the query that uses it, like a view is).

 

create function dbo.vget_nearest(@x geography, @start float)

returns table

as

return

SELECT TOP(1) * FROM

(  

SELECT TOP(1) WITH TIES Numbers.n, Name, TLU_TRAILS.SHAPE.STDistance(@x) AS dist  

FROM Numbers

JOIN dbo.TLU_TRAILS WITH(INDEX(S13_idx))   

ON TLU_TRAILS.Shape.STDistance(@x) < @start*POWER(2,Numbers.n)  

ORDER BY n

)

AS NearestPoints

ORDER BY n, dist

go

 

-- And use it with CROSS APPLY, like this (or hardcode the number 1000 in the TVF):

DECLARE @start FLOAT = 1000;

SELECT * FROM TBL_LOCATIONS

CROSS APPLY

dbo.vget_nearest(TBL_LOCATIONS.shape, @start)

go

 

Let me know if anything is unclear of if it can’t use the index. And (especially) if it helps performance...

Hope this helps,

Cheers,

Bob

Answer 5

Bob, I sincerely thank you for the incredibly succinct and clear example of how to accomplish the query in several different ways. I got them working on SQL Express at home, and will be trying this out on the server at work tomorrow!

Answer 6

@tpcolson - I've marked Bob's post as the answer. (If, for no other reason, than the effort that must have been involved in typing it!)

---

twitter: @alastaira blog: http://alastaira.wordpress.com/