主页 / dba / 问题 / 240584
Accepted
Michael B
Asked: 2019-06-15 06:33:22 +0800 CST

如何强制 SQL Server 通过视图使用我的空间索引？

  • 772

我有一些表，其中包含存储为 lat long 对的属性的事务。（在我的示例架构中，列和数据点更多）。

一个常见的请求是查找在特定点 X 英里范围内发生的交易，并且只检索附近每个物业发生的 5 次最近的交易。

为了完成这项工作，我决定添加一个封装最新逻辑的视图：

create or alter view dbo.v_example
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example;

因此查询可能如下所示：

select *
from dbo.v_example
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

不幸的是，当我通过视图查询时，SQL Server 不想使用空间索引。如果我删除schemabinding并尝试在视图上添加提示，我会发现查询处理器无法创建计划。

如何封装逻辑并仍然让它使用我的空间索引？

这是一个带有示例数据和计划形状的 db<>fiddle。

该表要大得多，扫描它然后进行聚集索引查找然后找到附近的点要慢得多。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    窗口功能和视图

    最近我回答了一个关于视图和窗口功能的不同问题，但并非所有答案都适用于此处。

    两个区别是这个例子geography在窗口函数上使用了数据类型和过滤器。这里的积极部分是你没有绑定到窗口函数，并且可以使用类似的东西CROSS APPLY来获得一个latitude&longitude组合的最接近的结果。

    上一个问题+答案

    简而言之，在应用过滤之前计算窗口函数。

    例如

    where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

    可以在Paul White 2013 年的这篇博文中找到有关窗口功能和视图的更多信息。

    测试

    作为第一个测试，当ROW_NUMBER()从视图中省略时结果很清楚

    create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

set statistics xml on;
select *
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1

    产生预期的高性能查询计划。

    DB<>小提琴

    但是你显然仍然想要额外的过滤

    保持视图+窗口功能

    您可以选择在之后添加窗口功能，如下所示：

    create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

    &

    set statistics xml on;
;WITH CTE AS
(
select most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
,*
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)
SELECT
* 
FROM CTE
WHERE most_recent is not null;

    再次导致预期的执行计划。

    DB<>小提琴

    使用内联表值函数 + 窗口函数

    CREATE FUNCTION dbo.F_Example
(
    @P1 INT

)  
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent FROM
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <= 1609.344e1
) AS A
WHERE most_recent= @P1
);

EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.F_Example(@P1)',N'@P1 INT',@P1 = 1

    产生您期望的查询计划。

    DB<>小提琴

    使用 CTE + 窗口函数

    ;WITH CTE AS
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= row_number() over (partition by latitude,longitude order by transaction_dt desc)
FROM dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)

SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent
FROM CTE2
WHERE most_recent < 5;

    使用CROSS APPLY和TOP(4) 不使用窗口函数

    SELECT example_id
      ,a.transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example e1
CROSS APPLY(
SELECT TOP(4) transaction_dt
FROM dbo.example e2
WHERE e1.latitude = e2.latitude  and e1.longitude = e2.longitude
GROUP BY latitude,longitude,transaction_dt
ORDER BY transaction_dt desc
) as a
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
AND e1.transaction_dt = a.transaction_dt
ORDER BY transaction_dt desc;

    我使用了，因为结果集不是top(4)基于< 5<= 5

    使用该CROSS APPLY方法时，还要添加一个索引，以删除索引假脱机和排序：

    在此处输入图像描述

    CREATE INDEX IX_latitude_longitude_transaction_dt
ON dbo.example(latitude,longitude,transaction_dt);

    在此处输入图像描述

    不用说，您可以将CROSS APPLY解决方案添加到视图中，并以与以前相同的方式查询它，这里有一个示例。以上所有示例都在一个 DB<>Fiddle here中。

    MichaelB结束解决方案评论

    感谢 Randi，我能够使用您的交叉应用的想法将我的逻辑重写为选择子查询。作为额外的奖励，如果我没有在我选择的列中提及该字段，我不会受到性能影响。

    DB<>小提琴

    create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= (
          select a.transaction_dt
          intersect
          select top 5 b.transaction_dt
          from dbo.example b
          where a.latitude=b.latitude
                and a.longitude=b.longitude
          order by transaction_dt desc
      )
from dbo.example a;

    &

    select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
      and most_recent is not null
    • 5

相关问题