1

I've recently started seeing this weird behavior whereby doing a spatial search results in a degradated experience as I decrease my radius. I dropped and rebuilt my spatial index last night but the same weird result.

Any help would be appreciated.

Background:

Table size = ~360K rows
Table = {ID, Location (geography)}

E.g. Doing a query with Radius=20000 takes 1 sec but decreasing Radius=1000 is still executing after 3 minutes.

Query:

DECLARE @x geography
SET @x = geography::Point(47.5302778, -122.0313889, 4326)

SELECT TOP (25) L.RID, L.Location.STDistance(@x) AS DIST 
FROM Location L
WHERE L.Location.STDistance(@x) <= @Radius

Index:

Right-clicking index gives me the following (I realize PAD-iNDEX isn't supported in SQL Azure, etc. but just copying verbatim what SQL Management Studio gives me)

CREATE SPATIAL INDEX [SPATIAL_Search_Location] ON [dbo].[Search] 
(
 [Location]
)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)
TMC
  • 8,088
  • 12
  • 53
  • 72
bmwlexusman
  • 21
  • 1

1 Answers1

1

Is it anything to do with the efficeny of the index?

I recently discovered the sp_help_spatial_geography_index in built function in MS SQL 2008, and its changed the way I create spatial indexes.

You might find that when using a smaller search area a better index might be Med, Med, High, High. Or a variation on it.

Also, Ive said it before and its got to be worth saying it again.... use an index hint!

SELECT TOP (25) L.RID, L.Location.STDistance(@x) AS DIST  
FROM Location L WITH(INDEX([SPATIAL_INDEX_NAME]))
WHERE 
L.Location.STDistance(@x) <= @Radius
CatchingMonkey
  • 1,391
  • 2
  • 14
  • 36