Re: stored proc bug with datetime variable

Google the phrase "parameter sniffing"...

Gert-Jan


ujjc001 wrote:

two variables declared in my proc:
@DATE_RANGE_START as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_START1 as datetime,
@DATE_RANGE_END1 as datetime,

and add this to my sp:
declare @DATE_RANGE_START datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_START = @DATE_RANGE_START1
set @DATE_RANGE_END = @DATE_RANGE_END1
the SP runs in 9 seconds (which is expected)

Passing in '1/1/01' and '1/1/07' respectivly.

Everything else is equal and non-important to this problem.
Why does it take 34 seconds when I use the variables from the input
parameters?
Interesting isn't it.
Jeff
.

Relevant Pages

  • stored proc and optional parameters
    ... i have a proc in development that accepts up to 6 paramaters. ... when i call the proc thru an 'execute procedure' statement or walk ... all is well and one datetime and a string is ... end foreach; ...
    (comp.databases.informix)
  • Re: Spalten alternativ ansprechen
    ... DECLARE @Monat INT ... '01' AS DATETIME) ... case when DATUM between '...' ... then UMSATZ as ...
    (microsoft.public.de.access.clientserver)
  • Re: a view based on stored procedure
    ... DECLARE @Week_1 DATETIME ... DECLARE @CubeTime DATETIME ... INSERT INTO WrkAvailPlanHours ...
    (microsoft.public.sqlserver.programming)
  • Re: Language problem
    ... @datum datetime ... > Then in application I have settings of my connection: ... > everything else doesn't because all other functions are set to slovenian. ... > DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK ...
    (microsoft.public.sqlserver.programming)
  • RE: User Defined Function Help
    ... @startDateTime datetime = null, ... DECLARE @selectFromList varchar ... SET @subservice = RTRIM ... EXEC ...
    (microsoft.public.sqlserver.programming)