Cross Apply Performance Difference

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.

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.

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.

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.

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.

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.

UNION vs CROSS APPLY performance

Firs query have high IO cost and low CPU cost than second query. second query have low IO and more CPU than first query.

I suggest that use second query, because IO have more affect in performance than CPU. if you can reduce IO of your query and increase CPU cost is better that reduce CPU cost and increase IO cost.

CROSS APPLY Style versus Performance

If there is a performance disadvantage it must be in the compilation stage of query execution. As execution plans are cached the second the time query executes there will be no performance penalty.

SQL Server can see through the CROSS APPLYs and convert them to simple, stacked "compute scalars".

Another potential problem might be the size of the query. Not sure if there are any relevant limitations in terms of number of AST-nodes or joins or so (I guess CROSS APPLY counts as a join when it comes to internal limits).

Apart from these problems I think that CROSS APPLYs are a very nice and fast solution.

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.

---

Related Topics