Posted by Uri Dimant on 01/11/07 10:40

> In the end I ended up de-parameterizing the query just for this case,
> but now I'm worried - how can I be sure that my other queries won't
> suffer from the same problem? Should I never use parameters because of
> this possibility?
>
An ability using parameters is very powerful , don't afraid using parameters
, just test it carefuly




<wizofaus@hotmail.com> wrote in message
news:1168509843.760497.149900@i56g2000hsf.googlegroups.com...
> Uri Dimant wrote:
>> Hi
>> http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
>>
> Thanks for that...amazingly enough it turned that that was exactly my
> problem, although I'm using ad-hoc queries rather than stored procs. I
> did some more testing, and it turned out that it was because it was
> executing the same query twice, the first time with an atypical
> parameter value, and the second time with a more typical one, that the
> query was running so slowly. That is, executing
>
> SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 999
> followed by
> SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 123
>
> Caused the second query to run absurdly slowly, because in the first
> case only very few rows in the table had MyKey = 999 whereas almost
> every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
> swapping the queries around, they both ran fine.
>
> In the end I ended up de-parameterizing the query just for this case,
> but now I'm worried - how can I be sure that my other queries won't
> suffer from the same problem? Should I never use parameters because of
> this possibility?
>

[Back to original message]