title: CROSS APPLY with table valued function restriction performance
body: <p>I have problem with <code>CROSS APPLY</code> with parametrised table valued function.
  Here is simplified pseudo code example:</p>
  
  <pre><code>SELECT * 
  FROM (
      SELECT lor.*
      FROM LOT_OF_ROWS_TABLE lor
      WHERE ...
  ) AS lor
  CROSS APPLY dbo.HeavyTableValuedFunction(lor.ID) AS htvf
  INNER JOIN ANOTHER_TABLE AS at ON lor.ID = at.ID 
  WHERE ...
  </code></pre>
  
  <ul>
  <li>Inner select on table <code>LOT_OF_ROWS_TABLE</code> is returning many rows.</li>
  <li>Joining tables <code>LOT_OF_ROWS_TABLE</code> and <code>ANOTHER_TABLE</code> returns only one or few rows. </li>
  <li>Table valued function is very time consuming and when calling for a lot of
  rows the select lasts very long time.</li>
  </ul>
  
  <p><strong>My problem:</strong> </p>
  
  <p>The function is called for all rows returned from <code>LOT_OF_ROWS_TABLE</code> regardless of the fact that the data will be limited when just join <code>ANOTHER_TABLE</code>.</p>
  
  <p>The select has to be in the shown format - it is generated and in fact it is much more dificult.</p>
  
  <p>When I try to rewrite it, it can be very fast, but it cannot be rewritten like this:</p>
  
  <pre><code>SELECT * 
  FROM (
      SELECT lor.*
      FROM LOT_OF_ROWS_TABLE lor
      WHERE ...
  ) AS lor
  INNER JOIN ANOTHER_TABLE AS at ON lor.ID = at.ID 
  CROSS APPLY dbo.HeavyTableValuedFunction(at.ID) AS htvf
  WHERE ...
  </code></pre>
  
  <p><strong>I'd like to know:</strong></p>
  
  <p>Is there any setting or hint or something that forces select to call function only for finally restricted rows?</p>
  
  <p>Thank you.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The table valued function is very complex: <a href="http://pastebin.com/w6azRvxR" rel="noreferrer">http://pastebin.com/w6azRvxR</a>.
  The select we are talking about is "user configured" and generated: <a href="http://pastebin.com/bFbanY2n" rel="noreferrer">http://pastebin.com/bFbanY2n</a>.</p>