如何加快这个 SQL Server 空间查询的速度？

Question

如何加快这个 SQL Server 空间查询的速度？

sql-server-2008performancespatialspatial-index

7

我有一个（我认为）很简单的SQL Server空间查询：

获取所有存在于某个四边形内的美国州（即网页Google/Bing地图的视口/边界框）

SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
    StateId, a.Name, Boundary.STAsText() AS Boundary, 
    CentrePoint.STAsText() AS CentrePoint
FROM [dbo].[States] a
WHERE @BoundingBox.STIntersects(a.Boundary) = 1

运行需要6秒，太慢了。

我该如何调试这个问题，或者我需要对其进行哪些微调？我是否有任何空间索引？我相信是的。

/****** Object:  Index [SPATIAL_States_Boundary]    
        Script Date: 07/28/2010 18:03:17 ******/
CREATE SPATIAL INDEX [SPATIAL_States_Boundary] ON [dbo].[States] 
(
    [Boundary]
)USING  GEOGRAPHY_GRID 
WITH (
    GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
    CELLS_PER_OBJECT = 1024, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

我需要提供关于返回的“地理”数据的更多信息吗？例如，点的数量等等？还是我需要运行“分析器”并从那里提供一些统计数据？

或者我的Cells_per_object / Grids设置不正确（说实话，我真的不知道应该将这些值设置为什么）。

更新

在下面的第一条回复中，@Bobs确认空间索引未被使用，因为主键（聚集索引）比表中50个奇数行的非聚集索引更快，所以我尝试强制使用空间索引。

SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
    StateId, a.Name, Boundary.STAsText() AS Boundary, 
    CentrePoint.STAsText() AS CentrePoint
FROM [dbo].[States] a WITH (INDEX(SPATIAL_States_Boundary))
WHERE @BoundingBox.STIntersects(a.Boundary) = 1

现在查询立即运行。所以我的新问题是，为什么这样修复了呢？

- Pure.Krome

2个回答

2

尝试一下不使用索引提示：

EXEC sp_executesql N'
  SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
      StateId, a.Name, Boundary.STAsText() AS Boundary, 
      CentrePoint.STAsText() AS CentrePoint
  FROM [dbo].[States] a
  WHERE @BoundingBox.STIntersects(a.Boundary) = 1'
, N'@BoundingBox GEOGRAPHY', @BoundingBox

如果这有任何差异，请参阅这里获取更多详细信息：

如果您在SSMS中运行代码，在空间查询周围使用sp_executesql （或使用自己的存储过程，其中包含空间值作为参数），以确保查询代价“知道”参数值在创建查询计划时，也就是在批处理开始或进入存储过程或 sp_executesql时。

- Peter Radocchia

很有趣...所以我将它移入了一个存储过程，并传入boundingBox变量。现在已经过了9秒，而没有使用任何索引。这是在SQL 2008 R2 / 10.50 RTM上的情况。 - Pure.Krome

此外，在SSMS中运行它（如上所述）需要9秒钟，没有使用索引，并且将该代码放入一个存储过程中并传递BoundingBox变量也是一样的结果。:( - Pure.Krome

这很有趣。如果你还没有尝试过，你也可以玩一下 .Filter()。我经常会将 .Filter() 的结果实现到一个临时表中，然后在此基础上进行更精确的操作。 - Peter Radocchia

此外，我建议您在http://social.msdn.microsoft.com/Forums/en-US/sqlspatial上进行跨贴。对于SQL Server空间问题，那里的读者群比SO更好。 - Peter Radocchia

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- bobs · Accepted Answer

看起来您已经有了一个运行查询的最佳计划，很难再进一步优化。以下是一些观察结果。

该查询在PK_States索引上进行聚簇索引扫描，而不使用空间索引。这是因为查询优化器认为使用聚簇索引比其他任何索引更好。为什么？可能是因为States表中的行数较少（50多个加上华盛顿特区、波多黎各等一些其他地方的行）。

SQL Server在8KB页面上存储和检索数据。筛选操作的行大小（请参见估算行大小）为8052字节，这意味着每页有一行，在整个表中大约有50页。查询计划估计它将处理其中的约18行（请参见预计行数）。这不是一个需要处理的重要行数。我的解释没有涉及表中的额外页面，但关键点是数字约为50，而不是50,000页。

那么，为什么它使用PK_States索引而不是SPATIAL_States_Boundry索引呢？按定义，聚簇索引包含表的实际数据。非聚簇索引指向存在数据的页面，因此需要检索更多页面。因此，只有在存在更大数量的数据时，非聚簇索引才变得有用。

可能有一些事情可以做来减少处理的页面数（例如使用覆盖索引），但是您当前的查询已经很好地优化了，您不会看到太多性能提升。