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?

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.

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?