How to speed up my spatial search in SQL Server?

Question

I have a database with about 1 million places (coordinates) placed out on the Earth. My web site has a map (Google Maps) that lets users find those places by zooming in on the map.

The database is a SQL Server 2008 R2 and I have created a spatial column for the location of each marker.

Problem is I need to cut down query time drastically. An example is a map area covering a few square kilometers which returns maybe 20000 points - that query takes about 6 seconds of CPU time on a very fast quad core processor.

I contruct a shape out of the visible area of the map, like this:

DECLARE @shape GEOGRAPHY = geography::STGeomFromText('POLYGON((' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' + 
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' +
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + '))', 4326)

And the query then makes the selection based on this:

@shape.STIntersects(MyTable.StartPoint) = 1

a) I have made sure the index is really used (checked the actual execution plan). Also tried with index hints. b) I have also tried querying by picking everything in a specific distance from the center of the map. It's a little bit better, but it still takes many seconds.

The spatial index looks like this:

CREATE SPATIAL INDEX [IX_MyTable_Spatial] ON [dbo].[MyTable] 
(
    [MyPoint]
)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

What can be done to dramatically improve this search? Should I have a geometry-based index instead? Or are there other settings for the index that are badly chosen (they are the default ones)?

EDIT------------------

I ended up not using SQL Server Spatial indexes at all. Since I only need to do simple searches within a square of a map, using decimal data type and normal <= and >= search is so much faster, and totally enough for the purpose. Thanks everyone for helping me!

Johan · Accepted Answer · 2011-09-01 11:46:19Z

1

SQL server 2008 (and later) supports SPATIAL indexes.

See: http://technet.microsoft.com/en-us/library/bb895373.aspx
for a list of functions that can be used whilst still being able to use an index.
If you use any other function TSQL will not be able to use an index, killing performance.

See: http://technet.microsoft.com/en-us/library/bb964712.aspx
For general info on spatial indexes.

answered Sep 1, 2011 at 11:46

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stuart Blackler Over a year ago

Won't all of the Convert's will hurt the performance as well? (asking for me, not necessarily the OP)

Sten Over a year ago

The index already is a spatial index (see the "CREATE SPATIAL INDEX" I inserted into the question). The Converts: No, it is already done once, before the actual query.

CatchingMonkey · Accepted Answer · 2011-09-08 11:51:44Z

1

Have you tried using an "Index hint"? For Example:

SELECT * FROM [dbo].[TABLENAME] WITH(INDEX( [INDEX_NAME] ))
WHERE 
[TABLENAME].StartPoint.STIntersects(@shape) = 1

answered Sep 8, 2011 at 11:51

CatchingMonkey

1,3912 gold badges14 silver badges37 bronze badges

1 Comment

Sten Over a year ago

Yes. I ended up not using SQL Server Spatial indexes at all. I will add info about it in my question above.

Collectives™ on Stack Overflow

How to speed up my spatial search in SQL Server?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related