空间索引减慢查询速度 [英] Spatial index slowing down query
问题描述
我有一个表，其中包含代表客户地区的多边形/多多边形:
I have a table that contains POLYGONS/MULTIPOLYGONS which represent customer territories:
- The table contains roughly 8,000 rows
- Approximately 90% of the polygons are circles
- The remainder of the polygons represent one or more states, provinces, or other geographic regions. The raw polygon data for these shapes was imported from US census data.
- The table has a spatial index and a clustered index on the primary key. No changes to the default SQL Server 2008 R2 settings were made. 16 cells per object, all levels medium.
这是一个简化的查询，它将重现我遇到的问题:
Here's a simplified query that will reproduce the issue that I'm experiencing:
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
看起来简单，直接的查询需要12到13秒才能执行，并且对于这样简单的查询，它具有非常复杂的执行计划.
What seems like a simple, straightforward query takes 12 or 13 seconds to execute, and has what seems like a very complex execution plan for such a simple query.
在我的研究中，一些资料来源建议向查询添加索引提示，以确保查询优化器正确使用空间索引.添加WITH(INDEX(idx_terr_territory))无效，并且从执行计划中可以明显看出，无论提示如何，它都引用我的索引.
In my research, several sources have suggested adding an index hint to the query, to ensure that the query optimizer is properly using the spatial index. Adding WITH(INDEX(idx_terr_territory)) has no effect, and it's clear from the execution plan that it is referencing my index regardless of the hint.
从美国人口普查数据导入的地区多边形似乎不必要地复杂，因此我创建了第二列，并测试了简化的多边形(w/
It seemed possible that the territory polygons imported from the US Census data are unnecessarily complex, so I created a second column, and tested reduced polygons (w/ Reduce() method) with varying degrees of tolerance. Running the same query as above against the new column produced the following results:
- 不减少:12649ms
- 减少10:7194ms
- 减少20:6077ms
- 减少30:4793毫秒
- 减少40:4397毫秒
- 减少50:4290毫秒
显然是朝着正确的方向前进，但是降低精度似乎是一个不明智的解决方案.这不是应该用于索引的内容吗?对于这样的基本查询，执行计划似乎仍然非常复杂.
Clearly headed in the right direction, but dropping precision seems like an inelegant solution. Isn't this what indexes are supposed to be for? And the execution plan still seems strangly complex for such a basic query.
出于好奇，我删除了空间索引，并被结果惊呆了:
Out of curiosity, I removed the spatial index, and was stunned by the results:
- 在没有索引的情况下查询速度更快(小于3秒，无还原，小于1秒，还原容差> = 30)
- 执行计划看起来非常简单:
- 为什么我的空间索引会减慢速度?
- 为加速查询，是否真的有必要降低多边形的复杂度?降低精度可能会带来很多问题，而且扩展性似乎不太好.
其他说明
- 已应用SQL Server 2008 R2 Service Pack 1
- 更多研究建议在存储过程中运行查询.尝试了一下，似乎没有任何改变.
- SQL Server 2008 R2 Service Pack 1 has been applied
- Further research suggested running the query inside a stored procedure. Tried this and nothing appeared to change.
Other Notes
推荐答案
我的第一个想法是检查索引的边界坐标；看看它们是否涵盖了您的整个几何.其次，以我的经验，默认为16MMMM的空间索引的性能非常差.我不确定为什么这是默认设置.我在此答案上写了一些有关空间索引调整的内容.
My first thoughts are to check the bounding coordinates of the index; see if they cover the entirety of your geometries. Second, spatial indexes left at the default 16MMMM, in my experience, perform very poorly. I'm not sure why that is the default. I have written something about the spatial index tuning on this answer.
首先确保索引覆盖了所有几何.然后尝试将每个对象的像元减少为8.如果这两个方面都没有提供任何改进，那么可能值得您花时间在我上面链接的答案中运行空间索引调整过程.
First make sure the index covers all of the geometries. Then try reducing cells per object to 8. If neither of those two things offer any improvement, it might be worth your time to run the spatial index tuning proc in the answer I linked above.
最终的想法是，状态边界具有如此多的顶点，并且要测试要与之相交的状态边界多边形，因此很可能要花很长时间而不缩小它们.
Final thought is that state boundaries have so many vertices and having many state boundary polygons that you are testing for intersection with, it very well could take that long without reducing them.
哦，自从SQL Server 2012开始已经两年了，现在有一个
Oh, and since it has been two years, starting in SQL Server 2012, there is now a GEOMETRY_AUTO_GRID tessellation that does the index tuning for you and does a great job most of the time.
这篇关于空间索引减慢查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
