February 18, 2009 at 4:01 pm
I have started at a shop where all procedures are created with at least the same 2 parameters. Let's call them @RunThisSection and @GenericParamValue, both varchar of reasonable length.
Each proc will do different things depending on the value passed in with @RunThisSection. So any things that touches a particular table will be put into the one proc. From selects with different where criteria, updates, deletes, inserts, you name it. Even selects used called from SSRS.
Needless to say, I think this is not good design. I have argued many times that this is poor design. I believe a proc should do one thing and one thing only.
I have argued from the point of view of design, proc cache usage, proc compile time, security management. I am about to argue then why have different procs at all? Why not just put everything into one huge proc!
My problem is, when challenged to give examples where this design pattern is specifically not recommended (yeah, I know, disproving a negative is not possible) I cannot find any. It is even difficult to find recommendations on limiting each procedure to one function only.
I have not encountered this approach to proc design before in my 10+ years of experience. Thoughts?
February 18, 2009 at 4:06 pm
I thought designing subroutines and functions in any programming language encouraged writing them such that each did only one thing. That is the way I was taught even back in my computer classes in high school many years ago.
February 18, 2009 at 4:09 pm
That's the problem. It seems so fundamental, that there is no reference to be found against it. It is like saying tyres should be square, find something that argues against it.
February 18, 2009 at 4:17 pm
[font="Verdana"]Wow. Do people actually write code that way? Who knew.
Okay, for coding styles, an excellent reference is Code Complete, by Steve McConnell. Look under section 7: High-Quality Routines. The whole section is good. The particularly relevant bits are the comments on Communicational cohesion and Logical cohesion. I'll quote (just the appropriate bits):
Steve McConnell, Code Complete, Second Edition, 7.2 Design at the Routine Level, p169
For routines, cohesion refers to how closely the operations in a routine are related. Some programmers prefer the term "strength": how strongly related are the operations in a routine? A function like Cosine() is perfectly cohesive because the whole routine is dedicated to performing one function. The goal is to have each routine do one thing well and not do anything else.
The payoff is higher reliability. [Bunch of studies and statistics to back that up.]
Functional cohesion is the strongest and best kind of cohesion, occuring when a routine performs one and only one operation.
Several other kinds of cohesion are normally considered to be less than ideal:
Communicational cohesion occurs when operations in a routine make use of the same data and aren't related in any other way.
The remaining kinds of cohesion are generally inacceptable. They result in code that's poorly organised, hard to debug, and hard to modify. If a routine has bad cohesion, it's better to put effort into a rewrite to have better cohesion than investing in a pinpoint diagnosis of the problem.
Logical cohesion occurs when operations in a routine are stuffed into the same routine and one of the operations is selected by a control flag that's passed in. It's called logical cohesion because the control flow or "logic" of the routine is the only thing that ties the operations together--they're all in a big if statement or case statement together. [...]It's cleaner to have [multiple] routines, each of which does one distinct operation. If the operations use some of the same code or share data, the code should be moved into a lower-level routine and the routines should be packaged into a class.
Now obviously you can't write classes in SQL Server. However, you don't really need to as the database itself persists the data across multiple related routines. What I do with related routines is to use similar names so that they get grouped together. So I use the form:
Object_Action
That way, all of the routines that operate on a specific object get grouped together when you list them.
Anyway, Code Complete is a great place to start! It has excellent reasoning as how this stuff relates to code quality and maintenance costs.
[/font]
February 18, 2009 at 6:05 pm
Thoughts?
I wish I was a consultant who worked near your office so I could make all the great money that's going to roll in when those queries have to be rewritten and tuned.
It's a really, really bad idea. Look up information on recompiles, just to start. There's more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 1:44 pm
Tried that.
There are also the extra costs just to maintain this stuff too. Ifthere is any troubleshooting, it usually requires going backwards and forwards with the developer to get the right set of parameter values just so you know which section you are troubleshooting.
Trying to run a trace on this stuff is not easy. Capturing everything and then having to pull out only those calls with the specific parameter values is not pretty. Sure, I could filter out everything but the values I am looking for with a like filter on the Text column, but the overhead...do you hit the DB engine with the filtering or the file system as you capture all calls with a server side trace?
February 19, 2009 at 1:50 pm
[font="Verdana"]How's your CV/Resume looking?[/font]
February 19, 2009 at 2:12 pm
Updating it this weekend. Thanks for the pointer to Code Complete. It is on my shopping list.
February 20, 2009 at 5:56 am
I do feel for you. I've been there. Trying to convince a programmer who knows everything there is to know that, maybe, just maybe, he's not quite up to snuff on the database side of things. I've usually lost those fights too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2009 at 1:42 pm
Grant Fritchey (2/20/2009)
I do feel for you. I've been there. Trying to convince a programmer who knows everything there is to know that, maybe, just maybe, he's not quite up to snuff on the database side of things. I've usually lost those fights too.
[font="Verdana"]Mine was an "Enterprise Architect" who insisted that his role was to dictate how the database was put together (including requiring row-by-row triggers!)
Sometimes it's just best to walk away.
[/font]
February 22, 2009 at 1:49 pm
Bruce W Cassidy (2/22/2009)
Grant Fritchey (2/20/2009)
I do feel for you. I've been there. Trying to convince a programmer who knows everything there is to know that, maybe, just maybe, he's not quite up to snuff on the database side of things. I've usually lost those fights too.[font="Verdana"]Mine was an "Enterprise Architect" who insisted that his role was to dictate how the database was put together (including requiring row-by-row triggers!)
Sometimes it's just best to walk away.
[/font]
The hard thing is, that this is the best job I have had, except for this one person, and even then it is only this aspect of his management style. Everyone else is a true professional. The conditions are great, the technology impressive. In these times, I think the the pan may be better than testing the fire!
February 22, 2009 at 2:16 pm
1 cool thing that can happen for you :
Write all you warnings, recommendations and consequences if not followed. Send to your boss and CC a couple of his superiors (make sure you link to authoritative experts (like the previous book) so that they can take this seriously and not just discount it without any further thoughts).
Then each time they go against your recommendations, send a email, remake your claim about possible side effects and have him answer by email to authorize the code changes or creation.
Now if anything goes wrong it's not your fault at that point.
Step 2, keep looking for something else. Someday in the future you can really find something you like but make sure you inquiry about those very points during the interview. Talk to your future managers and coders if possible.
Then once the problems start to arise just as you predicted, they'll want you back to fix it. This is when you come back as a consultant and charge 3-5 times your current income.
Been there, done that. Still making a very nice living without having to work 50 hours a week 😉 even 3 years later...
The great thing about being the consultant is that you are immediatly given an authoritative status and it's your way or the high way (assuming you make sens of course).
Good luck.
February 22, 2009 at 2:17 pm
[font="Verdana"]Ah, that makes it harder.
Maybe showing them the appropriate section in Code Complete, with the data to back up the studies, will help. Otherwise, my suggestion would be thus:
Build a small test frame (a workable sub-set of your database that shows off most of the types of functionality.)
Use the existing code, and write the equivalent code the correct way.
Now run some performance tests. Exercise all of the different code paths, and try to do it with a good selection of "real world data".
I can guarantee that the performance tests will show that correctly written code will perform much better than the method they are using.
However... that takes a fair amount of time and effort to do!
Also, look at a transition strategy. If you can gut the contents of the combined stored procedures so they just call other "correct" stored procedures, and hence any code already using the combined stored procedures still works... you may find there is less resistance to the change.
Then any future development can execute the correct stored procedures directly, rather than the out-dated code.
[/font]
February 22, 2009 at 2:26 pm
Thanks guys. It is calming to know there are fellow travellers out there. Ninja, I like your advice, Bruce, you are right, time is the biggest limiting factor. One of the good things about his job, is I can make time to do that sort of thing. It will still take a few weeks to set it up in between my usual tasks, but it can be done.
February 22, 2009 at 2:38 pm
[font="Verdana"]I ran out of time in my case. I focussed on showing the cumulitive data errors as a result of business code in triggers failing, rather than the performance issues. The enterprise architect seemed to be of the view that the performance issues were inconsequential ("oh, we can just run several copies"), so for me it was more important to show that the screwed up logic was producing actual faults in the data. So I never got around to showing the performance differences.[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply