如何控制啟動謂詞在執行計劃中的位置

對於下面程式碼的最後兩個語句，會產生實際的執行計劃。您可以看到啟動謂詞 on@Par1放置在不同的位置，這完全改變了來自test_fn1函式的實際行數。我需要控制這種行為。

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
 insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
 return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
 select s.*
   from dbo.test_fn1(@Par2,';') x
   inner join sys.objects s on s.name = x.item
   where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
  varchar(100))
returns table
as
return (
 select s.*
   from sys.objects s
   inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
   where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)

以下是啟動謂詞使查詢不適用於任何數據的正確計劃。

這是顯示放置啟動謂詞的錯誤行為的計劃。在這兩種情況下，我們都是從同一個函式開始的，只是改變了 T-SQL 中的順序。

在 SQL Server 2016 SP2 上測試。

是否有任何關於 SQL Server 如何放置啟動謂詞的白皮書或文件？

是否有任何關於 SQL Server 如何放置啟動謂詞的白皮書或文件？

不，不是我見過的。他們沒有寫那麼多。有一次我在部落格上寫了他們，我做了很多頭疼的事。

現在，我有一個答案——它不能保證總是有效。它在優化器上玩了一些技巧，您可以通過觀看Query Tuning Mastery: Clash of the Row Goals了解更多資訊，這是 Adam Machanic 的演講。

這樣一來，我們就可以在某種程度上控制優化器決定使用 TOP 堅持謂詞的位置。

函式重寫1：

在這一個中，WHERE子句出現在CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erik ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
   RETURN (   SELECT s.*
              FROM   sys.objects AS s
              CROSS APPLY
                     (   SELECT TOP (2147483647) *
                         FROM   dbo.test_fn1(@Par2, ';') AS x
                         WHERE  s.name = x.item
                         AND    @Par1 = 'CASE1' ) AS ca );
GO

函式重寫2：

在這一個中，WHERE子句出現在CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erikx ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
   RETURN (   SELECT s.*
              FROM   sys.objects AS s
              CROSS APPLY
                     (   SELECT TOP (2147483647) *
                         FROM   dbo.test_fn1(@Par2, ';') AS x
                         WHERE  s.name = x.item ) AS ca
              WHERE  @Par1 = 'CASE1' );
GO

您可以在此處查看生成的計劃。

在計劃 1 中，啟動表達式謂詞出現在 TOP 內部：

在計劃 2 中，它出現在外面。

如果您希望啟動表達式謂詞充當常量掃描，請嘗試以下操作：

CREATE OR ALTER FUNCTION dbo.test_erik_filter ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
   RETURN (   
              SELECT ca.*
              FROM (
                    SELECT TOP (1) 1 AS n 
                    WHERE @Par1 = 'CASE1'
                   ) AS x
              CROSS APPLY 
               (
              SELECT     s.*
              FROM       sys.objects AS s
              INNER JOIN dbo.test_fn1(@Par2, ';') AS x
                  ON s.name = x.item                  
              ) AS ca

        );
GO

如果我們執行兩個不同的測試案例，計劃會略有不同：

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE2', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO 

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE1', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

查看實時查詢計劃（這裡是正常的），有一些早期的重要區別。

沒有行出來，這意味著啟動表達式不正確。

行將從這一行中流出，因為它確實如此。

請記住，數據在查詢計劃中從右向左流動。將過濾器進一步放在計劃的左側對減少工作沒有好處。

Aaron Bertrand 在這裡討論了與正常過濾器類似的問題：

• 我應該使用 NOT IN、OUTER APPLY、LEFT OUTER JOIN、EXCEPT 還是 NOT EXISTS？

順便說一句，如果您的實際程式碼不只是從系統表/視圖中選擇，您應該考慮向它添加SCHEMABINDING屬性，這有助於過濾器的放置。

希望這可以幫助！

我的一個好朋友 Grzegorz (Stasiek) Domański 最初找到了使查詢檢查啟動謂詞的解決方案。這是程式碼（類似於 Erik 解決方案）。

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
  insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
  return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
  select sx.*
  from (select top 1 null x) a
  cross apply(
    select s.*
      from dbo.test_fn1(@Par2,';') x
      inner join sys.objects s on s.name = x.item
  ) sx
  where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
   varchar(100))
returns table
as
return (
  select sx.*
  from (select top 1 null x) a
  cross apply(
    select s.*
      from sys.objects s
      inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
  ) sx
  where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)

以下是執行計劃。標記的地方是啟動謂詞檢查的時刻。這些是實際計劃，因此只要啟動謂詞為假，就不會考慮任何記錄。這是我們期望的。

引用自：https://dba.stackexchange.com/questions/206191