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

Answer 2

I believe this is what you are looking for.

Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query

Basically it describes re-writing the query to get a generated plan using the correct order of joins. Then saving off that plan and forcing your existing query (that does not get changed) to use the plan you saved off.

The BOL link I put in even gives a specific example of re-writing the query putting the joins in a different order and using a FORCE ORDER hint. Then using sp_create_plan_guild to take the plan from the re-written query and use it on the original query.