向内联表值函数传递日期参数速度很慢

Question

我遇到了一个关于表值函数性能的奇怪情况。基本上，我有一个内联表值函数，它以DATETIME为参数。

它大致看起来像这样（不完全相同）：

  CREATE FUNCTION fn_MyFunction(@StartDate DATETIME)
  RETURNS TABLE
  AS
  RETURN (
    SELECT COUNT(*), CustomerID, SUM(PAID)
    FROM Orders   
    WHERE OrderDate > @StartDate
    GROUP BY CustomerID
  )

现在，我正在调查一个问题，即这个查询运行时间超过了1分钟。事实证明，如果我以这种方式调用查询：

SELECT * FROM fn_MyFunction('7/1/2011')

运行时间超过1分钟

然而，如果我以这种方式调用查询：

DECLARE @startDate DATETIME = '7/1/2011'
SELECT * FROM fn_MyFunction(@startDate)

它可以在一秒钟内运行完。SQL Server针对这两种调用使用了完全不同的执行计划。

显然，我希望它能一直采用第二种方法，但不幸的是，我通过LINQ 2 SQL调用了这个表值函数，而它不会声明中间变量。

有没有办法在行内表值函数中使用中间变量？我真的不想将其转换为多行表值函数。欢迎提出其他想法。我有点困惑。

- Linus

---

在你的测试中，你是否尝试运行SELECT * FROM fn_MyFunction('7/1/2011')超过一次？ - John Dewey

@John。是的。多次尝试过了。不是缓存问题。 - Linus

如果自动参数化出现问题，您可以尝试使用SELECT * FROM fn_MyFunction('7/1/2011') OPTION (RECOMPILE)。如果这不起作用，可能需要更新Orders的统计信息。 - Martin Smith

你能否在这两个调用上发布执行计划的副本？ - Brent Ozar

Answer 1

我用大量记录尝试了这个，两种方式都在9秒内返回值，没有区别...

这只是一个猜想，但可以测试一下隐式转换是否给函数提供了与显式转换相同的日期值？尝试使用像“2011/1/30”这样的日期，这样就会出现月/日转换问题。

- Cenas

Answer 2

添加OPTION (RECOMPILE)将解决您的问题。我遇到了与内联TVF相同的问题，如下所示：

这个语句执行时间不到一秒：

select PropertyID from msa_GetPropertlyListWithNoMessages_TVF(DATEADD(hh, -2, Getdate())) 

这条语句执行了5个小时后仍未完成：

declare @msg_age as Datetime
SET @msg_age = DATEADD(hh, -2, Getdate())
select PropertyID from msa_GetPropertlyListWithNoMessages_TVF(@msg_age)

在第二次调用中添加OPTION (RECOMPILE)可以纠正问题。

据我所知，使用datetime参数会以某种方式产生截然不同的执行计划。我很想找出原因。

- slagou