CROSS APPLY with table dependent performance limitation

Question

I have a problem with CROSS APPLY with a parameterized table function. Here is a 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 ... 

• The internal selection in the LOT_OF_ROWS_TABLE table returns many rows.
• The LOT_OF_ROWS_TABLE and ANOTHER_TABLE returns only one or more rows.
• A table-dependent function takes a lot of time and when calling a large number of rows, the selection lasts a 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 with a simple ANOTHER_TABLE .

The choice should be in the format shown - it is generated and in fact it is much more dense.

When I try to rewrite it, it can be very fast, but it cannot be rewritten as follows:

 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 parameter or tooltip or something that makes the call function only for permanently restricted lines?

Thanks.

EDIT:

The tabular rating function is very complex: http://pastebin.com/w6azRvxR . The choice we are talking about is "user configured" and generated: http://pastebin.com/bFbanY2n .

Answer 1

you can split this query into 2 parts using either a table variable or a 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 part of the time, which is a table-valued function on the right

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

Answer 2

I believe that this is what you are looking for.

Plan creation scenario: create a plan guide to enforce a plan obtained from a rewritten request

This basically describes re-recording the request to get the generated plan using the correct join order. Then save this plan and force your existing request (which will not change) to use the plan that you saved.

The BOL link that I am inserting even gives a concrete example of re-writing a query by placing the connections in a different order and using the FORCE ORDER hint. Then, using sp_create_plan_guild to take the plan from the rewritten request and use it in the original request.

Answer 3

YES and NO ... it’s hard to intervene in what you are trying to achieve without sample IN data and OUT result to compare the results.

I'd like to know:

Are there any settings or hints or something that forces the function call to be selected only for permanently limited lines?

So, I will answer your question above (after 3 years !!) directly, with a direct expression:

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

t

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 

Use your query to extrapolate the date you want ONCE and use CTE and then apply the second SQL with CROSS APPLY.

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