CROSS APPLY vs OUTER APPLY speed difference

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

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

Please feel free to comment this out.

CROSS APPLY is MSSQL-specific... 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.

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

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

CROSS APPLY vs OUTER APPLY speed difference

Related

Recent Posts