OK, this note is technical in nature, but the lesson is applicable to virtually everything that we do. So, if you can muddle through the geek speak I’ll have a present for you at the end. Well, maybe not a present. In the end you may know the answer already and I’m just repeating what you are mumbling in your sleep every night. Not that I’m listening to you mumble but as long as you play Candy Crush just before going to bed … oh, never mind.
In the course of some fun reading, I was reading an article on “A DLM Approach to Database Testing“. Yeah, it’s kind of sad, but this is indeed fun reading. Anyway, the author, Edward Elliot aka Phil Factor, was talking about how changing your mindset, looking at database testing from a little different perspective, you end up with more maintainable code inside the database.
Simpler database code
Generally, the need to think about how to write simple tests that will prove their code works tends to lead developers to write simpler database code, each piece with a singular, specific purpose. For example, let’s say a developer needs to devise code that will:
- Retrieve a set of customer records showing their daily spending
- Run a calculation on those records to get the average daily spend
- Add an entry to an audit trail containing the result
- Return the result
The temptation might be to simply write a single stored procedure to do all the work and return the results. Consequently, you’d then need to write a test that also does a lot of work, to prove the right records are returned, that the calculation is correct, that we always get an entry to the audit trail. If the failing test does lots of things it is often hard to see what exactly what caused the failure.
However, if we think about how to write simple tests that verify just one thing, we tend to break the task down into manageable units, for example, writing a procedure to return the result, another to add an audit log entry, an inline function to perform the calculation, and so on. Each piece of code has a singular purpose and therefore so does each corresponding test, simply needing to verify that it does exactly what it is supposed to do. Being able to test each piece of code in isolation means that we also often get much more maintainable code, as instead of having a single procedure to do everything we can separate our code and re-use in other places
When I was younger I decomposed pieces of work into smaller, more manageable chunks. And this is exactly what the author is talking about: taking one complex tasks and turning it into four simpler tasks. A rule of thumb (from many years ago) was that if you couldn’t see all of the code on the screen at the same time, whatever you were writing was too long and needed to be split up. While this is a good rule of thumb, common sense does need to be inserted in there at some point. However, large, complex stored procedures should be broken up. I recently (okay, five minutes ago as part of some research into this article) discovered a stored procedure that was, including comments, over 1300 lines long. That is way too long.
What’s worse is that there are comments, from 2008, describing a bug that exists in the code.
From 2008. And it hasn’t been fixed.
And the complexity? There are over 45 SELECT/UPDATE statements with one of the longer select statements being almost 140 lines long. The complexity is enormous. So how do you test a single stored procedure that is over 1300 lines long and has known bugs? Well, there are ways, but they’re not pretty.
Quite often in life, we are tasked with what appears to be huge tasks that take our breath away. But, if we sit back and take a look at it, we invariably find that the one task is, in reality, a bunch of smaller tasks. And those smaller tasks? Quite often they can be decomposed into additional tasks. It doesn’t matter whether you are writing code or building an 80 story tower in downtown Edmonton, the concepts are the same: take the huge task and break it down into smaller, more manageable segments. The only challenge is knowing when to stop decomposing.