SQL Server（2014）使用过滤器变量传递给过程时的查询优化

问题描述：

我正在寻找与优化技术有关的帮助，或者提示我继续解决我遇到的问题。对于in子句使用临时表可使我的查询运行超过5秒，将其更改为静态值可在一秒钟内返回数据。我想了解如何优化这一点。SQL Server（2014）使用过滤器变量传递给过程时的查询优化

-- details about the number of rows in table 
dept_activity table 

    - total rows    - 17,319,666 
    - rows for (dept_id = 10) - 36054 

-- temp table 
CREATE TABLE #tbl_depts (
    Id INT Identity(1, 1) PRIMARY KEY 
    ,dept_id integer 
); 

-- for example I inserted one row but based on conditions multiple department numbers are inserted in this temp table 
insert into #tbl_depts(dept_id) values(10); 

-- this query takes more than 5 seconds 
SELECT activity_type,count(1) rc 
FROM dept_activity da 
WHERE (
     @filter_by_dept IS NULL 
     OR da.depart_id IN (
      SELECT td.dept_id 
      FROM #tbl_depts td 
      ) 
     ) 
group by activity_type; 

-- this query takes less than 500 milli seconds 
SELECT activity_type,count(1) rc 
FROM dept_activity da 
WHERE (
     @filter_by_dept IS NULL 
     OR da.depart_id IN (
       10 -- changed to static value 
      ) 
     ) 
group by activity_type;

什么方法可以优化为第一个查询返回数据下一秒。

如何有关使用联合这样'SELECT ACTIVITY_TYPE，计数（1）RC FROM dept_activity哒 WHERE filter_by_dept IS NULL 组由ACTIVITY_TYPE 工会 SELECT ACTIVITY_TYPE，计数（1 ）RC FROM dept_activity哒 WHERE da.depart_id IN（SELECT td.dept_id FROM #tbl_depts TD ）由ACTIVITY_TYPE 基;' – TheGameiswar

问题1寻求性能帮助的应该包括DDL，DML所涉及的表格以及测试数据。如果你的测试数据很大，请尝试脚本化表格的模式和统计信息（'右键单击数据库 - >生成脚本 - >选择特定数据库对象 - >在下一个屏幕选择高级并选择脚本统计）'并粘贴它的问题。有了这个信息任何一个repro你面临同样的问题。否则它变得很难回答你的问题.Pasting服务器版本也有帮助 – TheGameiswar

答

你只用一个值来测试它，但是你的真实情况不同吗？

优化程序在这里存在的问题是它无法知道临时有多少行。 table in-clause将会实际找到，所以它必须做出猜测，并且可能为什么结果是不同的。查看估计的行数（+ vs实际值）可能会对此有所了解。

如果您的条款只包含这一个标准：

@filter_by_dept IS NULL OR da.depart_id IN

这可能是很好的测试，如果你if块分开你的逻辑，成获取所有一个发生了什么，以及过滤其它数据。

如果这不是真实的情况，您可能需要同时测试option (recompile)，这可能会导致更好的计划，但会使用更多的CPU，因为每次都会重新生成计划。或者通过动态SQL构建子句（或者仅使用临时表，但优化掉or语句，或者在没有可观的值的情况下完成in子句），但这可能会变得非常难看。

答

有不同的写作方法。使用按您的要求 -

独立块

IF @filter_by_dept IS NULL 
BEGIN 
    SELECT da.activity_type, count(1) rc 
    FROM dept_activity da 
    GROUP BY da.activity_ty 
END 
ELSE 
BEGIN 
    SELECT da.activity_type,COUNT(1) rc 
    FROM dept_activity da 
    INNER JOIN #tbl_depts td ON td.dept_id = da.depart_id 
    GROUP BY da.activity_ty 
END

动态查询

DECLARE @sql_stmt VARCHAR(5000)  
SET @sql_stmt = ' 
     SELECT activity_type, COUNT(1) rc 
     FROM dept_activity da 
    ' 

IF @filter_by_dept IS NOT NULL 
    SET @sql_stmt = @sql_stmt + ' INNER JOIN #tbl_depts td ON td.dept_id = da.depart_id' 

SET @sql_stmt = @sql_stmt + ' GROUP BY da.activity_type ' 
EXEC(@sql_stmt)

简单的左加入 相比较而言，它可以慢于上述两个方案。

SELECT da.activity_type, count(1) rc 
FROM dept_activity da 
LEFT JOIN #tbl_depts td ON td.dept_id = da.depart_id 
WHERE @filter_by_dept IS NULL OR td.id IS NOT NULL 
GROUP BY da.activity_type

答

最大的问题是最有可能使用“可选参数”。查询优化器不知道天气还是不会@filter_by_dept在下一次执行时会有一个值，它会选择对索引扫描进行安全选择，而不是索引搜索。 OPTION（RECOMPILE）可以成为你的朋友。特别是在简单，易于编译像这样的查询。此外，使用WHERE EXISTS代替IN可能带来收益。

请尝试以下操作...

DECLARE @filter_by_dept INT = 10; 

SELECT 
    da.activity_type, 
    rc = COUNT(1) 
FROM 
    dbo.dept_activity da 
WHERE 
    @filter_by_dept IS NULL 
    OR 
    EXISTS (SELECT 1 FROM #tbl_depts td WHERE da.depart_id = td.dept_id) 
GROUP BY 
    da.activity_type 
OPTION (RECOMPILE);

HTH，杰森

SQL Server（2014）使用过滤器变量传递给过程时的查询优化

相关推荐