CROSS APPLY with table valued function restriction performance
Solution 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
Solution 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.
![Pavel Hodek](https://i.stack.imgur.com/0ELCK.jpg?s=256&g=1)
Pavel Hodek
Hi! I work as a web developer. My area of interest are ASP.NET, C#, CSS, JavaScript, jQuery and Ajax.
Updated on September 08, 2020Comments
-
Pavel Hodek almost 4 years
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
andANOTHER_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 joinANOTHER_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.
- Inner select on table