Why is a transition applied faster than an 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 retrieve the 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 shows the cost of its implementation (32 seconds are required):

In the following query, I changed the request to use cross apply instead of inner join and made FunctionB to return the Detail Name for the 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 shows the cost of its implementation (it takes 3 seconds):

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

Why is the first case so slow? Does SQL Server FunctionB run in the second case for each row, or does it cache the results and avoid running the function with the same parameter?

Answer 1

I think cross apply will be faster because it can limit the number of rows to be joined before the actual join is made, so there will actually be fewer lines.

In your second example, the number of rows returned from FunctionB will be less than when combining with the entire table, so the actual join will go faster and the total time will be lower.

How many rows of data are in the tables and are they indexed correctly?

Answer 2

CROSS APPLY is intended for use with functions and tables that return a result based on parameters.

So, the fact that you are requesting a function is the reason that CROSS APPLY is faster.