以不妨礙並行性的方式模擬使用者定義的標量函式

我正在嘗試查看是否有辦法欺騙 SQL Server 使用特定計劃進行查詢。

1. 環境

想像一下，您有一些在不同程序之間共享的數據。因此，假設我們有一些佔用大量空間的實驗結果。然後，對於每個過程，我們知道我們想要使用哪個年/月的實驗結果。

if object_id('dbo.SharedData') is not null
   drop table SharedData

create table dbo.SharedData (
   experiment_year int,
   experiment_month int,
   rn int,
   calculated_number int,
   primary key (experiment_year, experiment_month, rn)
)
go

現在，對於每個過程，我們都將參數保存在表中

if object_id('dbo.Params') is not null
   drop table dbo.Params

create table dbo.Params (
   session_id int,
   experiment_year int,
   experiment_month int,
   primary key (session_id)
)
go

2.測試數據

讓我們添加一些測試數據：

insert into dbo.Params (session_id, experiment_year, experiment_month)
select 1, 2014, 3 union all
select 2, 2014, 4 
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
   2014, 3, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
   cross join master.dbo.spt_values as v2
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
   2014, 4, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
   cross join master.dbo.spt_values as v2
go

3. 獲取結果

現在，很容易通過以下方式獲得實驗結果@experiment_year/@experiment_month：

create or alter function dbo.f_GetSharedData(@experiment_year int, @experiment_month int)
returns table
as
return (
   select
       d.rn,
       d.calculated_number
   from dbo.SharedData as d
   where
       d.experiment_year = @experiment_year and
       d.experiment_month = @experiment_month
)
go

該計劃很好且平行：

select
   calculated_number,
   count(*)
from dbo.f_GetSharedData(2014, 4)
group by
   calculated_number

查詢 0 計劃

4.問題

但是，為了使數據的使用更加通用，我想要另一個功能 - dbo.f_GetSharedDataBySession(@session_id int). 因此，直接的方法是創建標量函式，翻譯@session_id-> @experiment_year/@experiment_month：

create or alter function dbo.fn_GetExperimentYear(@session_id int)
returns int
as
begin
   return (
       select
           p.experiment_year
       from dbo.Params as p
       where
           p.session_id = @session_id
   )
end
go

create or alter function dbo.fn_GetExperimentMonth(@session_id int)
returns int
as
begin
   return (
       select
           p.experiment_month
       from dbo.Params as p
       where
           p.session_id = @session_id
   )
end
go

現在我們可以創建我們的函式：

create or alter function dbo.f_GetSharedDataBySession1(@session_id int)
returns table
as
return (
   select
       d.rn,
       d.calculated_number
   from dbo.f_GetSharedData(
       dbo.fn_GetExperimentYear(@session_id),
       dbo.fn_GetExperimentMonth(@session_id)
   ) as d
)
go

查詢 1 計劃

該計劃是相同的，當然它不是並行的，因為執行數據訪問的標量函式使整個計劃成為串列的。

所以我嘗試了幾種不同的方法，比如使用子查詢而不是標量函式：

create or alter function dbo.f_GetSharedDataBySession2(@session_id int)
returns table
as
return (
   select
       d.rn,
       d.calculated_number
   from dbo.f_GetSharedData(
      (select p.experiment_year from dbo.Params as p where p.session_id = @session_id),
      (select p.experiment_month from dbo.Params as p where p.session_id = @session_id)
   ) as d
)
go

查詢 2 計劃

或使用cross apply

create or alter function dbo.f_GetSharedDataBySession3(@session_id int)
returns table
as
return (
   select
       d.rn,
       d.calculated_number
   from dbo.Params as p
       cross apply dbo.f_GetSharedData(
           p.experiment_year,
           p.experiment_month
       ) as d
   where
       p.session_id = @session_id
)
go

查詢3計劃

但是我找不到一種方法來編寫這個查詢，使其與使用標量函式的查詢一樣好。

幾個想法：

1. 基本上我想要的是能夠以某種方式告訴 SQL Server 預先計算某些值，然後將它們作為常量進一步傳遞。
2. 如果我們有一些中間物化提示，可能會有所幫助。我檢查了幾個變體（多語句 TVF 或帶有頂部的 cte），但到目前為止，沒有一個計劃比具有標量函式的計劃好
3. 我知道即將改進 SQL Server 2017 - Froid：優化關係數據庫中的命令式程序。不過，我不確定它是否會有所幫助。不過，如果在這裡被證明是錯誤的，那就太好了。

附加資訊

我正在使用一個函式（而不是直接從表中選擇數據），因為它更容易在許多不同的查詢中使用，這些查詢通常@session_id作為參數。

我被要求比較實際的執行時間。在這種特殊情況下

• 查詢 0 執行約 500 毫秒
• 查詢 1 執行約 1500 毫秒
• 查詢 2 執行約 1500 毫秒
• 查詢 3 執行約 2000 毫秒。

計劃 #2 使用索引掃描而不是查找，然後通過嵌套循環上的謂詞進行過濾。計劃#3 並沒有那麼糟糕，但仍然比計劃#0 做更多的工作並且工作得更慢。

讓我們假設dbo.Params很少更改，並且通常有大約 1-200 行，不超過，假設 2000 是預期的。現在大約有 10 列，我不希望經常添加列。

Params 中的行數不是固定的，所以每@session_id行都會有一行。列數不固定，這是我不想dbo.f_GetSharedData(@experiment_year int, @experiment_month int)從任何地方呼叫的原因之一，所以我可以在內部向這個查詢添加新列。我很高興聽到對此的任何意見/建議，即使它有一些限制。

在問題中規定的限制範圍內（正如我所理解的那樣），您無法真正安全地在 SQL Server 中準確地實現您想要的，即在單個語句中並通過並行執行。

所以我的簡單回答是否定的。這個答案的其餘部分主要是討論為什麼會這樣，以防萬一。

如問題中所述，可以獲得併行計劃，但有兩個主要品種，它們都不適合您的需求：

1. 一個相關的嵌套循環連接，在頂層循環分配流。鑑於保證來自Params特定session_id值的單行，內部將在單個執行緒上執行，即使它標有並行圖示。這就是為什麼表面上平行的計劃 3表現不佳的原因；它實際上是連續的。
2. 另一種選擇是嵌套循環連接內側的獨立並行性。這裡的獨立意味著執行緒在內側啟動，而不僅僅是與執行嵌套循環連接的外側相同的執行緒。SQL Server 僅在保證有一個外側行且沒有相關連接參數（計劃 2）時才支持獨立的內側嵌套循環並行性。

因此，我們可以選擇具有所需相關值的串列並行計劃（由於一個執行緒）；或者一個內部並行計劃，因為它沒有要搜尋的參數，所以必須掃描。（旁白：確實應該允許使用**一組相關參數來驅動內部並行性，但它從未實現過，可能是有充分理由的）。

那麼一個自然的問題是：為什麼我們需要相關參數？為什麼 SQL Server 不能簡單地直接尋找由例如子查詢提供的標量值？

好吧，SQL Server 只能使用簡單的標量引用來“索引查找”，例如常量、變數、列或表達式引用（因此標量函式結果也可以限定）。子查詢（或其他類似的結構）太複雜（並且可能不安全）而無法推入整個儲存引擎。因此，需要單獨的查詢計劃運算符。這又需要相關性，這意味著沒有您想要的那種並行性。

總而言之，目前確實沒有比將查找值分配給變數然後在單獨的語句中使用函式參數中的方法更好的解決方案。

現在您可能有特定的本地註意事項，這意味著SESSION_CONTEXT值得記憶體年份和月份的目前值，即：

SELECT FGSD.calculated_number, COUNT_BIG(*)
FROM dbo.f_GetSharedData
(
   CONVERT(integer, SESSION_CONTEXT(N'experiment_year')), 
   CONVERT(integer, SESSION_CONTEXT(N'experiment_month'))
) AS FGSD
GROUP BY FGSD.calculated_number;

但這屬於解決方法的範疇。

另一方面，如果聚合性能是最重要的，您可以考慮堅持使用內聯函式並在表上創建列儲存索引（主索引或輔助索引）。您可能會發現列儲存儲存、批處理模式處理和聚合下推的好處無論如何都比行模式並行搜尋提供了更大的好處。

但要注意標量 T-SQL 函式，尤其是列儲存儲存，因為很容易在單獨的行模式篩選器中逐行評估函式。保證 SQL Server 選擇評估標量的次數通常非常棘手，最好不要嘗試。

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