问题为什么SQL Server中的空间搜索速度比PostGIS慢？

我正在努力将Postgres的一些空间搜索功能与PostGIS一起移动到SQL Server，即使有索引，我也会看到一些相当糟糕的性能。

我的数据大约有一百万个点，我想找出哪些点在给定的形状内，所以查询看起来像这样：

DECLARE @Shape GEOMETRY = ...
SELECT * FROM PointsTable WHERE Point.STWithin(@Shape) = 1

如果我选择一个相当小的形状，我有时可以得到亚秒，但如果我的形状相当大（有时它们），我可以得到超过5分钟的时间。如果我在Postgres中运行相同的搜索，它们总是在一秒钟之内（事实上，几乎所有搜索都在200毫秒之内）。

我在我的索引上尝试了几种不同的网格大小（全高，全中，全低），每个对象不同的单元格（16,64,256），无论我做什么，时间都保持不变。我想尝试更多组合，但我甚至不知道要走哪条路。每个对象更多的细胞？减？一些奇怪的网格尺寸组合？

我查看了我的查询计划，他们总是使用索引，它根本就没有帮助。我甚至试过没有索引，并没有更糟糕。

有没有人可以给出任何建议？我能找到的一切建议“我们不能给你任何关于索引的建议，只是尝试一切，也许一个会工作”，但是用它花费10分钟创建一个索引，盲目地做这个是浪费大量时间。

编辑：我也贴了这个一个微软论坛。以下是他们在那里要求的一些信息：

我能得到的最好的工作指数就是这个：

CREATE SPATIAL INDEX MapTesting_Location_Medium_Medium_Medium_Medium_16_NDX
    ON MapTesting (Location)
 USING GEOMETRY_GRID
  WITH (
    BOUNDING_BOX = ( -- The extent of our data, data is clustered in cities, but this is about as small as the index can be without missing thousands of points
        XMIN = -12135832,
        YMIN = 4433884,
        XMAX = -11296439,
        YMAX = 5443645),
    GRIDS = (
        LEVEL_1 = MEDIUM,
        LEVEL_2 = MEDIUM,
        LEVEL_3 = MEDIUM,
        LEVEL_4 = MEDIUM),
     CELLS_PER_OBJECT = 256 -- This was set to 16 but it was much slower
  )

我在使用索引时遇到了一些问题，但这是不同的。

对于这些测试，我运行了一个测试搜索（我的原始帖子中列出的那个），每个索引都有一个WITH（INDEX（...））子句（测试网格大小和每个对象的单元格的各种设置），一个没有任何提示。我还使用每个索引和相同的搜索形状运行了sp_help_spatial_geometry_index。上面列出的索引运行得最快，并且在sp_help_spatial_geometry_index中也列为最有效。

运行搜索时，我得到以下统计信息：

(1 row(s) affected)
Table 'MapTesting'. Scan count 0, logical reads 361142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_592590491_384009'. Scan count 1827, logical reads 8041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6735 ms,  elapsed time = 13499 ms.

我也尝试使用随机点作为数据（因为我不能给出我们的真实数据），但事实证明这种搜索对于随机数据来说非常快。这使我们相信我们的问题是网格系统如何与我们的数据一起工作。

我们的数据是整个州的地址，因此有一些非常高密度的区域，但主要是稀疏数据。我认为问题是没有任何网格尺寸设置适用于两者。网格设置为 HIGH，索引在低密度区域返回太多单元格，并且网格设置为 LOW，网格在高密度区域（在 MEDIUM，它并没有那么糟糕，但仍然不擅长）。

我能够获得使用的索引，它只是没有帮助。每个测试都在“show actual execution plan”打开的情况下运行，并且始终显示索引。

10516

2017-08-12 17:00

起源

+1：我也想知道。 - Nordic Mainframe

您的百万点数据是存储为几何（平坦，欧几里德空间）还是地理（圆形，朴实形状的坐标）？如果您正在混合几何和地理，那么您将为转换数学引入性能命中。 - mwalker

@Mwalker，这是几何学，我认为你无论如何都不能混合搭配它们。 - Brendan Long

感谢您对CELLS_PER_OBJECT的评论。我使用CELLS_PER_OBJECT = 16和level = MEDIUM获得6.8秒，并且我将CELLS_PER_OBJECT更改为256并且级别= HIGH，时间降至2.8秒:-) - Guilherme Campos Hazan

答案:

以下是有关SQL-Server的空间扩展以及如何确保有效使用索引的一些注释：

http://sqlskills.com/BLOGS/BOBB/post/How-to-ensure-your-spatial-index-is-being-used.aspx

显然，如果计划者在解析时不知道实际的几何形状，那么他就很难建立一个好的计划。 autor建议插入 exec sp_executesql：

更换：

-- does not use the spatial index without a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1
go

有：

-- this does use the spatial index without using a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
exec sp_executesql 
N'select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1', N'@latlonPoint geometry', @latlonPoint
go

2017-08-12 17:15

我的空间索引正在被使用。我点击了“包括实际执行计划”，它显示了正在使用的空间索引。 - Brendan Long

我试过这个建议只是为了确定，时间和执行计划是一样的。 - Brendan Long

+1提供信息，即使它没有解决OP的问题 - Peter Radocchia

我刚刚度过了类似的问题。特别是，我们正在进行多边形点类型的查询，其中有一组相对较小的多边形，但每个多边形都很大且复杂。

对于多边形表上的空间索引，解决方案结果如下：

使用“几何自动网格”而不是旧的MMLL等。这提供了8级索引而不是旧4级，并且设置是自动的。和...
将'每个对象的单元格'设置为2000或4000.（考虑到默认值为16，这不容易猜到！）

这产生了巨大的变化。它比默认配置中的空间索引快10倍，并且比没有索引快60倍。

2018-05-17 04:23

我相信STIntersects更好地优化使用索引会比STWithin具有更好的性能，特别是对于更大的形状。

2017-08-26 17:31

我们尝试过两者并没有任何区别。 - Brendan Long

对于STIntersects查询，您是否可以在设置统计信息配置文件后发布计划信息？ - Giri

我的直觉反应是“因为微软并不打算让它快速，因为它不是企业特色”。也许我是愤世嫉俗的。

我不确定你为什么要离开Postgres。

2017-08-12 17:20

我怀疑这与它是一个新功能有更多关系;我听说他们应该在下一个版本中做得更好。令我感到困惑的是，我没有听到任何关于它变慢的事情，所以我担心我只是遗漏了一些东西。 - Brendan Long

在SQL Server中使用空间索引后，我开始这样做，这是真的。 - Paul Mendoza

您是否正确设置了空间索引？你的边界框是否正确？里面的所有点都是？在您的情况下，可能HHMM for GRIDS将是最好的（取决于一个bouding框）。

你能尝试使用sp_help_spatial_geometry_index，看看有什么问题吗？ http://msdn.microsoft.com/en-us/library/cc627426.aspx

尝试使用过滤器操作，并告诉我们你得到的穿孔号码？（它只执行主过滤器（使用索引）而不经过二级过滤器（真正的空间操作））

你的设置有问题。空间确实是新功能，但它并没有那么糟糕。

2017-08-26 16:58

我已经尝试了两种尺寸（LLLL，LLMM，LLHH，MMLL等）的每种组合，最好是MMMM，每个对象256个单元。 sp_help_spatial_geometry_index 说主过滤器的效率是90％，我认为这可能是问题（其他的则低至70％）。 Filter 比...快得多 STIntersects 但仍比Postgres慢2-5倍（并不准确）。 - Brendan Long

我们认为问题是我们的数据在高密度区域中相当稀疏，因此静态网格大小的方法没有帮助。如果我们将网格设置为高，则索引在稀疏区域中过于具体，但如果我们将其设置为低，则索引在高密度区域中无用。 - Brendan Long

然后尝试在每个高密度区域周围设置多个空间索引。或者至少将整个美国分成几个大区域。我希望你的大部分数据都在东海岸和西海岸。 - Desinderlase

我们的数据不是整个美国，它只是科罗拉多州。问题是用户可以选择跨越整个州的区域。例如，我的测试查询是从柯林斯堡到丹佛的一个薄盒子。这有两个由低密度区域分隔的高密度区域，我的印象是SQL Server一次只能使用一个空间索引（而不是索引这两个区域中的一个将比我现在更糟）。 - Brendan Long

我可以尝试在星期一再次加载这个数据库再次测试它，因为我们刚刚删除它，因为这种配置比仅使用两个数据库更复杂。 - Brendan Long

您可以尝试将其分为两遍：

选择候选人进入临时表w / .Filter()。
查询候选人w / .STWithin()。

例如：

SELECT * INTO #this FROM PointsTable WHERE Point.Filter(@Shape) = 1
SELECT * FROM #this WHERE Point.STWithin(@Shape) = 1

（取代 SELECT * 只有你需要的实际列来减少I / O）

这种微优化不应该是必要的，但我之前已经看到了不错的性能改进。此外，您将能够通过（1）与（2）的比率来衡量您的指数的选择性。

2017-08-28 04:11

除了SQL服务器使用之外，执行效率问题四叉树索引而PostGIS使用 R-树指数。

在大多数情况下，R树是更好的算法，特别是对于具有不同几何尺寸的大型数据集。

2018-06-23 08:44

我不熟悉空间查询，但它可能是参数化查询问题

尝试使用固定值编写查询（不使用参数）（使用对参数化查询执行缓慢的值）并运行它。将时间与参数化版本进行比较。如果它快得多，那么你的问题是参数化查询。

如果上面的速度要快得多，那么我会用字符串中嵌入的参数值动态构建你的sql字符串，这样你就可以删除引起问题的参数。

2017-08-23 06:58

看起来相当不可能的是查询参数化是这里的问题。 OP说： “如果我选择一个相当小的形状，我有时可以得到亚秒，但如果我的形状相当大（有时它们），我可以得到超过5分钟的时间。” 1。查询性能似乎受查询几何的空间属性的影响。 2。即使您认为参数化查询可能很慢，它们也不应该将快速查询转换为需要超过5分钟（！）才能执行的查询...我无法想出任何合理的解释为什么它们会产生这种效果。 - stakx

问题 为什么SQL Server中的空间搜索速度比PostGIS慢？

答案:

热门问题

问题为什么SQL Server中的空间搜索速度比PostGIS慢？