为什么我在SQL Server中的空间搜索比PostGIS慢?

我正致力于将一些空间搜索功能从带有PostGIS的Postgres迁移到sqlserver,我看到了一些非常糟糕的性能,即使是使用索引。

我的数据大约有一百万个点,我想找出这些点中的哪一个在给定的形状中,因此查询如下所示:

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

如果我选择一个相当小的形状,我有时可以得到次秒的时间,但如果我的形状相当大(有时是这样),我可以得到5分钟以上的时间。如果我在Postgres中运行同样的搜索,它们总是在1秒之内(事实上,几乎所有搜索都在200毫秒以下)。

我在索引上尝试了几种不同的网格大小(都是高、中、低),每个对象有不同的单元格(16、64、256),无论我做什么,时间都保持不变。我想尝试更多的组合,但我甚至不知道该往哪个方向走。每个对象有更多单元格?少一点?一些奇怪的网格大小组合?

我也把这个贴在 a Microsoft forum . 以下是他们要求的一些信息:

我能得到的最好的工作指标是:

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_索引。上面列出的索引运行速度最快,在sp_help_spatial_geometry_索引中也被列为效率最高的索引。

运行搜索时,我得到以下统计信息:

(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”打开的情况下运行的,它总是显示索引。