sql - 需要帮助来优化可能出错的空间 SQL 查询

Question

更新:自从写这篇文章后，我们决定将我们的地理空间数据迁移到 ElasticSearch 数据库，这产生了更好的结果。

我是 SQL 的新手，我需要帮助来优化空间查询，以便它在 2 秒内运行。我们已经尝试了在各种网站上找到的一些建议(更多内容见下文)。

背景

我们有一个表 [Id, Geometry]，其中包含大约 300,000 个不同大小和复杂性的几何形状，存储为几何数据类型。我们使用下面的代码片段为 Geometry 创建了一个空间索引。

CREATE SPATIAL INDEX [IX_Geometry_Spatial]
ON [dbo].[Geometries] ([Geometry]) USING  GEOMETRY_AUTO_GRID 
WITH  (
        BOUNDING_BOX = (XMAX = 24.43359375, XMIN = 10.810546875, YMAX = 69.2249968541159, YMIN = 55.2791152920156)
      );

我们想要的是找到与输入几何形状相交的所有几何形状。这是通过使用以下查询完成的。

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('POLYGON ((x0 y0, ...))', 4326); -- A Worst Case Polygon containing 1442 data points

SELECT Count(Id)
FROM Geometries
WHERE Geometries.Geometry.Filter(@g.Reduce(.25)) = 1

对于一些最坏情况下的输入几何形状(大而复杂的多边形)，此执行大约需要 7-10 秒。

这是查询的执行计划:

我们可以看到我们命中了空间索引，但是最昂贵的操作是聚集索引查找(Clustered)

聚集索引查找细节:

空间索引查找细节:

问题

繁重的工作不应该由空间索引而不是聚集索引来完成吗？

是否可以通过更改空间索引的设置来改进查询？我们应该使用什么设置(对于 GRIDS、CELLS_PER_OBJECT 等)？

如何才能整体缩短执行时间，或者您对这种查询的期望是 7-10 秒？

我们尝试过的方法很有帮助

每一个都节省了大约几秒钟。

• 检查索引碎片，并重建索引。
• 将交集方法从 STIntersect() 切换到 Filter()
• 使用 Reduce(.25) 减少了输入几何。这将几何图形从 1442 个数据点减少到 7 个。(如果我们决定使用它，它必须针对不同的输入是动态的，但这是另一个问题。)
• 引入了一个新的表列 SimpleGeometry，其中包含 Geometry 列中所有几何对象的边界框。创建了一个新的 SimpleGeometry 空间索引，并使用 SimpleGeometry 代替 Geometry 进行查找。 (未在查询示例中使用。)

Answer 1

如果您每次添加新形状时都进行计算，然后仅将交点存储在表格中，会怎样？现在您的查询是即时的。