Parameter triggers different execution plan, but why?

Question

I have a table

CREATE TABLE [dbo].[Numbers]
    (
        [Date] [date] NULL,
        [Time] [time](3) NULL,
        [Value] [char](10) NULL
    )

and the table has > 10 Billion rows, therefore it is partitioned by month and has a clustered index on [Date], [Time] ASC

Now I use a table valued function that reads this data:

SELECT * 
FROM [dbo].[QueryNumbers] ('2012-10-08','2012-10-08','07:00:00.000','08:00:00.000')

This returns me around 6000 rows in 1 second

However when I do the same like this:

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT * 
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime)

The same query takes 3 minutes (which is a desaster), I played a bit with the parameters and it seems that the time parameter trigger the different behaviour. Anybody has a hint for me what is going wrong here ?

Answer 1

Your first query has constants for the parameters, so when sql compiles the plan it knows exactly what the parameters are going to be, and can use that knowledge to pick an optimal plan.

In the second query, the values are parameterized, therefore sql will create a plan that will work for any potential values for the parameters.

You can test this by running the parameterized query with the OPTION (RECOMPILE) clause. This will allow sql to create an optimal plan for those specific values.

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT * 
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime) OPTION (RECOMPILE)