CROSS APPLY vs OUTER APPLY速度差 [英] CROSS APPLY vs OUTER APPLY speed difference

问题描述

我正在使用 CROSS APPLY 来加入用户和 GeoPhone 表，一切都运行得很快，但现在我的电话列中的用户值为 NULL.交叉应用会在最终输出中跳过这些行.所以我切换到外部应用.但它的工作速度要慢得多(当输出中的总行数仅增加 1000 时，速度会慢 15 倍以上).

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).

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

对比:

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.

有什么想法吗?

我的最终解决方案:

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

这为非空电话分配实际国家/地区，为空电话分配第一个范围的国家/地区(对于我的情况，这已经是未知").出于某种原因，WHERE dbo.Users.Phone <= dbo.GeoPhone.[End] OR dbo.Users.Phone IS NULL 执行相同的结果，但速度要慢得多.

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 NULL does the same results but greatly slower.

请随时对此发表评论.

推荐答案

CROSS APPLY 是 MSSQL 特定的... 微软申请

CROSS APPLY is MSSQL-specific... Microsoft on APPLY

APPLY 使右侧查询对左侧查询中的每个结果执行一次.CROSS 只考虑像 INNER JOIN 这样的匹配行.使用 OUTER 会考虑左侧查询中的所有行.额外的行伤害了.

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.

我建议您重新编写右侧查询以明确接受 NULL，而不是使用 OUTER APPLY.

I recommend you reformulate your right-side query to explicitly accept NULLs instead of using OUTER APPLY.

这篇关于CROSS APPLY vs OUTER APPLY速度差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！