title: CROSS APPLY performance difference
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However if I just change the table I get the BrandId from for the the CROSS APPLY..</p>
  
  <pre><code>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 &lt;-- change here
  </code></pre>
  
  <p>the query now only takes 2 seconds to run.  As I join <code>dbo.Brand b ON cah.RepresentedByBrandId = b.BrandId</code> I would expect them to be the same.</p>
  
  <p>Can someone explain why the huge performance difference?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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.  </p>
  
  <p>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?</p>