Can I create a "Covering, Spatial" index in SQL Server 2008?
I currently have a site with a table that has Lat/Long float columns, and an index over those 2 columns plus another one I need to retrieve.
I'm constantly querying this table to get the rows that fall within a radius from a certain point (I'm actually getting a square for speed), but I only need the fields that are already indexed, so this index is in fact covering, and the execution plan has only 2 steps:
Index Seek (cost: 100%) and SELECT (cost: 0%)
Now, I'm trying to take advantage of the spatial features of SQL 2008. I've created the Geography column, filled it, created the spatial index, the works.
And it all works fine, except that the execution plan has a million steps, and 74% of the time is spent on a Clustered Index Seek, where it joins the rows it found in the Spatial Index to the actual table, to get the rest of the data...
(The Spatial Index Seek takes 1% of the Execution Plan Cost)
So, apparently, it IS using the Spatial index appropriately and finding the records I need much faster than before with my "regular" index over Lat/Long, but the joining to the main table is KILLING me, the Spatial query takes 7 times as long as my old one.
Is there any way to add more columns to the spatial index so that it'll be covering and it can do things in one step, just like it was doing before?
Are there other things I could do to improve this situation?
UPDATE: I found that "regular" indexes can "include" other columns using the INCLUDE keyword (which I didn't know about, I used to just include the columns in the index itself)
According to the documentation here, that clause is not an option for Spatial Indexes... Any ideas?
Thanks!
Daniel
No, unfortunately there is currently no way to create a covering spatial index - the query will always do the bookmark lookup to the base table in order to get the geography value for the row.
For STIntersects, this is clearly always required as we still need to perform the secondary filter on the actual geography object to verify that it actually intersects the parameter object. However, if you do not require exact answers and can use Filter(), then it would be possible to provide the primary key columns from the index without doing the lookup to the base table at all. Supporting this is something we are considering for the next release.
In terms of speeding up your current query, have you tried using Filter() and tuning your index with the output from sp_help_geography_index?
链接地址: http://www.djcxy.com/p/43118.html上一篇: Log4J能够从磁盘恢复完整？