SQL Server 2016不执行原查询获取行数优化冗余谓词方法

场景说明 #

接手运行耗时极长的SQL查询，计划通过移除冗余谓词提升查询性能。

示例基表数据 #

冗余谓词示例 #

实际场景存在大量同类冗余谓词，例如数据层已限制itemNo字段固定为4位长度，因此datalength(itemNo)=4属于完全无效的冗余谓词。
带冗余谓词的原查询语句：

select distinct itemNo, itemType
from @src 
where datalength(itemNo)=4

原有排查逻辑 #

逐个移除可疑谓词，对比移除前后查询返回的总行数：只要行数一致，即可判定该谓词可安全移除。

当前问题 #

该查询单次执行时长超过45分钟，需要找到无需先执行完整原查询、即可直接获取指定查询返回总行数的方法。
已知以下方案无法满足需求：

• select @@ROWCOUNT必须等查询完整执行、返回全部结果后才能拿到行数，无法缩短耗时
• SQL Server 2016不支持多列同时作为COUNT(DISTINCT)的参数，以下写法直接报错无法运行：

/* SQL Server 2016中不支持该语法，执行失败 */
select COUNT(distinct itemNo, itemType)
from @src 
where datalength(itemNo)=4

环境信息 #

• 数据库版本：Microsoft SQL Server 2016
• 已完成的测试代码：

declare @src as table (itemNo integer, itemType varchar(max));
insert into @src
select * from
(values(1000,'camera'),(1000,'camera'), (1000,'camera'), (1001,'mobilePhone'),(1002,'VR gear'),(1003,'other')) t(a,b)

-- 基础查询
select distinct itemNo, itemType
from @src 
where datalength(itemNo)=4

select @@ROWCOUNT

可行方案 #

不需要返回全量去重结果集，直接统计去重后的行数即可，执行效率远高于执行原SELECT DISTINCT语句，以下两种写法在SQL Server 2016中均可正常使用，逻辑和原查询完全等价：

• 写法1：子查询分组后计数
  先通过GROUP BY对目标字段去重，再统计分组总数，无拼接冲突风险，是最稳妥的方案

SELECT COUNT(*) AS query_row_count
FROM (
    SELECT itemNo, itemType
    FROM @src
    -- 此处替换为待测试的谓词组合即可
    WHERE datalength(itemNo)=4
    GROUP BY itemNo, itemType
) AS temp

• 写法2：多列拼接后去重计数
  用不会出现在字段值中的特殊字符（比如CHAR(1)，常规业务数据几乎不会包含该控制字符）拼接多列后，再做单列COUNT(DISTINCT)，写法更简洁

SELECT COUNT(DISTINCT CONCAT(itemNo, CHAR(1), itemType)) AS query_row_count
FROM @src
-- 此处替换为待测试的谓词组合即可
WHERE datalength(itemNo)=4

排查时分别统计「带待验证谓词」和「移除待验证谓词」两个版本的计数值，两个数值完全相等就说明该谓词没有过滤任何数据，可以安全移除。

内容的提问来源于stack exchange，提问作者smpa01