CLR Types - Pro SQL Server Internals - page 282

Listing 14-9. Customer-based POI Lookup: Table creation
create table dbo.LocationsGeo2
(
CompanyId int not null,
Id int not null identity(1,1),
Location geography not null,
constraint PK_LocationsGeo2
primary key clustered(CompanyId,Id)
);
-- 12,070,100 rows; 50 companies; 241,402 rows per company
;with Companies(CID)
as
(
select 1
union all
select CID + 1 from Companies where CID < 50
)
insert into dbo.LocationsGeo2(CompanyId,Location)
select c.CID, l.Location
from dbo.LocationsGeo l cross join Companies c;
create spatial index Idx_LocationsGeo2_Spatial
on dbo.LocationsGeo2(Location);
In this case, when we perform POI lookup for a specific company, the CompanyId column must be included as
the predicate to the queries. SQL Server has two choices on how to proceed: the first choice is a clustered index seek
based on the CompanyId value calling STDistance method for every POI that belongs to the company. The second
choice is to use a spatial index, find all POIs within the specified distance regardless of the company to which they
belong, and, finally, join it with the clustered index data. Let's run the queries shown in Listing 14-10.
Listing 14-10. Customer-based POI Lookup: Test queries
declare
@Lat decimal(9,6) = 47.620309
,@Lon decimal(9,6) = -122.349563
,@CompanyId int = 15
declare
@g geography = geography::Point(@Lat,@Lon,4326)
select count(*)
from dbo.LocationsGeo2
where Location.STDistance(@g) < 1609 and CompanyId = @CompanyId
select count(*)
from dbo.LocationsGeo2 with (index=Idx_LocationsGeo2_Spatial)
where Location.STDistance(@g) < 1609 and CompanyId = @CompanyId
Neither method is efficient when a table stores a large amount of data for a sizable number of companies. The
execution plan of the first query utilizing clustered index seek shows that it performed an STDistance call 241,402
times, or once per every company POI. The execution plan is shown in Figure 14-7 .