Sifting index slows down the query

Question

Background

I have a table containing POLYGONS / MULTIPOLYGONS that represent customer territories:

• The table contains approximately 8,000 rows.
• Approximately 90% of the polygons are circles.
• The rest of the polygons is one or more states, provinces, or other geographical regions. The raw landfill data for these figures was imported from the US Census .
• The table has a spatial index and a clustered primary key index. There were no changes to the default settings of SQL Server 2008 R2. 16 cells per object, all levels of the environment.

Here's a simplified query that will reproduce the problem I am facing:

DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-76.992188 39.639538)', 4326) SELECT terr_offc_id FROM tbl_office_territories WHERE terr_territory.STIntersects(@point) = 1 

It seems like a simple, simple request takes 12 or 13 seconds to execute, and seems like a very complex execution plan for such a simple request.

In my research, several sources suggested adding an index hint to a query so that the query optimizer uses the spatial index correctly. Adding WITH(INDEX(idx_terr_territory)) has no effect, and the execution plan shows that it refers to my index regardless of the hint.

Polygon reduction

It seemed possible that the polygons of the territory imported from the US Census data were unnecessarily complex, so I created a second column and tested the reduced polygons (w / Reduce () with different degrees of tolerance. Fulfilling the same query as above for the new column, gave the following results:

• No reduction: 12649ms
• Decreased by 10: 7194ms
• Decreased by 20: 6077 m.
• Decreased by 30: 4793 m.
• Reduced by 40: 4397ms
• Reduced by 50: 4290 m.

Clearly heading in the right direction, but lowering accuracy looks like an inelegant solution. Aren't these the ones for which indexes should be? And the execution plan still seems rather complicated for such a basic request.

Spatial index

Out of curiosity, I removed the spatial index and was stunned by the results:

• Requests were faster WITHOUT the index (sub 3 s without reduction, sub 1 s with a tolerance of> 30)
• The execution plan looked far, much simpler:

My questions

• Why is my spatial index slowing down?
• Does my polygon complexity really needed to speed up my query? A decrease in accuracy can cause problems in the future, and it seems that it will not scale very well.

Other notes

• SQL Server 2008 R2 Service Pack 1 was applied
• Further studies proposed perform the query inside the stored procedure. Tried this and nothing has changed.

Answer 1

My first thoughts are to check the bounding coordinates of the index; see if they cover all of your geometry. Secondly, the spatial indexes remaining by default 16MMMM, in my experience, work very poorly. I do not know why this is the default value. I wrote something about setting up a spatial index on this answer .

First make sure the index covers all geometries. Then try reducing the number of cells by one object to 8. If neither of these two offers any improvement, it might be worth the time to complete the spatial index setup procedure in the answer I linked above.

The final thought is that state boundaries have so many vertices and have many state polygons that you test to intersect with, it can very well take this long without decreasing them.

Oh, and since it's been two years since SQL Server 2012, now there is GEOMETRY_AUTO_GRID tessellation that does index tuning for you and does a lot of work most of the time.

Answer 2

It might just be fue for a simpler execution plan executed in parallel, while the other won't. However, there is a warning about the first implementation plan, which may be worth exploring.