Why cross apply is faster than inner join?

Question

I have the following functions:

FunctionA - returns Object ID and Detail ID
FunctionB - returns Detail ID and Detail Name

The following query is used to extract Object ID, Detail ID and Detail Name:

SELECT FunctionA.ID
      ,FunctionA.DetailID
      ,FunctionB.DetailName
FROM FunctionA (...)
INNER JOIN FunctionB (...) 
    ON FunctionA.DetailID = FunctionB.DetailID

The screenshot below display its execution plan cost (it takes 32 seconds):

In the following query I have changed the query to use cross apply instead inner join and made the FunctionB to return Detail Name for specific Detail ID:

SELECT FunctionA.ID
      ,FunctionA.DetailID
      ,FunctionB.DetailName
FROM FunctionA (...)
CROSS APPLY FunctionB (FunctionA.DetailID) 
    ON FunctionA.DetailID = FunctionB.DetailID

The screenshot below display its execution plan cost (it takes 3 seconds):

In the first case FunctionB returns all pairs Detail ID and Detail Name and normally it takes a lot of time. In the second case, FunctionB is executed faster because it returns only Detail Name for specific Detail ID, but it is executed for each Object ID.

Why the first case is so slower? Is SQL Server executing the FunctionB in the second case for each row, or it is caching results and avoids execution of the function with same parameter?

Noelyn

Could you please post an execution plan in .xml format. I am thinking one of two things here. RBAR is forced by the INNER JOIN method. With cross apply the optimizer has the opportunity to optimize differently if it sees fit. I would hazard a guess that the execution plans are not identical and seeing those we could determine. – Beggs

just out of curious which version of sql server are you using? If your using sql express then the restrictions will cause the above due to thread and memory limitations. – Spirelet

@ChocoSmith I am using SQL-SERVER-2012 - check the questions tags – Noelyn

@Noelyn stackoverflow doesn't have a tag for sql-server-express-2012 (only for other express versions) so I was curious if you were running express hence the question and the reference to thread and memory limitations. but since you are running non express then this isn't the answer you are looking for :) – Spirelet

@ChocoSmith Sorry, I did not understand you correctly. – Noelyn

One question I would have is whether your functions are inline table-valued functions or multistatement table-valued functions. That can make a difference in the generated execution plan. – Vesting

Answer 1

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.

Boykin

Answer 2

I think cross apply will be faster sometimes because it can limit the number of rows being joined, before the actual joining is done, so there are fewer rows to actually join.

In your second example, the number of rows returned from FunctionB will be fewer than when joining against the whole table, so the actual join will go faster and the total time will be lower.

How many rows of data are there in the tables, and are they properly indexed?

Possessive