Posted by Bob Barrows [MVP] on 01/11/07 12:12

wizofaus@hotmail.com wrote:
> Uri Dimant wrote:
>>> In the end I ended up de-parameterizing the query just for this
>>> case,

"de-parameterizing"? You mean changing to dynamic sql and leaving yourself
vulnerable to sql injection??

>>> 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
>>
> Sure, except that the content of the database is out of my control -
> this particular scenario (where nearly all the records matched a
> particular key, but the query was first run against a different key)
> could easily arrise in a production environment. More to the point,
> I've seen no evidence that I'm getting any performance benefits from
> using parameterized queries.
> I suspect I will at least add a configuration option to avoid
> parameterized queries (relatively straightforward, as I have a layer
> of
> code that handles query parameters) if I see a problem like this
> again.

This is a ridiculous overreaction. Problems due to parameter-sniffing are
too rare to justify eliminating the benefits of using parameters. Talk about
"throwing the baby out with te bath water".

The article showed two, no three, ways to alleviate the problems caused by
parameter sniffing and still use parameters. So what do you do? ignore the
article's advice and "de-parameterize" your query...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Back to original message]