Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table with around 2 million records in it. I created a spatial index, using the defaults other than the bounding box. I've been noticing that some queries are extremely fast, and some are extremely slow. The determining factor appears to the size of the polygon used in the query.

On larger search areas, using WITH(INDEX(SIX_FT5)) slows down the query considerably (from 0 seconds, to 15+ seconds). On smaller search areas, the exact opposite it true.

Here's some of the queries I'm testing with:

Fast:

SELECT TOP(1000) 
  * 
FROM [FT5] 
WHERE 
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Slow:

SELECT TOP(1000) 
  * 
FROM [FT5] WITH(INDEX(SIX_FT5)) -- Index hint is the only difference
WHERE 
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Why is that WITH clause slowing things down sometimes?

share|improve this question
    
Can you include the definition of your index SIX_FT5? – Erik Oct 13 '15 at 3:26

That your indexing is slowing some queries but not others has a lot to do with the way spatial indexes work:

The spatial index is broken into 4 levels which are broken into grids. With each level you will be able to specify a density which of low, medium or high corresponding to 16, 64 or 256 cells respectively. Then within each cell these are then subdivided into the next lower level with its setting.

All of this means that depending on your settings and your data distribution you may need to adjust the density at different levels to get proper index performance. This may also mean that you need to balance your performance of your main queries of this data against your edge cases.

Take a look at this Microsoft TechNet library article for a more detailed explanation:

Spatial Indexing Overview

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.