SQL Server Spatial查询优化

Question

我有一个表(Table2)，其中包含一些存储为地理数据类型的区域(多边形)。该表包含1529行。在另一个表(Table1)中，我有大约。22000行，每行都有一个X/Y，我从X/Y创建存储在地理列中的点。

我需要进行空间连接，以找出每个点所属的区域。我已经在两个表上创建了一个空间索引，但我认为查询太慢了。现在，大约需要72秒来建立连接，如下所示：

SELECT ...
FROM [DatabaseA].dbo.Table1 t1 
INNER JOIN [DatabaseB].dbo.Table2 t2 ON t1.Geo.STIntersects(t2.Geo) = 1
WHERE t2.ObjectTypeId = 1 AND t2.CompanyId = 3

请注意，这两个表位于不同的数据库中，但在同一服务器上。

在创建空间索引之前，查询速度要慢得多，我可以看到索引正在被使用。但是，在table2上创建索引不会影响性能，只有在table1上创建索引才能提供更好的性能。这两个索引都有高级网格

当我查看执行计划时，我注意到一个过滤器部分，它占用了71%的时间：

CASE WHEN [Expr1015]>(2) THEN CASE WHEN [Expr1016]=[Expr1017] THEN (1) ELSE (0) END ELSE [DatabaseA].[dbo].[Table1].[Geo] as [t].[Geo].STIntersects([DatabaseB].[dbo].[Table2].[Geo] as [g].[Geo]) END=(1)

所以，我的问题是：

这个查询应该花这么长时间吗？我应该使用其他网格大小吗？过滤器表达式是什么意思？

有没有人有优化这方面的建议？

Answer 1

我也遇到过类似的问题。我有2000个点和85000个多边形。我需要将这些点与匹配的多边形进行匹配。最初，该查询需要8个小时。

SELECT Item.Name, Polygons.Name
FROM dbo.Geofence AS Polygons 
JOIN dbo.ItemLocation AS Points 
ON Polygons.GeoFence.STIntersects(Points.GeoLocation) = 1

问题是points表有一个非聚集索引。添加聚集索引将时间减少到12秒。

添加空间索引(如下代码)可将时间缩短至1秒。我还在点表中添加了一个。

CREATE SPATIAL INDEX [SpatialIndex-Polygons] ON dbo.Polygons
(
    [Geofence]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Answer 2

相反，它必须首先对t2中的所有值运行函数t1.Geo.STIntersects(t2.Geo) -这些值实际上与预先计算的索引值没有关系。

如果速度是您的目标，并且您有存储空间，那么您可以创建第三个表，其中包含预先计算的t1.Geo.STIntersects(t2.Geo)的每个组合的结果。然后，您可以将t1和t2连接到第三个表中的预计算值，这应该能够生成几乎即时的查询结果(对于具有1,529和22,000条记录的源表)。

如果t1和t2中的数据是相对静态的，您可以手动重新运行一个查询来更新第三个表中的数据。如果频繁更改，可以通过触发器对t1和t2执行更新、插入和删除操作，或者您可以将更新、插入和删除操作包装到更新预计算表的存储过程中。