CROSS APPLY performance difference

Question

I have a SQL query that takes about 30 seconds to run that returns 1 record. The function used in the CROSS APPLY is instant when run with the BrandId of this record.

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

However if I just change the table I get the BrandId from for the the CROSS APPLY..

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](ah.RepresentedByBrandId) AS c <-- change here

the query now only takes 2 seconds to run. As I join dbo.Brand b ON cah.RepresentedByBrandId = b.BrandId I would expect them to be the same.

Can someone explain why the huge performance difference?

UPDATE

The difference is because the CROSS APPLY is running on the whole Brand table when I use b.BrandId and the whole AccountHandler table when I use ah.RepresentedByBrandId. The AccountHandler table is considerably smaller.

However I was expecting the CROSS APPLY to run just on the results of the JOINs which is one 1 record. Is this possible or have I miss understood CROSS APPLY?

Answer 1

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.

Answer 2

I had the same issue and I solved it using OUTER APPLY instead of CROSS APPLY.