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

问题描述

该问题可能特定于SQL Server. 当我编写查询时:

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.

...

仅填充大约3万行的表，然后发现时差:

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

Query1 = 70ms;查询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:

因为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屋！