Database Reference
In-Depth Information
Figure 14-6.
POI Lookup: Execution plans
The first and second queries perform a clustered index scan and calculate the distance for every row of the table.
The last query uses a spatial index to lookup such rows. You can see the execution times for the queries in Table
14-2
.
Table 14-2.
POI Lookup: Execution time
dbo.Locations
dbo.LocationsGeo
dbo.LocationsGeoIndexed
473 ms
9,433 ms
18 ms
As you see, the spatial index greatly benefits the query. It is also worth mentioning that without the index, the
performance of the
CalcDistanceCLR
method is better compared to the
STDistance
method.
Although the spatial index greatly improves performance, it has its own limitations. It works within the scope of
the entire table, and all other predicates are evaluated after spatial index operations. This can introduce suboptimal
plans in some cases.
As an example, let's look at the use-case, for when we store POI information on a customer-by-customer basis,
as shown in Listing 14-9.
