环境:SQL 2008 R2
我使用子查询创建了派生表,并与主表连接。我只想知道子查询是否仅执行一次,还是将对结果集中的每一行执行子查询。考虑以下示例(虚构表名称仅供参考)
SELECT E.EID,DT.Salary FROM Employees E
INNER JOIN
(
SELECT EID, (SR.Rate * AD.DaysAttended) Salary
FROM SalaryRate SR
INNER JOIN AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID
因此,用于内部联接的子查询将仅执行一次或多次?
如果我使用OUTER APPLY重写上述查询,那么我肯定会为每行执行子查询。见下文。
SELECT E.EID,DT.Salary FROM Employees E
OUTER APPLY
(
SELECT (SR.Rate * AD.DaysAttended) Salary
FROM SalaryRate SR
INNER JOIN AttendanceDetails AD on AD.EID=SR.EID
WHERE SR.EID=E.EID
) DT --Derived Table for outer apply
因此,只想确保Inner Join将只执行一次子查询。
要注意的第一件事是,你的查询是没有可比性的,OUTER APPLY
需要被替换CROSS APPLY
,或INNER JOIN
用LEFT JOIN
。
但是,当它们变得可比时,您可以看到两个查询的查询计划是相同的。我刚刚模拟了一个示例DDL:
CREATE TABLE #Employees (EID INT NOT NULL);
INSERT #Employees VALUES (0);
CREATE TABLE #SalaryRate (EID INT NOT NULL, Rate MONEY NOT NULL);
CREATE TABLE #AttendanceDetails (EID INT NOT NULL, DaysAttended INT NOT NULL);
运行以下命令:
SELECT E.EID,DT.Salary FROM #Employees E
OUTER APPLY
(
SELECT (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
WHERE SR.EID=E.EID
) DT; --Derived Table for outer apply
SELECT E.EID,DT.Salary FROM #Employees E
LEFT JOIN
(
SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;
给出以下计划:
并更改为“内/交叉”:
SELECT E.EID,DT.Salary FROM #Employees E
CROSS APPLY
(
SELECT (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
WHERE SR.EID=E.EID
) DT; --Derived Table for outer apply
SELECT E.EID,DT.Salary FROM #Employees E
INNER JOIN
(
SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;
给出以下计划:
在这些计划中,外部表中没有数据,而员工中只有一行,因此这是不现实的。对于外部应用,SQL Server能够确定员工中只有一行,因此仅对外部表执行嵌套循环联接(即逐行查找)将是有益的。在向员工放置1,000行之后,使用LEFT JOIN / OUTER APPLY产生以下计划:
You can see here that the join is now a hash match join, which means (in it's simplest terms) that SQL Server has determined that the best plan is to execute the outer query first, hash the results and then lookup from employees. This however does not mean that the subquery as a whole is executed and the results stored, for simplicity purposes you could consider this, but predicates from the outer query can still be still be used, for example, if the subquery were executed and stored internally, the following query would present massive overhead:
SELECT E.EID,DT.Salary FROM #Employees E
LEFT JOIN
(
SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID
WHERE E.EID = 1;
What whould be the point in retrieving all employee rates, storing the results, only to actually look up one employee? Inspection of the execution plan shows that the EID = 1
predicate is passed to the table scan on #AttendanceDetails
:
So the answer to the following points is:
It depends. Using APPLY
SQL Server will attempt to rewrite the query as a JOIN if possible, as this will yield the optimal plan, so using OUTER APPLY
does not guarantee that the query will be executed once for each row. Similarly using LEFT JOIN
does not guarantee that the query is executed only once.
SQL is a declarative language, in that you tell it what you want it to do, not how to do it, so you shouldn't rely on specific commands to elicit specific behaviour, instead, if you find performance issues, check the execution plan, and IO statistics to find out how it is doing it, and identify how you can improve your query.
此外,SQL Server不会对子查询进行分级处理,通常将定义扩展到主查询中,因此即使您已编写:
SELECT E.EID,DT.Salary FROM #Employees E
INNER JOIN
(
SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
FROM #SalaryRate SR
INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;
实际执行的内容更像是:
SELECT e.EID, sr.Rate * ad.DaysAttended AS Salary
FROM #Employees e
INNER JOIN #SalaryRate sr
on e.EID = sr.EID
INNER JOIN #AttendanceDetails ad
ON ad.EID = sr.EID;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句