CROSS APPLY with table valued function restriction performance

Question

I have problem with CROSS APPLY with parametrised table valued function. Here is simplified pseudo code example:

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 ...

• Inner select on table LOT_OF_ROWS_TABLE is returning many rows.
• Joining tables LOT_OF_ROWS_TABLE and ANOTHER_TABLE returns only one or few rows.
• Table valued function is very time consuming and when calling for a lot of rows the select lasts very long time.

My problem:

The function is called for all rows returned from LOT_OF_ROWS_TABLE regardless of the fact that the data will be limited when just join ANOTHER_TABLE.

The select has to be in the shown format - it is generated and in fact it is much more dificult.

When I try to rewrite it, it can be very fast, but it cannot be rewritten like this:

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 ...

I'd like to know:

Is there any setting or hint or something that forces select to call function only for finally restricted rows?

Thank you.

EDIT:

The table valued function is very complex: http://pastebin.com/w6azRvxR. The select we are talking about is "user configured" and generated: http://pastebin.com/bFbanY2n.

Answer 1

you can divide this query into 2 parts use either table variable or temp table

SELECT lor.*,at.* into #tempresult
FROM (
    SELECT lor.*
    FROM LOT_OF_ROWS_TABLE lor
    WHERE ...
) lor
INNER JOIN ANOTHER_TABLE AS at ON lor.ID = at.ID 
WHERE ...

now do the time consuming part which is table valued function right

SELECT  * FROM #tempresult
CROSS APPLY dbo.HeavyTableValuedFunction(#tempresult.ID) AS htvf