T-SQL code is extremely slow when saved as an Inline Table-valued Function
database-performance, sql-server, t-sql, user-defined-functions
Solution
When you use literals SQL Server can look at the column statistics to estimate how many rows will be returned and choose an appropriate plan based on that assumption. When you use variables the values are not known at compile time so it falls back on guesses.
If the plan is better when it guesses than when it refers to the actual statistics then this indicates the statistics likely need updating.
If you have auto update of statistics turned on then you may well be hitting the issue here Statistics, row estimations and the ascending date column
Problem
I can't seem to figure out why SQL Server is taking a completely different execution plan when wrapping my code in an ITVF. When running the code inside of the ITVF on its own, the query runs in 5 seconds. If I save it as an ITVF, it will run for 20 minutes and not yield a result. I'd prefer to have this in an ITVF for code reuse. Any ideas why saving code as an ITVF would cause severe 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: Ok...I think I figured out the problem...but I don't understand it. Possibly I should post an entirely new question, let me know what you think. The issue here is when I call the function with literals, it is REALLY slow...when I call it with 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?