与表值函数限制性能交叉应用 [英] CROSS APPLY with table valued function restriction performance

问题描述

我对带有参数化表值函数的CROSS APPLY有问题. 这是简化的伪代码示例:

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

• 表LOT_OF_ROWS_TABLE上的内部选择返回许多行.
• 联接表LOT_OF_ROWS_TABLE和ANOTHER_TABLE仅返回一或几行.
• 表值函数非常耗时，并且在调用很多 行选择会持续很长时间.
• 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.

我的问题:

对于从LOT_OF_ROWS_TABLE返回的所有行都调用该函数，而不考虑仅在加入ANOTHER_TABLE时数据将受到限制的事实.

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

我想知道:

是否存在任何设置或提示或某些强制选择仅针对最终受限制的行调用函数的内容?

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

谢谢.

表值函数非常复杂: http://pastebin.com/w6azRvxR . 我们正在讨论的选择是用户配置"并生成的: http://pastebin.com/bFbanY2n .

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.

推荐答案

您可以使用表变量或临时表将该查询分为两部分

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

这篇关于与表值函数限制性能交叉应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！