16

I have a table with around 2 million records in it. I create 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)) 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)

Anyone know what's going on here?

Improve this question
8
  • I was just going through something similar dba.stackexchange.com/questions/61289/… the other day... I wasn't generating a polygon from text, but was intersecting points and polygons... I specified to use the spatial index on the point, which had great speed results. I then tried using the spatial index on the polygon, and had very poor performance...which seems like the exact opposite of your problem!
    – Inactivated Account
    Commented Mar 21, 2014 at 22:10
  • 4
    If you think about it, changing the size of the search envelope should have a significant impact on the query -- the more rows that are returned through an index, the slower the response. At some point, it becomes faster to full table scan and throw away rows based on envelope. I would suggest that you spend more time with the spatial index options, since you probably have room for optimization of the index.
    – Vince
    Commented Mar 21, 2014 at 22:18
  • Are your records representing points? That was not stated. Also, can you publish the create index syntax that you used? Was it AutoGrid?
    – user41185
    Commented Oct 20, 2015 at 18:19
  • I have used 'Geography Auto Gird' and 'Cells per Object' = 4000. Intersected 110+ million points with ~45K polygons.
    – Michael
    Commented Mar 5, 2017 at 11:16
  • 1
    Another thing you have to remember is that an intersect is a complex operation, first it must look if the elements bound intersect, relatively fast operation through indexes, but then for each item that matches, it must calculate if every single item actually intersects, which is yet another, more costly operation, which becomes even more costly as the polygons are more complex and/or more numerous.
    – AKK2
    Commented Apr 25, 2017 at 12:24

1 Answer 1

Reset to default
1

As commented by @Vince:

If you think about it, changing the size of the search envelope should have a significant impact on the query -- the more rows that are returned through an index, the slower the response. At some point, it becomes faster to full table scan and throw away rows based on envelope. I would suggest that you spend more time with the spatial index options, since you probably have room for optimization of the index.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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