使用表值函数限制的CROSS APPLY性能问题

Question

我在使用带有参数表值函数的时遇到了问题。 以下是一个简化的伪代码示例：

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表仅返回一行或几行。
• 表值函数非常耗时，当调用大量行时，选择时间持续很长时间。

我的问题：

无论只连接ANOTHER_TABLE数据会受限，函数都会对从LOT_OF_ROWS_TABLE中返回的所有行进行调用。

这个查询必须保持原来的格式 - 它是生成的，而且实际上比这更加困难。

当我尝试重写它时，它可以非常快速，但不能像这样重写：

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

我想知道：

是否有任何设置、提示或其他东西可以强制select仅为最终受限行调用函数？

谢谢。

编辑：

该表值函数非常复杂：http://pastebin.com/w6azRvxR。 我们所讨论的选择是“用户配置”的并且生成的：http://pastebin.com/bFbanY2n。

- Pavel Hodek

---

或许显而易见：如果您能够更改查询文本中连接的顺序，那么您就可以使用FORCE_ORDER查询提示。代码是如何生成的？在那方面有没有办法改变行为？ - David Abrahamsson

将您的 dbo.HeavyTableValuedFunction(..) 多行表值函数更改为内联表值函数。 - RBarryYoung

@RBarryYoung：太复杂了，无法简单地重写为内联函数。 - Pavel Hodek

@PavelHodek 啊，好吧，那就是导致你问题的原因，如果你不向我们展示它，我们能做的帮助很少。 - RBarryYoung

@RBarryYoung：表值函数：http://pastebin.com/w6azRvxR 和我们谈论的选择：http://pastebin.com/bFbanY2n。 - Pavel Hodek

@PavelHodek 我现在正在查看这个，"ID" 是一个用户定义的数据类型吗？如果是，请展示一下定义。 - RBarryYoung

Answer 1

您可以将此查询分为两部分，使用表变量或临时表来实现。

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

现在进行耗时的部分，即表值函数正确性的验证。

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

- Sankara

---

谢谢，但我不能这样做，因为'lor'表和cross apply必须在一起 - 它们代表复杂生成查询中的一个单元（可以连接更多单元），然后最终过滤（实际上，another_table是带有某些记录的临时表变量，表示最终限制）。 - Pavel Hodek

Answer 2

我相信这就是你在寻找的内容。

强制执行方案：创建一个计划指南以强制执行从重写查询中获得的计划

基本上，它描述了重新编写查询以使用正确的连接顺序来生成计划。然后保存该计划并强制现有查询（不进行更改）使用保存的计划。

我放入的BOL链接甚至提供了一个具体的示例，重新编写查询以按不同顺序放置连接，并使用FORCE ORDER提示。然后使用sp_create_plan_guild从重新编写的查询中获取计划并在原始查询中使用它。

- Kenneth Fisher

---

不幸的是，该选择是“用户配置”的并且是动态生成的，它可以连接更多的表 - 我无法预测执行计划。 - Pavel Hodek

Answer 3

是和否...如果没有输入数据和输出结果进行比较，很难理解你想要实现什么。

我想知道：

是否有任何设置或提示或其他东西可以强制选择仅为最终受限行调用函数？

所以我将直接回答你上面的问题（3年后！！）：

您需要了解CTE以及CROSS APPLY与INNER JOIN之间的区别，以及在您的情况下使用CROSS APPLY的必要性。您“可以”将函数中的代码应用于使用CTE的单个SQL语句中。

例如：

阅读this和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

应用查询一次来推断您想要的日期，并使用CTE，然后再应用第二个SQL使用CROSS APPLY。

你别无选择。你不能在一个SQL中做你试图做的事情。

- Fandango68