我的数据大约有一百万个点,我想找出哪些点在给定的形状内,因此查询看起来像这样:
DECLARE @Shape GEOMETRY = ...
SELECT * FROM PointsTable WHERE Point.STWithin(@Shape) = 1
如果我选择一个相当小的形状,有时可以获得亚秒级的时间,但如果我的形状相当大(有时候确实很大),我可能需要超过5分钟的时间。如果我在Postgres中运行相同的搜索,它们总是在一秒以下(事实上,几乎所有搜索都在200毫秒以下)。
我尝试了几种不同的索引网格大小(全部高、全部中、全部低)、每个对象的单元格数(16、64、256),无论我做什么,时间都保持相当稳定。我想尝试更多的组合,但我甚至不知道该走哪个方向。每个对象的单元格数更多?更少?一些奇怪的网格大小组合?
我查看了我的查询计划,它们总是在使用索引,但完全没有帮助。我甚至尝试过没有索引,也没有太大的恶化。
有人能给出任何建议吗?我找到的所有东西都表明“我们不能就索引给你任何建议,只需尝试一切,也许会有一个有效”,但创建索引要花费10分钟,这样盲目地做是巨大的时间浪费。
编辑: 我还在Microsoft论坛上发布了这篇文章。下面是他们在那里要求的一些信息:
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”下,情况没有那么糟糕,但仍然不太好)。 我能够获得使用的索引,但它没有帮助。每个测试都打开了“显示实际执行计划”,并且始终显示索引。