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

Using the following

FROM GRSM.dbo.GRSM_Trails --WITH(index(S319_idx))
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, 2012 at 04:14 AM in Default

avatar image

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

more ▼

answered May 08, 2012 at 10:26 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: May 08, 2012 at 04:14 AM

Seen: 2399 times

Last Updated: May 08, 2012 at 10:26 AM

Copyright 2018 Redgate Software. Privacy Policy