SQL CROSS APPLY vs OUTER APPLY速度差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6726696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CROSS APPLY vs OUTER APPLY speed difference
提问by Denis
I was using CROSS APPLY to join Users and GeoPhone tables and everything worked fast but now I have Users with NULL values in Phone column. Cross apply skips these rows in final output. So I switched to OUTER APPLY. But it works greatly slower (more than 15 times as slower when total number of rows in output increased just by 1000).
我正在使用 CROSS APPLY 加入 Users 和 GeoPhone 表，一切都运行得很快，但现在我在 Phone 列中有值为 NULL 的用户。交叉应用会在最终输出中跳过这些行。所以我切换到外部应用。但它的工作速度要慢得多（当输出中的总行数仅增加 1000 时，速度会慢 15 倍以上）。
SELECT TOP (10000) dbo.Users.Login, dbo.Users.Phone, GeoPhone.Country
FROM dbo.Users CROSS APPLY
(SELECT TOP 1 Country
FROM dbo.GeoPhone
WHERE dbo.Users.Phone <= dbo.GeoPhone.[End]) GeoPhone
Versus:
相对：
SELECT TOP (10000) dbo.Users.Login, dbo.Users.Phone, GeoPhone.Country
FROM dbo.Users OUTER APPLY
(SELECT TOP 1 Country
FROM dbo.GeoPhone
WHERE dbo.Users.Phone <= dbo.GeoPhone.[End]) GeoPhone
I'm trying to understand why. As I see execution plan is different. But theoretically I can't see any computation that may cause such slowdown.
我试图理解为什么。正如我所见，执行计划是不同的。但理论上我看不到任何可能导致这种放缓的计算。
Any ideas?
有任何想法吗？
MY FINAL SOLUTION:
我的最终解决方案：
SELECT TOP (10000) dbo.Users.Login, dbo.Users.Phone, GeoPhone.Country
FROM dbo.Users CROSS APPLY
(SELECT TOP 1 Country
FROM dbo.GeoPhone
WHERE ISNULL(dbo.Users.Phone, 0) <= dbo.GeoPhone.[End]) GeoPhone
This assigns actual Country for non-null phones and country from first range for null phones (which is "UNKNOWN" for my case already). For some reason WHERE dbo.Users.Phone <= dbo.GeoPhone.[End] OR dbo.Users.Phone IS NULLdoes the same results but greatly slower.
这为非空电话分配了实际国家/地区，并为空电话分配了第一个范围内的国家/地区（对于我的情况，这已经是“未知”）。出于某种原因WHERE dbo.Users.Phone <= dbo.GeoPhone.[End] OR dbo.Users.Phone IS NULL，结果相同，但速度要慢得多。
Please feel free to comment this out.
请随时对此发表评论。
回答by Magicianeer
CROSS APPLY is MSSQL-specific... Microsoft on APPLY
CROSS APPLY 是特定于MSSQL 的... Microsoft on APPLY
APPLY causes the right-side query to execute once per result in the left-side query. CROSS only considers matching rows like INNER JOIN. Using OUTER considers all rows in left-side query. The extra rows hurt.
APPLY 使右侧查询对左侧查询中的每个结果执行一次。CROSS 只考虑像 INNER JOIN 这样的匹配行。使用 OUTER 会考虑左侧查询中的所有行。额外的行伤害。
I recommend you reformulate your right-side query to explicitly accept NULLs instead of using OUTER APPLY.
我建议您重新制定右侧查询以明确接受 NULL 而不是使用 OUTER APPLY。
回答by A-K
You can try this:
你可以试试这个：
SELECT TOP (10000) dbo.Users.Login, dbo.Users.Phone, GeoPhone.Country
FROM dbo.Users CROSS APPLY
(SELECT TOP 1 Country
FROM dbo.GeoPhone
WHERE dbo.Users.Phone <= dbo.GeoPhone.[End]) GeoPhone
UNION ALL
SELECT TOP (10000) dbo.Users.Login, dbo.Users.Phone, NULL AS Country
FROM dbo.Users
WHERE dbo.Users.Phone IS NULL
Make sure you have an index on dbo.Users.Phone
确保您在 dbo.Users.Phone 上有一个索引
