Re: Help me convince the dev manager. Please.
From: songie D (songie_at_D.com)
Date: 05/12/04
- Next message: songie D: "Re: SQL Function parameters"
- Previous message: AlexS: "Re: query against vldb"
- In reply to: Rohan Hattangdi: "Help me convince the dev manager. Please."
- Next in thread: David Webb: "Re: Help me convince the dev manager. Please."
- Reply: David Webb: "Re: Help me convince the dev manager. Please."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 May 2004 20:17:19 +0100
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?
"Rohan Hattangdi" <RohanHattangdi2003@hotmail.com> wrote in message
news:OIouKJFOEHA.2480@tk2msftngp13.phx.gbl...
> All,
>
> I cannot believe that I am posting about this again.
>
> I just came back from a meeting with the dev managers here and one of the
> dev managers insists that there must be another method out there to handle
> optional parameters. I have gone over the article by Erland Sommarskog
and,
> both in conjunction with the article and via independent research, and
> through personal experience, firmly believe that dynamic SQL is the best
way
> to handle optional parameters.
>
> There are only three options - dynamic SQL, static SQL and a hybrid
> solution. I keep mentioning that dynamic SQL is the industry-standard and
> the preferred method for handling optional parameters but the response is
> always that there must be a better way to handle this because dynamic SQL
> makes code difficult to read and to debug. I came back with the fact the
> dynamic SQL code is not that hard to read if structured correctly (code
> style and standards) and is easy to debug if all parameters are passed in
to
> ensure that the SQL string is formed correctly. Unit testing must
inherently
> include the testing of all the parameters anyway. On top of that,
correctly
> structured SQL will ensure that common mistakes in string concatenation
will
> be avoided. Also, the QA process should take care of ensuring that if not
> all possible combinations, at least all logical combinations will be
tested
> before the sproc makes it out to the real world.
>
> I myself am happy with the article by Erland Sommarskog - he is a MVP and
> his article includes suggestions by other MVPs and perhaps most important
of
> all, the article makes sense.
>
> Is there anywhere else I can go to prove that dynamic SQL is the best?
> Something from Microsoft? Something with a nice big official stamp that
> says "Best Practice" or something similar?
>
> I am out of options. I cannot think of any other way to convince this dev
> manager. I cannot stand by and watch this poor practice continue. I am
> re-writing a sproc to show the manager how clearly structured dynamic SQL
> can be both easy to read and to debug.
>
> Please, if anyone out there has something on this, I would really
appreciate
> it if you would help.
>
> TIA,
>
> Rohan
>
>
- Next message: songie D: "Re: SQL Function parameters"
- Previous message: AlexS: "Re: query against vldb"
- In reply to: Rohan Hattangdi: "Help me convince the dev manager. Please."
- Next in thread: David Webb: "Re: Help me convince the dev manager. Please."
- Reply: David Webb: "Re: Help me convince the dev manager. Please."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
