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.

Hornwort

Stating the obvious maybe: If only you could change the order of the joins in the query text. Then you could have used the FORCE_ORDER query hint. How is the code generated? Is there no way you could change the behavior on that end? – Gussie

Change your dbo.HeavyTableValuedFunction(..) multi-line table-valued function to an inline Table-Valued function. – Bordure

@RBarryYoung: too complex to simply rewrite to an inline function – Hornwort

@PavelHodek Ah, well, that's what's causing your problem, so if you won't show it to us, there's little we can do to help. – Bordure

@RBarryYoung: The table valued function: pastebin.com/w6azRvxR and the select we are talking about: pastebin.com/bFbanY2n – Hornwort

@PavelHodek I am looking through this now, is "ID" a user-defined datatype? If so, can you show us the definition? – Bordure

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

Elizbeth

Thank you, but I cannot do this, because 'lor' table and cross apply have to be together - they represent one unit within complex generated query (there can be more units joined) and then finally filtered (in real the another_table is temporary table variable with some records that represents final restriction). – Hornwort

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.

Calie

Unfortunately the select is "user configured" and generated, it can join more other tables - I can not predict the plan. – Hornwort

Answer 3

YES and NO... it's hard to interprit what you're trying to achieve without sample data IN and result OUT, to compare outcomes.

I'd like to know:

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

So I'll answer your question above (3 years later!!) directly, with a direct statement:

You need to learn about CTE and the difference between CROSS APPLY compared to INNER JOIN and why using CROSS APPLY in your case is necessary. You "could" take the code in your function and apply it into a single SQL statement using CTE.

ie:

Read this and this.

Essentially, something like this...

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

Apply your query to extrapolate the date you want ONCE, and using CTE, then apply your second SQL using the CROSS APPLY.

You have no choice. You cannot do what you're trying to do in ONE SQL.

Korn