February 27, 2009 at 1:17 pm
It's definitely the clutter that bothers me. It can cause additional time to be wasted when maintaining or updating an application.
:cool:It's also just plain dumb to leave them laying around like that.
February 27, 2009 at 4:03 pm
Tom Garth (2/27/2009)
:cool:It's also just plain dumb to leave them laying around like that.
It's not necessarily question of smart of dumb. It's easy to keep control of your app, but what about something you inherited? Sometimes it's not easy to determine what's needed and what isn't.
February 27, 2009 at 4:23 pm
That can be hard especially when multiple types of apps consume the data in a database.
One example would be a mortgage industry system, with multiple front-ends and levels, could have:
- Those who persue leads (marketing in any form).
- An intelligent dialing system.
- Loan pre-qualifiers.
- Loan Officers.
- Closers.
- Financers.
- Management.
- Real-time stats and reporting.
- Etc.
Maybe there are multiple systems and databases to look after. One may be over-tasked and have many other issues to deal with. Sometimes you just do not have the time to hand-pick through them. That is when it is time to automate some.
I would set up to track when stored procedures (or views, user functions, packages, etc.) are run. Then after at least a month, look into those that have not run. This should reduce the set to look at significantly, and allow one to make better educated decisions on what to do next, whether it be isolate, rename, delete, archive, wait longer, etc.
February 28, 2009 at 12:34 pm
i am in a different boat on this topic. I am the developer, and the DBA. I never throw away code. I might move it to storage, and never use it, but the minute you do that final delete, murphy's law calls up and now u are stuck.
i have ran into times where I have had to fix somebody else's mess, and that is usually done best by a rebuild.
as for the person who gets the third party updates that take one developer a month to implement.... i think you should really start demanding better quality code for your money. to me that sounds absurd, inefficient and flat out a waste of resources. better bet, take that developer, and task him to rebuild the app, keep good documentation and drop the third party vendor. depending on how often this vendor pushes updates you may find yourself in the black soon.
February 28, 2009 at 3:21 pm
Steve Jones - Editor (2/27/2009)
Tom Garth (2/27/2009)
:cool:It's also just plain dumb to leave them laying around like that.
It's not necessarily question of smart of dumb. It's easy to keep control of your app, but what about something you inherited? Sometimes it's not easy to determine what's needed and what isn't.
You are right. That line in my post was narrow-minded, and just referring to how I try to think when I'm the developer.
My main thought is that the clutter bothers me more than any other problems that unused code causes.
I have seen more than my share of this clutter that I felt would be impossible to clean up from a practical point of view. We can't always have the input we would like on selecting vendors, 3rd party apps, and specs. But then again, sometimes that problem helps a little to keep us employed.
March 2, 2009 at 7:45 am
I'm very much in the clean up camp. I have an inviolable rule that the only access applications have to the databases is to stored procedures, so that I don't suddenly find that some cruddy application prevents me from modifying the schema when necessary. I don't want applications to be developed that use SPs that are obsolete either because that means those SPs have to be maintained over schema changes.
If I have a stack of obsolete stored procedures in the system they present problems: when the schema is updated there is a choice between three different treatments of these SPs: prune them, leave them as they are (that is leave them not working after the schema change) or update them to match the schema. Updating them is a lot of work for no value. Leaving them in the database but not working is dangerous - some application developer may see them and try to use them (and a cavalier attitude to testing may mean he builds a great big edifice on the non-working stuff before he discovers that it doesn't work), and that is a waste of resource; so pruning is the sensible option.
Of course pruning doesn't mean that the obsolete SPs are no longer available to be revived if it's ever appropriate to do so - nothing that has ever been released is ever permanently deleted from source control.
This approach might not be the right one in all environments - but in an environment where rapid development and enhancement of the products is essential to staying in business pruning whenever the opportunity presents itself seems to me to be the way to go.
But I don't think use-frequency is a useful guide as to what to prune; in fact I am 100% certain that pruning everything that has never been called would be a disastrous mistake. We have SPs used only during recovery from catastrophic failures some of which have never been called except during recovery testing, so it's not necessarily right to prune SPs that have never been called; other SPs go around looking for problems (not kust in the databases, but in the rest of the system too) and call repair SPs (that reset remote COM+ components of the product, for example, or clear the mup cache, or run dcdiag) or reporting SPs (that email either us or customer staff requesting intervention); if the apps, active directory, dhcp-dns integration, all secure channels, all comms links, all third party apps and equipment that we have to interface to, and all the rest of the system are all behaving properly these repair SPs and intervention request SPs are never called - and on some customer sites some of them never have been called: but they may be called tomorrow.
Tom
March 2, 2009 at 9:51 am
Interesting thoughts. I would have thought that most people wouldn't prune old procs, but quite a few people do seem to do it.
And a great point about DR procs. They might never be used, but they shouldn't be removed.
April 16, 2009 at 1:08 pm
From a completely different angle, the more SPs there are in the DB, the more places there are to determine and manage security and rights, and the more chances trhere are that SOMEONE out there is still using something that has been replaced, possibly getting incorrect results.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply