SQL Server 2005表值函数怪异性能

我有一个 巨大的 表值函数中1分钟查询与同一查询的执行时间差。

但最奇怪的是,用另一个(有效的)公司id参数运行UDF 在大约40秒内给我一个结果,只要我将公司id更改12(再次生效),它就不会停止。 这两个查询的执行计划完全不同 当然,长的是最复杂的。但是批量版本和自定义项版本的执行计划是一样的,批量版本很快。。。!

如果我“手动”执行以下查询,则执行时间为1分钟36秒,共306行:

 SELECT
  dbo.date_only(Call.date) AS date,
  count(DISTINCT customer_id) AS new_customers
 FROM
  Call
 LEFT OUTER JOIN
  dbo.company_new_customers(12, 2009, 2009) new_customers
   ON dbo.date_only(new_customers.date) = dbo.date_only(Call.date)
 WHERE
  company_id = 12
  AND year(Call.date) >= 2009
  AND year(Call.date) <= 2009
 GROUP BY
  dbo.date_only(Call.date)

我将这个完全相同的查询存储在一个函数中,并按如下方式运行:

SELECT * FROM company_new_customers_count(12, 2009, 2009)

已经13分钟了。。。我相信这不会给我任何结果。

昨天,在超过4小时的时间里,我有着完全相同的无限循环行为(所以我停止了它)。

下面是函数的定义:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION company_new_customers_count 
( 
 @company_id int, 
 @start_year int,
 @end_year int
)
RETURNS TABLE 
AS
RETURN 
(
 SELECT
  dbo.date_only(Call.date) AS date,
  count(DISTINCT customer_id) AS new_customers
 FROM
  Call
 LEFT OUTER JOIN
  dbo.company_new_customers(@company_id, @start_year, @end_year) new_customers
   ON dbo.date_only(new_customers.date) = dbo.date_only(Call.date)
 WHERE
  company_id = @company_id
  AND year(Call.date) >= @start_year
  AND year(Call.date) <= @end_year
 GROUP BY
  dbo.date_only(Call.date)
)
GO

我很乐意了解发生了什么事。

谢谢

附加:

新客户的定义:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Create the list of new customers of @company_id
--          in the given period.
-- =============================================
CREATE FUNCTION company_new_customers 
(   
    @company_id int, 
    @start_year int,
    @end_year   int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        customer_id,
        date
    FROM
    (   -- select apparition dates of cutomers before @end_year
        SELECT
            min(date)       AS date,
            customer_id
        FROM
            Call
        JOIN
            Call_Customer ON Call_Customer.call_id = Call.call_id
        WHERE
            company_id = @company_id
            AND year(date) <= @end_year
        GROUP BY
            customer_id
    ) new_customers
    WHERE
        year(date) >= @start_year -- select apparition dates of cutomers after @start_year
)
GO

仅日期定义:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Julio Guerra
-- Create date: 14/10/2010
-- Description: Return only the date part of a datetime value
-- Example:         date_only('2010-10-25 13:00:12') returns 2010-10-25
-- =============================================
CREATE FUNCTION date_only
(
    @datetime datetime
)
RETURNS datetime
AS
BEGIN
    RETURN dateadd(dd, 0, datediff(dd, 0, @datetime))
END
GO

从公司新客户数中选择*的执行计划(8,2009,2009)

从公司新客户数中选择*的执行计划(12,2009,2009)