October 2, 2024 at 12:00 am
Comments posted to this topic are about the item Cleaning Up Your Database
October 2, 2024 at 11:35 am
People like Brent who say "don't clean up old code" apparently don't have much experience with 40-50 year old codebases that are still mission critical for more than 18 million people. I have.
And the one I'm referring to has so much old code, that *any* change is taking a minimum of 6-12 months, leading to a situation where the interest on the technical debt requires about 9-10 times more money than the actual changes and maintenance.
Even worse, we *know* that most of that code is, in the course of a year, never called. But nobody can say it won't be called for a millenium-change, so... does it stay in?
If you make throwaway code for a prototype, fine. If you are building mission critical stuff that you will maintain for a long time? That old code will start to add up. It will make it very very expensive to move to something new, or even change the existing code.
And the same goes for old tables. If you don't want to remove them, move them to a new namespace or old_database, or add triggers to it to monitor any change. No change in 3 months? Drop it via stored procedure. You can even make a stored procedure you run for any table you want to monitor that way, and automatically rename it after 3 months, and terminate it permanently after 3 more.
The idea of being unable to recreate your database from scripts and finding that acceptable is just... mindblowingly bad development practice.
As a freelancer, however, I say: keep it up! My fees are growing higher, the more desperate my clients are.
October 2, 2024 at 1:11 pm
Another good reason to clear out unused code is when you have a column that is used to filter results and you find that it can contain a new set of values (eg. due to a vendor's upgrade). "Great, I'll just use Redgate's SQL Search to find out where it's used so I know what might have to change" (other methods are available). Around 30% of the views and procedures in the list for checking turned out to be old versions or parts of abandoned developments.
October 2, 2024 at 2:24 pm
Steve, you touched a live wire on this one with me. In my humble opinion ( and you all know how humble I am ), if you don't do the cleanup as a part of the implementation, you are not doing your job and fulfilling your responsibilities to yourself and others. Of course, a large part of the responsibility for DOING the cleanup is being sure you can UNDO the CLEANUP if you MESSUP. The first priority for cleanup seems to me to be knowing your systems well enough to know the effects of the cleanup process. The second part of that responsibility is being able to fix anything you might SCREWUP. In my years as a DBA, we always included the cleanup in the process of implementation of new or changed code and data. Of course, any altered or removed data and code was BACKEDUP
It's easy to complain about what some predecessor left for you to figure out, but remember that someday YOU will be the predecessor yourself.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
October 2, 2024 at 2:58 pm
I'll admit that I am a culprit. The dev instance looks like the SQL version of a condemned building. Even PROD can be cleaned up a bit.
A decent project for the off season. 🙂
October 2, 2024 at 8:10 pm
I'll admit that I am a culprit. The dev instance looks like the SQL version of a condemned building. Even PROD can be cleaned up a bit.
A decent project for the off season. 🙂
My seasons are all 'off' now, but I enjoy your condemned building comparison. They always seem to collapse on themselves...
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
October 3, 2024 at 12:37 am
I always clean-up as I go, this way I do it when I am working on it knowing that I will not have a chance later on.
It is a bit like at home, do the dishes at night and keep the house clean. If you do not, the dirt will be harder to clean.
If I inherit databases with years of neglect in them, the issue at hands is different. I will skip the clean-up and build myself a list of objects that I need to perform my job.
Like building an excel table based on sys.objects and add tags to the objects.
I always find funny when old tables have the word new in their name...
Philippe
October 4, 2024 at 4:21 pm
People like Brent who say "don't clean up old code" apparently don't have much experience with 40-50 year old codebases that are still mission critical for more than 18 million people. I have.
And the one I'm referring to has so much old code, that *any* change is taking a minimum of 6-12 months, leading to a situation where the interest on the technical debt requires about 9-10 times more money than the actual changes and maintenance.
...
As a freelancer, however, I say: keep it up! My fees are growing higher, the more desperate my clients are.
I tend to agree with you. The more code there is, broken or not, the more people are fearful of disturbing things. This extra code is technical debt, and it inhibits agility.
Though if you're paid by the hour, I suspect you have no interest in changing things.
October 4, 2024 at 4:23 pm
Another good reason to clear out unused code is when you have a column that is used to filter results and you find that it can contain a new set of values (eg. due to a vendor's upgrade). "Great, I'll just use Redgate's SQL Search to find out where it's used so I know what might have to change" (other methods are available). Around 30% of the views and procedures in the list for checking turned out to be old versions or parts of abandoned developments.
That's a great point. How much time to we (as a team, across lots of tickets) waste looking through or evaluating code that isn't useful? We're spending time on things that aren't adding value for clients. And we keep doing it. At least if we did it once and marked it for removal (and renamed), we'd stop this time wasting and be able to focus more on actual new work that is requested.
October 4, 2024 at 4:24 pm
I'll admit that I am a culprit. The dev instance looks like the SQL version of a condemned building. Even PROD can be cleaned up a bit.
A decent project for the off season. 🙂
There's never time to make this a project. It should be a regular and constant set of maintenance that you perform as a part of work. You'll never get it finished, so accept that and manage it.
October 4, 2024 at 4:25 pm
Steve, you touched a live wire on this one with me. In my humble opinion ( and you all know how humble I am ), if you don't do the cleanup as a part of the implementation, you are not doing your job and fulfilling your responsibilities to yourself and others. Of course, a large part of the responsibility for DOING the cleanup is being sure you can UNDO the CLEANUP if you MESSUP. The first priority for cleanup seems to me to be knowing your systems well enough to know the effects of the cleanup process. The second part of that responsibility is being able to fix anything you might SCREWUP. In my years as a DBA, we always included the cleanup in the process of implementation of new or changed code and data. Of course, any altered or removed data and code was BACKEDUP
It's easy to complain about what some predecessor left for you to figure out, but remember that someday YOU will be the predecessor yourself.
Love this
October 4, 2024 at 4:29 pm
I always clean-up as I go, this way I do it when I am working on it knowing that I will not have a chance later on. It is a bit like at home, do the dishes at night and keep the house clean. If you do not, the dirt will be harder to clean. If I inherit databases with years of neglect in them, the issue at hands is different. I will skip the clean-up and build myself a list of objects that I need to perform my job. Like building an excel table based on sys.objects and add tags to the objects. I always find funny when old tables have the word new in their name...
Good analogies. Regular work, as long as it doesn't become the majority of time, is a way to keep things manageable. I might skip dishes tonight, but I need to get back to it tomorrow or things will grow out of control.
I dislike _new or _old, or _2, etc. I usually tell people, when you aren't sure what to do, defer but mark. Use _delete_20250331. A lot of places I've worked, we worry about eoy processes, but most things close by the end of Q1, so at the very least mark them for removal at that time (and set some ticket/reminder).
FWIW, I'd also make two branches with PRs. One could be fb-rm-deprecatedtable with
delete dbo.sometable_delete_20250331
The other would be fb-fix-sometame-before20250331 with
sp_rename 'dbo.sometable_delete_20250331', 'dbo.sometable'
October 5, 2024 at 1:45 pm
I tend to agree with you. The more code there is, broken or not, the more people are fearful of disturbing things. This extra code is technical debt, and it inhibits agility.
Though if you're paid by the hour, I suspect you have no interest in changing things.
Funny enough, it's usually some of the people that live in that place that have no interest in cleaning it up, and quite a few of the freelancers hate it because it prevents them from achieving their objectives in the time and budget that was allocated. Unless the company has the habit of hiring the same freelancers forever, but that's a failure in and of itself because why exactly get a freelancer other than for some fresh new perspective? If it's just hands, hiring someone is cheaper in the long run.
Personally I tend to get really annoyed when people have a lot of technical debt because guess who is going to have to shovel that dungheap? Yep, that's me. And it is worse when the people who built the dungheap are still there, because if you clean it up you demonstrate their failure. More so when they have claimed for years that "it coulnd't be done". Now suddenly you don't get invited to meetings anymore. And yes that happened. I found it very funny, but I imagine if I had worked there in a permanent position I might have found it less funny.
October 7, 2024 at 4:21 pm
To be fair to Brent his time, as a consultant, would not be time well spent cleaning up a client's code base.
When I'm analysing an old code base for a client I don't know which of the items still present is active or not. It takes time and therefore money to analyse the old artefacts. Time and money wasted if those artefacts should not be there.
For new employees, how do they know if something is used or not? Do you remember that something you once saw turned out not to be used or do you dismiss it and move on because it wasn't important? If you don't clean old stuff then the next new employee is going to face exactly the same challenge and the existence of that challenge does not deliver business value.
I had to work out why a production system was not working as expected. Digging through the code it looked as if the deployment script was deploying an older version of a particular function. It turned out that the reason the version was so out of date was because another part of the code had deprecated the thing that read the file with the old version in it. Again, time and money wasted but with the added risk of a misdiagnosis and potentially deploying the wrong version at the wrong time.
I find it intensely frustrating that the "no business value" argument is used so liberally. Pretty soon you end up with a mountain of "No business value" causing fear and doubt, slowing everybody down, costing a lot of money.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply