Menu
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
235
When an index contains all the columns needed to satisfy a query, it is called a covering index. Including strategic columns in a nonclustered index can ensure that the most frequent queries can be satisfied entirely from the index without the need for key lookups into the clustered index.
The Create Spatial Index Tool within QGIS enables you to create spatial indexes very quickly for your GIS Layers. A spatial index can be very important on a GIS layer, especially for large datasets, as they will allow queries to run a lot quicker and speed up map rendering.
Density is information about the number of duplicates in a given column or combination of columns and it is calculated as 1/(number of distinct values). The Query Optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table or indexed view.
You can create a rowstore index before there is data in the table. Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order. More to come!
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?
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
