Overused Procedure? Is that possible?

Question

Overused Procedure? Is that possible?

mtillman-921105

SSCertifiable

Points: 7156
More actions
March 3, 2011 at 4:11 pm

#148599

OK, here's one for our developer gurus! 😎
I like the idea of using one procedure for lots of tasks rather than copying code around (and possibly having to go edit all of it later if there's a change). But I've got a few procedures, and one in particular, that may be overly used.
This procedure is almost 1,000 lines long and has 17 parameters. Sometimes only half the parameters are used. It has large blocks contained in IFs too.
Anyway, this is sort-of common sense, but I just wanted to know if anyone has rules when they draw the line and say to themselves "OK, that's it, this poor procedure's getting too patched up and hard to work with! Enough!" What signs do you look for or guides do you use?
I can think of a few off the top of my head:
- It's too complicated for mere humans to work with.
- It has 50 parameters
- It has 2,000 lines of code
- There are 20 big IF blocks in the thing
All of these fall under the category, by the way, of "may be necessary, but probably not". So there's no hard and fast rules here.
Edit: In this case, the procedures are for a reporting warehouse which is not often busy, so performance is not at the top of my list of concerns in this context. (But if one of my procedures takes longer than say 2 or 3 seconds, I do try to improve the performance.)
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Would be thinking of running that with various of the 50 or so parameters supplied and looking at the execution plan ......

and making decision to simplify based on the actual plan

Edited to

corrected spelling errors

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

Thank you for your answers, especially from two of you who are exceptionally knowledgeable, but I should have mentioned that this is on a warehouse which is not very often busy. So performance is not paramount in my case, although I do try to write high performance code.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking