CODEWITHSUNDEEP
sqlazuregeospatialazure-sql-databasespatial
bmwlexusman
bmwlexusman

Reputation: 21

Why are spatial queries taking longer against a spatial index in SQL Azure when I decrease my radius?

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)

Upvotes: 1

Views: 472

Answers (1)

CatchingMonkey
CatchingMonkey

Reputation: 1391

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

Upvotes: 1

Related Questions