Re: Help me convince the dev manager. Please.

From: Nunya (nunya_at_anon.com)
Date: 05/14/04 

Date: Fri, 14 May 2004 08:48:51 -0500

Probably agree w/ the comment on "correctly structured" databases.

Have to disagree about the comments on not having much choice other than
dynamic SQL. The query cost w/ dynamic SQL won't necessarily be lower. And
there's another more optimized solution that I haven't seen mentioned here
yet:

Use your called stored procedure as a main switchboard with a series of IFs
based on the combination of parameters used. Then create and call separate
stored procs for each separate combination of parameters (or combine minor
variations that don't impact the query plan significantly). That way, each
parameter scenario can use a highly optimized static query, and the query
plan will be specific to the actual query run each time called rather than
the first query executed as it would be in a single stored proc w/ a series
of static SQL in IF statements.

Some might complain about the maintaining multiple stored procs, but I find
editing concatenated strings to build SQL more of a maintenance issue than
multiple procedures. It's really just a form of the modularization that we
take for granted in other programming languages. I hope Yukon brings us
packages like Oracle -- makes it more intuitive and attractive to modularize
code by letting us bundle related procedures and functions together.

"David Webb" <spivey@post.com> wrote in message
news:uIeTGPGOEHA.556@TK2MSFTNGP10.phx.gbl...
> I disagree. "correctly structured" databases give you nothing for this
> problem. If I have a table with customers and I have to provide a free
text
> search on any or all of 8 different fields (name, address, city, etc),
there
> is no database design or structure that would help with this problem. My
> choices are dynamic SQL or the (@param = col or @param is null) struct for
a
> search/list sp. If there are one or two search fields, I'll go with a
> static query, but with 4 or more, there is not much choice other than
> dynamic SQL. The compile cost will be higher, but the query cost will
> certainly be lower.
>
>
>
> "songie D" <songie@D.com> wrote in message
> news:uhMW8WFOEHA.1340@TK2MSFTNGP12.phx.gbl...
> > Dynamic SQL's performance is a whole lot worse than stored procedure
> > SQL - not just in the fact that it's not precompiled, but also
> > due to the fact that it has a tendency to have DBAs complain of the
> > generation of huge log files which dynamic SQL apparently causes
> > SQL server to do.
> > It should be treated like cursors - useful at times, but used as
sparingly
> > as possible. But like others have said, there's very few database
> scenarios
> > which actually NEED it, if the database is correctly structured. The
> 'need'
> > for it is more likely to arise because a database project has been
driven
> > down the road of a bad design plan that can't be reversed down and needs
> > new features adding. It sounds like this is the unfortunate scenario in
> your
> > case. If it is, perhaps you should do as suggested and post what the dev
> > manager's solution is, and what yours is, and let the group compare
them?
>
>

Relevant Pages