Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Those are variables not parameters. What is the definition of the function and the execution plan? –  Martin Smith Feb 12 '13 at 17:29
add comment

1 Answer

up vote 3 down vote accepted

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)
share|improve this answer
    
Wow, I thought it would be very clear for the compiler what values he will get since the datatype is specified and the assignment of the values is done just before the table valued function is called. With recompile I reach the 1 second as well. –  nojetlag Feb 12 '13 at 18:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.