主页 / dba / 问题 / 140527
Accepted
Edmund
Asked: 2016-06-07 07:15:16 +0800 CST

如何在不复制 SQL 的情况下更改聚合函数（通过使用 SQL）

  • 772

在 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 中执行此操作的标准方法？

stored-procedures performance

1 个回答

  1. Best Answer

    矛盾检测可以启动以确保只运行其中一个语句，在我的简单测试中，只要有语句级的重新编译提示，它就会运行，但为什么要冒险呢？例如：

    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 并没有真正的优势，所以为什么不保持简单呢？您可以只编写一些简单的过程 SQL，IF...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 )

    添加一些参数检查。希望这能满足您的要求，即保证在需要时只编译一个语句，安全且易于实现。

    HTH

    • 3

相关问题