Databases
StackOverflow2013
Postsdbo
POCROSS APPLY with table valued function restriction performance
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCROSS APPLY with table valued function restriction performance
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload