Performance

如何在不複製 SQL 的情況下更改聚合函式(使用 SQL)

  • March 28, 2017

在 SQL Server 2016 中,我有一個場景,其中數據將根據大型GROUP BY ROLLUP. 我想要一個儲存過程,它有一個參數,該參數指定使用哪個聚合函式來描述分組,這種方式不會冒 SQL 注入的風險並利用編譯(這是一個繁重的儲存過程)。

我的想法是使用一組查詢來總結特定聚合函式上的數據分組。(例如 agg.DataMin、agg.DataMedian、agg.DataWeightedAverage 等)。然後將這些與 CTE 中的參數一起使用

WITH AggData AS
(
  SELECT * FROM agg.DataMin WHERE @AggFunction = 1 
  UNION ALL
  SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 
  UNION ALL
  SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT ...

我關心的是查詢性能和行業最佳實踐。數據表的大小合理(2+ Gig)。我將不得不添加許多聚合查詢,其中一些是內聯表值函式,用於一些遺漏聚合。

在上面,查詢/表值函式是僅在@AggFunction匹配WHERE條件時執行,還是在返回結果後都執行和過濾?如果是後者,是否有一種方法可以在執行時縮短對不需要的查詢的評估?另外,是否有一些標準方法可以在我忽略的 SQL 中執行此操作?

矛盾檢測可以啟動以確保只執行其中一個語句,在我的簡單測試中,只要有語句級重新編譯提示,它就會執行,但為什麼要冒險呢?例如:

USE tempdb
GO

-- CREATE SCHEMA agg
--DROP TABLE agg.DataMin
--DROP TABLE agg.DataMedian
--DROP TABLE agg.DataWeightedAverage
--GO

CREATE TABLE agg.DataMin ( x INT PRIMARY KEY )
CREATE TABLE agg.DataMedian ( x INT PRIMARY KEY )
CREATE TABLE agg.DataWeightedAverage ( x INT PRIMARY KEY )
GO

INSERT INTO agg.DataMin ( x )
SELECT object_id FROM sys.all_objects

INSERT INTO agg.DataMedian ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'P'

INSERT INTO agg.DataWeightedAverage ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'X'
GO


-- Are there some situations when it wouldn't...
DECLARE @AggFunction INT = 1

;WITH AggData AS
(
  SELECT * FROM agg.DataMin WHERE @AggFunction = 1 
  UNION ALL
  SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 
  UNION ALL
  SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT *
FROM AggData
OPTION ( RECOMPILE )

我的結果: 重新編譯在行動

在這個簡單的例子中,重新編譯時只掃描了左側的一張表,右側掃描了3張表,沒有重新編譯。重新編譯提示允許優化器“查看”參數值並採取相應措施。在將使用參數嗅探的儲存過程中,還需要重新編譯以獲得相同的行為,無論是在語句級別還是在儲存過程級別。

但是我不能說是否沒有不會發生矛盾檢測的情況;你不能證明是否定的。換句話說,我無法證明即使重新編譯也總是會發生矛盾檢測。可能有一些未知的情況,即使重新編譯也不會發生;過度的複雜性浮現在腦海中。

此外,在您的範例中使用 CTE 並沒有真正的優勢,那麼為什麼不保持簡單呢?您可以只編寫一些簡單的過程 SQLIF...THEN...ELSE來保證只有一個語句會觸發,例如

DECLARE @AggFunction INT = 99

IF @AggFunction = 1
  SELECT * FROM agg.DataMin
ELSE IF @AggFunction = 2
  SELECT * FROM agg.DataMedian
ELSE IF @AggFunction = 3
  SELECT * FROM agg.DataWeightedAverage
ELSE
   RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )

在您使用時添加一些參數檢查。希望這能滿足您的要求,即保證在需要時只編譯一個語句,安全且易於實現。

高溫高壓

引用自:https://dba.stackexchange.com/questions/140527