CODEWITHSUNDEEP
sql-servergeospatialspatial-index
Zoinky
Zoinky

Reputation: 5029

SQL Server Spatial Indexes Setup

I currently have two tables cities and listings, both have a field called Position and that is a spatial field. My question is have i set up my indexes correctly or should I add/remove some? For cities I do not show them on a map, I only use one query that is relevant to cities and that is i take a lat/long and i get the 50 nearest cities and for listings I do the nearest as well and I show them on a map like I do here

http://tinyurl.com/on9454y

For both tables I have 2 indexes each that are set as follow.

256 HHHH

/****** Object:  Index [Position_Index]    Script Date: 1/25/2015 3:56:50 PM ******/
CREATE SPATIAL INDEX [Position_Index] ON [dbo].[Listings]
(
[Position]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

16 HHHH

/****** Object:  Index [Spatial_Index]    Script Date: 1/25/2015 3:58:23 PM ******/
CREATE SPATIAL INDEX [Spatial_Index] ON [dbo].[Listings]
(
[Position]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),    
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

using sql server 2012

Upvotes: 1

Views: 621

Answers (2)

Madison
Madison

Reputation: 411

You only need one of the indices for the Position field in each table. Having 2 is redundant and will harm performance from the standpoint that any database writes to those fields would have to update both. You do require having the index in both tables, since you are looking for nearest points in both cities and listings.

In terms of which one to start with, it depends on whether or not you are using points or areas.

For areas (i.e. not just points), you should start with the 16 cells per object one since according to MSDN

By default, the cells-per-object limit is 16 cells per object, which provides a satisfactory trade-off between space and precision for most spatial indexes.

I would apply this to the cities and see how your query performs. If not satisfactory, increase it (probably just keep doubling) until you see no performance gain. I imagine 16 should work just fine for you for the reason stated above, but it will be very data specific. Again, it will only matter if you are not using points.

If your data only contains points (which seems probable in your situation, at least for listings), then the CPO value is not important, and you should get very good performance by setting each level to HIGH as you have already done. From the article here:

In the case of point data, it has been found in most, if not in all, cases that spatial indexes with all grid levels set to HIGH outperform other configurations. Since we are dealing with point data, the CELLS_PER_OBJECT setting is irrelevant and can be set to any legal value (1-8192) without effect.

References

I would highly recommend reading these articles if you have not already done so:

Upvotes: 1

simon_dmorias
simon_dmorias

Reputation: 2483

I agree with what Madison has said. Though I would add that I find most queries require a hint to use the spatial index, for example:

SELECT *
FROM geoTable WITH (INDEX (spatial_index))
WHERE geoColumn.STDistance(@g) < 100

Remember though if you are doing a number of joins and/or where clauses using the spatial index may not actually be any quicker than scanning, so test both scenarios for different cases.

If your tables are fairly big (1 million + rows) spatial indexes tend to slow. Consider spliting them in seperate tables, either per state or country as you cannot partition these indexes.

Upvotes: 0

Related Questions