14

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?

  • 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! – DPSSpatial Mar 21 '14 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 Mar 21 '14 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? – gischimp Oct 20 '15 at 18:19
  • I have used 'Geography Auto Gird' and 'Cells per Object' = 4000. Intersected 110+ million points with ~45K polygons. – Michael Mar 5 '17 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 Apr 25 '17 at 12:24
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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