如何加快此Sql Server空间查询的速度? [英] How can I speed up this Sql Server Spatial query?

问题描述

我(我认为)是一个简单的Sql Server空间查询:

I have (what I think) is a simple Sql Server spatial query:

获取存在于某些4面多边形(即网页的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秒钟才能运行:(

It takes 6 seconds to run :(

这是执行计划....

已删除

以及过滤器"操作的统计信息...

And the stats on the Filter operation...

已删除

现在，我只是不确定如何调试此..以找出需要微调的内容，等等.我有空间索引吗?我相信是这样...

Now, I'm just not sure how to debug this .. to figure out what I need to fine tune, etc. Do I have any spatial indexes? I believe so ...

/****** 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

是否需要提供有关返回的GEOGRAPHY数据的更多信息?例如.点数等?还是我需要运行profiler并从那里提供一些统计信息?

Do I need to provide some more information on the GEOGRAPHY data which is returned? eg. number of points, etc? Or do I need to run profiler and give some stats from there?

或者我的Cells_per_object/网格设置不正确(我真的不知道我应该将这些值设置为TBH).

Or are my Cells_per_object / Grids set incorrectly ( I really have no idea what I should be setting those values to, TBH).

任何人都可以帮忙吗?好吗?

Can anyone please help? Please?

在下面来自@Bobs的第一条回复中，确认空间索引未得到使用，因为主键(聚集索引)比具有50个奇数行的表上的非聚集索引快.强制使用空间索引(针对狗屎n咯咯声):-

After the first reply from @Bobs below confirming that the spatial index was not getting used because the Primary Key (clustered Index) would be faster than a non-clustered index on a table with 50 odd rows ... I then tried to force the Spatial Index (for shits-n-giggles) :-

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

...，然后猜出什么..查询立即运行.

... and guess what .. the query runs instantly.

WTF?其他人知道为什么吗?我是否还需要为此发布查询计划，以帮助解释原因/原因?

WTF? Anyone else know why? Do I need to post a query plan for that, also, to help explain why/what?

推荐答案

看来您有运行查询的最佳计划.要对此进行改进将非常困难.这是一些观察结果.

It appears that you have an optimal plan for running the query. It’s going to be tough to improve on that. Here are some observations.

查询正在对PK_States索引执行聚集索引扫描.它没有使用空间索引.这是因为查询优化器认为最好使用聚集索引而不是任何其他索引.为什么?可能是因为州"表中的行很少(50行，而华盛顿特区，波多黎各等可能还有其他几行).

The query is doing a Clustered Index Scan on the PK_States index. It’s not using the spatial index. This is because the query optimizer thinks it will be better to use the clustered index instead of any other index. Why? Probably because there are few rows in the States table (50 plus maybe a few others for Washington, D.C., Puerto Rico, etc.).

SQL Server在8KB页面上存储和检索数据.筛选器操作的行大小(请参阅估计行大小)为8052字节，这意味着每页一行，整个表中约50页.查询计划估计它将处理这些行中的大约18行(请参阅估计的行数).这不是要处理的大量行.我的解释没有涉及表格中多余的页面，但要指出的是，这个数字大约是50页而不是50,000页.

SQL Server stores and retrieves data on 8KB pages. The row size (see Estimate Row Size) for the filter operation is 8052 bytes, which means there is one row per page and about 50 pages in the entire table. The query plan estimates that it will process about 18 of those rows (See Estimated Number of Rows). This is not a significant number of rows to process. My explanation doesn’t address extra pages that are part of the table, but the point is that the number is around 50 and not 50,000 pages.

因此，回到为什么它使用PK_States索引而不是SPATIAL_States_Boundry索引的原因.根据定义，聚集索引包含表的实际数据.非聚集索引指向数据所在的页面，因此要检索的页面更多.因此，非聚集索引仅在有大量数据时才有用.

So, back to why it uses the PK_States index instead of the SPATIAL_States_Boundry index. The clustered index, by definition, contains the actual data for the table. A non-clustered index points to the page where the data exists, so there are more pages to retrieve. So, the non-clustered index becomes useful only when there are larger amounts of data.

您可以采取某些措施来减少页面处理的数量(例如，使用覆盖索引)，但是您当前的查询已经得到了优化，并且性能不会得到很大改善.

There may be things you can do to reduce the number of pages processes (e.g., use a covering index), but your current query is already well optimized and you won’t see much performance improvement.

这篇关于如何加快此Sql Server空间查询的速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！