x

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?
more ▼

asked May 08 '12 at 04:14 AM in Default

tpcolson gravatar image

tpcolson
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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/
more ▼

answered May 08 '12 at 10:26 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x9
x1

asked: May 08 '12 at 04:14 AM

Seen: 1172 times

Last Updated: May 08 '12 at 10:26 AM