Posted by wizofaus on 01/11/07 16:04

Bob Barrows [MVP] wrote:
> 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??

No, because the parameter values are fully under my control - they are
not submitted directly by the user. At any rate, in this case it's
always just a simple integer.

>
> >>> 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".

Well, yes, but as I said, the testing I've done has revealed that my
app definitely is suffering badly from parameter-sniffing problems
(I've come across yet another one since), and that the only reliable
way I've found to solve it is to NOT use parameters, which doesn't seem
to be adversely affecting performance.
>
> 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...

Yes, but they use stored procs. I'm trying to avoid stored procs in
order to keep RDBMS independence (although for the time being, we've no
immediate need to support other databases).

[Back to original message]