T-SQL code is extremely slow if stored as an Inline Table-value function

Question

I cannot understand why SQL Server adopts a completely different execution plan when packing my code in ITVF. When executing the code inside ITVF, the request itself is executed after 5 seconds. If I save it as ITVF, it will work for 20 minutes and will not produce a result. I would prefer it to be in ITVF for code reuse. Any ideas why saving code as ITVF could lead to serious performance issues?

CREATE FUNCTION myfunction ( @start_date date, @stop_date date ) RETURNS TABLE AS RETURN ( with ad as ( select [START_DATE] ,[STOP_DATE] ,ID ,NAME ,'domain1\' + lower(DOMAIN1_NAME) collate database_default as ad_name from EMP_INFO where DOMAIN1_NAME != '' union select [START_DATE] ,[STOP_DATE] ,ID ,NAME ,'domain2\' + lower(DOMAIN2_NAME) collate database_default as ad_name from EMP_INFO where DOMAIN2_NAME != '' ) select ad.ID ,ad.NAME ,COUNT(*) as MONITORS from scores join users on (scores.evaluator_id = users.[user_id]) join ad on (lower(users.auth_login) = ad.ad_name and scores.[start_date] between ad.[START_DATE] and ad.[STOP_DATE]) where scores.[start_date] between @start_date and @stop_date group by ad.ID ,ad.NAME ) 

EDIT:

Well ... I think I understood the problem ... but I do not understand this. Perhaps I should post a completely new question, let me know what you think. The problem here is when I call a function with literals, it is REALLY slow ... when I call it variables, it is fast.

 -- Executes in about 3 seconds declare @start_date date = '2012-03-01'; declare @stop_date date = '2012-03-31'; select * from myfunction(@start_date, @stop_date); --Takes forever! Never completes execution... select * from myfunction('2012-03-01', '2012-03-31') 

Any ideas?

Answer 1

When you use literals, SQL Server can look at column statistics to estimate how many rows will be returned and select the appropriate plan based on this assumption. When you use variables, the values ​​are not known at compile time, so it goes back to assumptions.

If a plan is better when it’s guessed than when it refers to actual statistics, it indicates that the statistics are probably in need of updating.

If you have automatic updating of statistics, you may encounter a problem here. Statistics, row estimates and a column with an increasing date