CROSS APPLY vs OUTER APPLY speed difference
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.
Why cross apply is faster than inner join?
CROSS APPLY is meant to be used with functions and tables that return result based on parameters.
So, the fact you querying function is the reason why "CROSS APPLY" is faster.
CROSS APPLY performance difference
Found it.
To force the CROSS APPLY to run on the sub set of results from the JOINs and not on the whole table before the JOINS I used OPTION (FORCE ORDER)
SELECT
b.BrandId,
b.Name,
ah.Type,
c.ContactEmails,
c.ContactNumbers,
c.ContactLinks
FROM
@IdsToFilterBy ids
JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id
JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId
CROSS APPLY dbo.[fn_GetBrandContactDetails](b.BrandId) AS c
OPTION (FORCE ORDER)
This now runs instantly and looking at the execution plan the function is only being called for the one result and not the whole db table.
Benefits of INNER JOIN over CROSS APPLY
You can always use a CROSS APPLY
where you'd use an INNER JOIN
. But there are reasons you might (and often will) prefer INNER JOIN
.
In case the two are equivalent the SQL Server optimizer does not treat them differently in my experience. Therefore, I do not follow the suggestion that a CROSS APPLY
is faster. If apples are compared to apples the performance is, in all the query plans I have seen, identical.
INNER JOIN
is more convenient to write. Also, it is idiomatic. Therefore, it is most legible and maintainable. INNER JOIN
also is more widely supported although that probably does not matter on SQL Server. I also estimate that many developers simply do not know CROSS APPLY
.
When should I use CROSS APPLY over INNER JOIN?
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?
See the article in my blog for detailed performance comparison:
INNER JOIN
vs.CROSS APPLY
CROSS APPLY
works better on things that have no simple JOIN
condition.
This one selects 3
last records from t2
for each record from t1
:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
It cannot be easily formulated with an INNER JOIN
condition.
You could probably do something like that using CTE
's and window function:
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
t2o
ON t2o.t1_id = t1.id
AND t2o.rn <= 3
, but this is less readable and probably less efficient.
Update:
Just checked.
master
is a table of about 20,000,000
records with a PRIMARY KEY
on id
.
This query:
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
),
t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
JOIN q
ON q.rn <= t.id
runs for almost 30
seconds, while this one:
WITH t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
CROSS APPLY
(
SELECT TOP (t.id) m.*
FROM master m
ORDER BY
id
) q
is instant.
Difference between cross apply and inner join based on below example
If my understanding of cross apply is correct the reason you are getting different results here is that CROSS APPLY will apply whatever comes after apply (the right operator) to every row in the left operator (#temp). This means that the number of rows in the result will be the same as the number of rows in #temp. Basing my answer off of "The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output." from https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx.
Note that if you really wanted the results to be the same you could change your cross apply query to this:
SELECT *
FROM #temp t1
CROSS apply (SELECT Sum(col1) AS col1
FROM #temp2 t2
WHERE t1.col1 = t2.col1) A
WHERE A.col1 IS NOT NULL
Also, note that INNER JOIN is defined to be where there is a match on both sides. In your case this means that there were only 3 rows. If you had used a LEFT OUTER join instead you would have gotten the same results in this case as the CROSS APPLY.
INNER JOIN vs CROSS JOIN vs CROSS APPLY
The first two are equivalent. Whether you use an inner join or cross join is really a matter of preference in this case. I think I would typically use the cross join
, because there is no real join condition between the tables.
Note: You should never use cross join
when the intention is a "real" inner join
that has matching conditions between the tables.
The cross apply
is not doing the same thing. It is only choosing one row. If your intention is to get at most one matching row, then use cross apply
. If the intention is to get exactly one matching row, then use outer apply
.
Related Topics
Difference Between Drop Table and Truncate Table
How to Compare Values Which May Both Be Null in T-Sql
How to Write "Not in ()" SQL Query Using Join
Is There Something Equivalent to Argmax in SQL
Is MySQL Limit Applied Before or After Order By
Finding Free Slots in a Booking System
How to Create an "On-The-Fly" Mapping Table Within a Select Statement in Postgresql
How We Can Use Cte in Subquery in SQL Server
Issues with SQL Server Merge Statement
Getting the Number of Rows with a Group by Query
Query to Check Index on a Table
How to Get Input File Name as Column in Aws Athena External Tables
How to Insert a Unique Id into Each SQLite Row
Get List of Computed Columns in Database Table (SQL Server)