WHERE-CASE 子句子查询性能 [英] WHERE-CASE clause Subquery Performance

问题描述

该问题可能特定于 SQL 服务器.当我编写查询时，例如:

The question can be specific to SQL server. When I write a query such as :

SELECT * FROM IndustryData WHERE Date='20131231'
AND ReportTypeID = CASE WHEN (fnQuarterDate('20131231')='20131231') THEN  1 
                        WHEN (fnQuarterDate('20131231')!='20131231') THEN  4
                        END;

是否对表的每一行执行 Where 子句中 Case 内的函数调用 fnQuarterDate(或任何子查询)?

如果我事先在一个变量中获取函数(或任何子查询)的值会怎样更好:

How would it be better if I get the function's (or any subquery) value beforehand inside a variable like:

DECLARE @X INT
IF fnQuarterDate('20131231')='20131231'
SET @X=1 
ELSE
SET @X=0
SELECT * FROM IndustryData WHERE Date='20131231'
AND ReportTypeID = CASE WHEN (@X = 1) THEN  1 
                        WHEN (@X = 0) THEN  4
                        END;

我知道在 MySQL 中，如果在 WHERE 子句中的 IN(..) 中有一个子查询，它会为每一行执行，我只是想为 SQL SERVER 找出相同的内容.

I know that in MySQL if there is a subquery inside IN(..) within a WHERE clause, it is executed for each row, I just wanted to find out the same for SQL SERVER.

...

刚刚用大约 30K 行填充表格并找出时差:

Just populated table with about 30K rows and found out the Time Difference:

查询 1= 70 毫秒；查询 2 = 6 毫秒.我认为这可以解释它，但仍然不知道其背后的实际情况.

Query1= 70ms ; Query 2= 6ms. I think that explains it but still don't know the actual facts behind it.

如果有一个简单的子查询而不是 UDF 会有什么区别吗?

Also would there be any difference if instead of a UDF there was a simple subquery ?

推荐答案

我认为该解决方案理论上可以帮助您提高性能，但这也取决于标量函数的实际作用.我认为在这种情况下(我的猜测是将日期格式化为本季度的最后一天)真的可以忽略不计.

I think the solution may in theory help you increase the performance, but it also depends on what the scalar function actually does. I think that in this case (my guess is formatting the date to last day in the quarter) would really be negligible.

您可能希望阅读此页面并提供建议的解决方法:

You may want to read this page with suggested workarounds:

http://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-功能#

因为 SQL Server 必须在每一行上执行每个函数，所以使用任何函数都会招致类似游标的性能损失.

Because SQL Server must execute each function on every row, using any function incurs a cursor like performance penalty.

在解决方法中，有一条评论

And in Workarounds, there is a comment that

我在连接列中使用标量 UDF 时遇到了同样的问题，表现太可怕了.在我用临时表替换 UDF 之后包含 UDF 的结果并在 join 子句中使用它，性能要好几个数量级.MS 团队应该修复 UDF更可靠.

I had the same problem when I used scalar UDF in join column, the performance was horrible. After I replaced the UDF with temp table that contains the results of UDF and used it in join clause, the performance was order of magnitudes better. MS team should fix UDF's to be more reliable.

所以看起来是的，这可能会提高性能.

So it appears that yes, this may increase the performance.

您的解决方案是正确的，但我建议考虑改进 SQL 以使用 ELSE，它对我来说看起来更清晰:

Your solution is correct, but I would recommend considering an improvement of the SQL to use ELSE instead, it looks cleaner to me:

AND ReportTypeID = CASE WHEN (@X = 1) THEN  1 
                    ELSE 4
                    END;

这篇关于WHERE-CASE 子句子查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！