October 11, 2010 at 10:48 am
In doing some stored procedure tuning, I recently came across one that is trying to find all of the locations within a given distance from the point passed in. I tried adding a geography column (I called it "geo" as this is a proof of concept) to the table of locations and putting a spatial index on it and then changing the procedure like so:
alter procedure FindLocations (@lat float, @long float, @distance int) as
declare @p geography = geography::Point(@lat, @long, 4326)
set @distance *= 1609.344 --convert distanct from miles to meters; geography distance returned in meters
select LocationID from Locations where geo.STDistance(@point) < @distance
When I look at the query plan, the spatial index on the geo column isn't being used. Even if I force it with a query hint, it gives worse performance than the table scan that it chooses in the absence of such a hint. Is there something that I'm missing such that the spatial index isn't being used? Thanks in advance.
October 12, 2010 at 9:05 am
It works for me with a test script.
You can see how the following works for you:
use tempdb
GO
CREATE TABLE [dbo].[geo1](
[id] [int] IDENTITY(1,1) NOT NULL,
[latlong] [geography] NULL,
CONSTRAINT [PK_geo1] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
GO
CREATE SPATIAL INDEX [sp1] ON [dbo].[geo1]
(
[latlong]
)USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
insert into geo1 (latlong)
select top 100000 geography::Point((ABS(CHECKSUM(newid()))%1000000)*0.00001,(ABS(CHECKSUM(newid()))%1000000)*0.00001, 4326)
from sys.all_columns c1,sys.all_columns c2
go
-----------------
-- The query below should use the spatial index
declare @lat float=5.55
declare @long float=6.66
declare @p geography = geography::Point(@lat, @long, 4326)
declare @distance int
set @distance=10000
select *, latlong.Lat, latlong.Long, latlong.STDistance(@p)
from geo1
where latlong.STDistance(@p)<@distance
October 12, 2010 at 11:15 am
I ran your script and looked at the execution plan; it's using a clustered index scan on the table. I'm running on SQL2008 SP1 if that makes a difference.
StmtText
-----------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1005]=[tempdb].[dbo].[geo1].[latlong].STDistance([@p])))
|--Compute Scalar(DEFINE:([Expr1003]=[tempdb].[dbo].[geo1].[latlong].Lat, [Expr1004]=[tempdb].[dbo].[geo1].[latlong].Long))
|--Parallelism(Gather Streams)
|--Filter(WHERE:([tempdb].[dbo].[geo1].[latlong].STDistance([@p])<CONVERT_IMPLICIT(float(53),[@distance],0)))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[geo1].[PK_geo1]))
October 12, 2010 at 11:58 am
I have SQL2008 development edition SP1 CU6
select @@version
Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Maybe this is something they have fixed after SP1 ?
October 12, 2010 at 12:13 pm
Yeah, I'm on Enterprise Edition SP1 vanilla. When I get a spare minute, I'll start looking through the CUs and see if any of them addressed spatial index usage. Thanks for your help!
January 30, 2014 at 10:13 am
Here's some info regarding the spatial index in SQL Geometry and MAXDOP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply