SQL Server 2005 Table-valued Function weird performance -

i have huge difference of time execution between 1-minute query , same 1 in table-valued function.

but weired thing running udf (valid) company_id argument gives me result in ~40 seconds , change company_id 12 (valid again), never stops. the execution plans of these 2 queries absolutely not same , of course, long 1 complicated. execution plan between batch version , udf version same , batch version fast...!

if following query "by hand", execution time 1min36s 306 rows:

 select   dbo.date_only(call.date) date,   count(distinct customer_id) new_customers    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)    company_id = 12   , year(call.date) >= 2009   , year(call.date) <= 2009  group   dbo.date_only(call.date) 

i stored same query in function , ran :

select * company_new_customers_count(12, 2009, 2009) 

13 minutes running... , sure never give me result.

yesterday, had exact same infinite-loop-like behaviour during more 4h (so stopped it).

here definition of function:

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  return  (  select   dbo.date_only(call.date) date,   count(distinct customer_id) new_customers    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)    company_id = @company_id   , year(call.date) >= @start_year   , year(call.date) <= @end_year  group   dbo.date_only(call.date) ) go 

i happy understand going on.

thanks

additional:

definition of company_new_customers:

set ansi_nulls on go set quoted_identifier on go -- ============================================= -- description: create list of new customers of @company_id --          in given period. -- ============================================= create function company_new_customers  (        @company_id int,      @start_year int,     @end_year   int ) returns table  return  (     select         customer_id,         date         (   -- select apparition dates of cutomers before @end_year         select             min(date)       date,             customer_id                     call         join             call_customer on call_customer.call_id = call.call_id                     company_id = @company_id             , year(date) <= @end_year         group             customer_id     ) new_customers             year(date) >= @start_year -- select apparition dates of cutomers after @start_year ) go 

definition of date_only:

set ansi_nulls on go set quoted_identifier on go -- ============================================= -- author:      julio guerra -- create date: 14/10/2010 -- description: return date part of datetime value -- example:         date_only('2010-10-25 13:00:12') returns 2010-10-25 -- ============================================= create function date_only (     @datetime datetime ) returns datetime begin     return dateadd(dd, 0, datediff(dd, 0, @datetime)) end go 

execution plan of select * company_new_customers_count(8, 2009, 2009)

execution plan of select * company_new_customers_count(12, 2009, 2009)

from these query plans looks benefit index (if inferred db schema right):

create index ix_call_company_date on call (company_id, date) 

in general seems standard query optimization problem , table-valued functions aren't making difference here actually.