Need Help, fast

I have a SP which is having an odd problem. There is an int which I am
using to to return several different queries in the SP. The first
query gets the value of the int based on a date, @.minsess. @.minsess is
then used in multiple queries in the SP. For some reason if I replace
@.minsess with a value say 656 then query plan changes and the entire sp
executes in seconds, with @.minsess each query takes much longer
resulting in the SP taking more than a minute to execute.
A dumbed down version of this would be like this.
Select @.minsess = min(sessionid) from table1 where FileDate >=
CONVERT(varchar(10), getdate() - 3, 112)
Select * FRom table2 where sessionid >= @.minsess
Select * FRom table2 where sessionid >= 656
In this example @.minsess would equal 656. Both are INTs, why in the
world would the first table2 hit take multiple times longer to return
than the second?For a parameter to a stored procedure, the optimizer picks up the value for
when the execution plan
is created ("first execution") and put into cache. This is the value the opt
imize base its execution
plan on.
For a constant, then the optimize know the value.
For a variable, the optimizer doesn't know the value and have to guess the s
electivity (number of
rows that matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Moore" <ptmoore@.gmail.com> wrote in message
news:1108047830.104598.326620@.o13g2000cwo.googlegroups.com...
>I have a SP which is having an odd problem. There is an int which I am
> using to to return several different queries in the SP. The first
> query gets the value of the int based on a date, @.minsess. @.minsess is
> then used in multiple queries in the SP. For some reason if I replace
> @.minsess with a value say 656 then query plan changes and the entire sp
> executes in seconds, with @.minsess each query takes much longer
> resulting in the SP taking more than a minute to execute.
> A dumbed down version of this would be like this.
> Select @.minsess = min(sessionid) from table1 where FileDate >=
> CONVERT(varchar(10), getdate() - 3, 112)
> Select * FRom table2 where sessionid >= @.minsess
>
> Select * FRom table2 where sessionid >= 656
> In this example @.minsess would equal 656. Both are INTs, why in the
> world would the first table2 hit take multiple times longer to return
> than the second?
>|||You get this problem because you use a variable in a WHERE clause. Because
the Query Optimizer doesn't know at compile time what the value of the
variable will be, it decides on a query plan based on an estimate of the
number of rows that will be returned. For a >= comparison it assumes 30% of
the rows will be returned IIRC.
If you change your code to:
Select * FRom table2 where sessionid >= (Select min(sessionid) from table1
where FileDate >=
CONVERT(varchar(10), getdate() - 3, 112))
the Query Optimizer will come up with a faster execution plan.
Jacco Schalkwijk
SQL Server MVP
"Paul Moore" <ptmoore@.gmail.com> wrote in message
news:1108047830.104598.326620@.o13g2000cwo.googlegroups.com...
>I have a SP which is having an odd problem. There is an int which I am
> using to to return several different queries in the SP. The first
> query gets the value of the int based on a date, @.minsess. @.minsess is
> then used in multiple queries in the SP. For some reason if I replace
> @.minsess with a value say 656 then query plan changes and the entire sp
> executes in seconds, with @.minsess each query takes much longer
> resulting in the SP taking more than a minute to execute.
> A dumbed down version of this would be like this.
> Select @.minsess = min(sessionid) from table1 where FileDate >=
> CONVERT(varchar(10), getdate() - 3, 112)
> Select * FRom table2 where sessionid >= @.minsess
>
> Select * FRom table2 where sessionid >= 656
> In this example @.minsess would equal 656. Both are INTs, why in the
> world would the first table2 hit take multiple times longer to return
> than the second?
>|||Thanks