SQL Server 空间查询优化

Question

我有一张表（Table2），其中包含作为地理数据类型存储的一些区域（多边形）。该表包含1529行。 在另一张表（Table1）中，我有大约22000行，每行都有一个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)

所以，我的问题是：

这个查询是否应该花费如此长的时间？ 我应该使用其他网格尺寸吗？ 那个过滤表达式是什么意思？

有人有优化这个的提示吗？

- user1632306

---

非常类似于许多问题，可以在这里查看我的回答：https://dev59.com/-Wsz5IYBdhLWcg3wy7DU#7672934 - CatchingMonkey

SQL Server 2008还是2012？ - AakashM

你试过吗？我一直发现，尽管它可能会说正在使用它，但添加提示后，性能显著提高。 - CatchingMonkey

是的，我尝试过了，但不幸的是没有成功。 - user1632306

也许您也可以看一下这个答案：http://stackoverflow.com/questions/11211776/fine-tuning-a-geospatial-index/11347642#11347642 - CatchingMonkey

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

问题在于点表格有一个非聚集索引。添加聚集索引将时间缩短到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]

- Paul Totzke

Answer 2

简而言之，您正在使用参数t2.Geo调用一个函数，该函数必须对t2中所有值进行评估，其中t2.ObjecttypeId = 1且t2.CompanyId = 3。在表t2上创建索引并没有真正帮助，因为它无法使用那些预计算的索引值。相反，它必须首先在t2中运行函数t1.Geo.STIntersects(t2.Geo)的所有值 - 这些值几乎与预计算的索引值没有关系。

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

如果t1和t2中的数据相对静态，则可以手动重新运行查询，更新第三个表中的数据。如果它经常更改，则可以通过对t1和t2的更新、插入和删除触发器自动维护，或者您可以将更新、插入和删除包装到更新预计算表的存储过程中。

- AR15Dude

---

我越来越发现，在处理大型空间数据集（10K+记录）时，需要有计算表存储我想要查找的任何关系和属性。空间索引使您能够在短时间内完成许多工作，但对于有用户等待查询结果的OLTP系统来说，它并不实用。至少当您拥有大量数据时是如此，这通常是空间数据的情况。确定交叉点、最近邻居等，并将其存储在表中。根据需要定期刷新。 - Eric