asked
Why won't my query use the spatial index?
I have a table that contains all the polygons for each country in the world. When I run the following query it doesn't appear to use the spatial index. Please help me understand what I'm doing wrong. Spatial Index is named geom_sidx QUERY: declare @latlonPoint geometry = geometry::STGeomFromText('POINT (-118.767464 35.518066)','4326') SELECT iso3 FROM [WorldPolygon].[dbo].[countryPolygons] where geom.STIntersects(@latlonPoint) = 1 EXECUTION PLAN: SELECT Cost: 0% <-- Filter Cost: 89% <==== Clustered Index Scan (Clustered)[countryPolygons].[PK_countrie_3214EC277756D6ECB] Cost:11% Thanks,
First up, be sure you have all the service packs and cumulative updates installed on your server. Spatial indexing has been patched repeatedly over the years. If you're running a release version you may run into trouble right there. Generally, if the optimizer doesn't choose your index, that suggests that it might not be the right index for that query. Without having access to your data, your structures, and the full actual execution plan, most of what I'm going to suggest are general comments. First, try adjusting the density levels on the objects within the index. I've found that you get very different behavior between the low, medium, and high densities depending on what kind of query you're running and whether or not it includes or excludes more data. I have a couple of [blog posts][1] on [the topic][2]. After that, you can try an index hint to force the use of the index. But pay careful attention to the execution time and the number of reads because the optimizer might be right and scanning the cluster is more efficient for this query. [1]:
http://www.scarydba.com/2009/05/28/spatial-index-performance-brain-pain/ [2]:
http://www.scarydba.com/2009/06/11/more-spatial-headaches/
Good. Just be sure and test it. Just because the index is used doesn't mean it's a faster access method.
14 People are following this question.
