asked
Spatial index hints don't work in SQL Server 2008?
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?
Spatial index hints do work in 2008+. Just make sure you have at least put on Service Pack 1 or you will have troubles. I have [examples of using index hints][1] from years ago on my blog. They do work. I think this query is running slowly because you're applying functions for your ORDER BY statement. That's going to require scans and calculations to arrive at the correct order. And a function to look for NOT NULL is the problem for the hint. Note, it says "Spatial indexes do not suppor the comparator supplied in the predicate." That's your NOT NULL statement. You might want to instead restructure the query to filter out the NULL .Shape values first, then you won't have to look for null distances. Make the selection from GRSM_WETLAND_POLY itself a derived table, then use the CROSS APPLY. I'd imagine the hint will work then. [1]:
http://www.scarydba.com/2009/06/11/more-spatial-headaches/
5 People are following this question.
