Mike,

Look at the execution plan. The second and third probably is executing a index scan while the first is going through an index seek.

The problem is that the optimizer can't optimize the query because it does not know what exactly is in the @BegDateChar and @EndDateChar when it compiles the execution plan for this query.

If you're doing a stored procedure, make sure you pass those variables directly to the stored procedure without modifying them in there. When doing SPT, make sure you calculated to the parameters in the front-end and pass them as parameters along with the SPT.

If all is well, both the stored procedure and the SPT will do parameter sniffing and determine the right and same execution plan as it was programmed like example 1.

Walter,


>I’ trying to get the sum of AmtDue between two dates. If I use a constant text date-range the Execution Time is 0. If use a variable, which I need to use; it takes 18 seconds. What is causing this?
>
>

>declare
>@BegDate		datetime,
>@EndDate		datetime,
>@BegDateChar		char(19),
>@EndDateChar		char(19)
>
>set @BegDate	= convert(datetime,'03/04/2008 00:00:00')
>set @EndDate	= convert(datetime,'03/05/2008 23:59:59')
>
>set @BegDateChar= convert(char,@BegDate)
>set @EndDateChar= convert(char,@EndDate)
>
>select
>@BegDate	as BegDate,
>@EndDate	as EndDate,
>@BegDateChar	as BegDateChar,
>@EndDateChar	as EndDateChar
>
>--Query-1 (Execution Time: 00:00:00)
>select sum(AmtDue) as AmtDue
>	from invfl with (nolock) 	
>	where invdate between '03/04/2008 00:00:00' and '03/05/2008 23:59:59'
>
>--Query-2 (Execution Time: 00:00:18)
>select sum(AmtDue) as AmtDue
>	from invfl with (nolock) 	
>	where InvDate between @BegDateChar	and @EndDateChar
>
>--Query-3 (Execution Time: 00:00:18)
>select sum(AmtDue) as AmtDue
>	from invfl with (nolock) 	
>	where InvDate between @BegDate		and @EndDate
>

>
>Can you please tell me what the correct syntax is?