T-SQL Tuesday #105: Brick walls
It’s the second Tuesday of August already, and that means it’s T-SQL Tuesday time. This month’s topic, chosen by Wayne Sheffield (b|t), is to write about brick walls we ran into and how we dealt with the situation.
Thinking about this topic, I remembered two moments in my career where I really was stuck … and then got unstuck in two very different ways.
The unconscious solution
The first event I want to share took place a long time ago. It was early in my career, my first programming job in fact. My job was to maintain and improve some programs, written in BASIC, for the administration of a laboratory.
I no longer remember what the issue was that had me (figuratively) banging my head against the wall. But I do remember the circumstances. It was a Friday, I was working on a program and I simply did not see any solution to the issue I was facing. I really wanted this done. I was eager to get this program done before the weekend, so that I could start on a fresh assignment on Monday. But I simply did not see any way how to solve my problem.
With hindsight, it is probably a good thing I didn’t own a car yet. If I had, I might have been sitting there, struggling to come up with something, until who knows how late. But now I had to leave the office in time to take the last bus home.
On the bus I was probably still mauling the issue in my head. But once home there was distraction. I cooked a meal, ate, flicked my favorite records on the record player, and played some computer games. I went to visit my parents on Saturday and Sunday. All this time I had so much distraction that I never spent one extra second contemplating the problem I had been facing that Friday.
And then on Monday I went back to the office, asked myself “now where was I?”, then remember that I had been very much stuck the Friday before. Anticipating yet another day of banging my head against, the wall, keyboard, and any other available object, I opened the program that I had been working on and the assignment document to remind me what it was I was about to do. I looked at the code. Looked at the assignment. Looked at the code again. And then I laughed at myself for getting stuck at this, because there was such an obvious, simple, elegant solution to this. I started typing, tested the code, and was done in less than half an hour.
I later realized that my mind apparently just needed to relax. When I was consciously trying to force myself to find a solution, my mind blocked. When I started relaxing and doing other things, I gave my subconscious mind the freedom to ponder the problem, without pressure or stress. I do not know whether the solution was born when I was playing games and listening to music, when I was sleeping, or when I was spending time with my parents. But I do know that it was there, ready for the taking, when I was back in the office.
This taught me an important lesson. Software developers (and probably most other professions too, but I’m not speaking for them) have their own version of writer’s block. If I run into that condition, then it doesn’t help to try to force a solution. It is far more efficient to just put the task to rest, pick up other tasks (or some time off), and let my subconscious mind do the hard work.
But taking time off until a solution presents itself is not a magic bullet that can be always used, as I experienced many years later. I was well into my 40’s and I had switched to database development. At that time I was working on a system to facilitate the implementation of rule-based derivation and verification rules. All rules were entered in a database and then the system would make sure that when data changed, derived data would automatically be updated and verifications were checked.
The problem I was facing was caused by the lack of restrictions. It was perfectly fine for users to specify rules where new information was derived from other derived information. My task was to design and write the code to optimize the order in which derivations were executed.
I had already spent multiple weeks on this, and I was getting nowhere. Whatever I tried, I kept getting incorrect results. Rules that should be evaluated were skipped, or were executed before the input data was derived, or sets of rules started running in endless loops. No matter what I did, I was unable to find the root cause. Whenever I found a problem I would issue a fix, only to get two new problems.
Taking my mind off the problem clearly didn’t work. There had been multiple weekends. I had worked on and completed various other tasks. I still ran into that same brick wall every time I returned to this issue.
Solving this was a lot of hard work. What I ended up doing was ditching the computer and the algorithm and resorting to pen and paper instead (well, actually an MS word document). I came up with various examples of what should be valid input to the algorithm. I tried to come up with the ideal order of events myself. And then I finally realized why I was never able to find a solution to the problem – it is because there was none. Even some fairly basic scenarios already caused loops in the ordering, with no way to automated find even a fairly okay order of processing the loop. The basic problem was in the foundation of the tool we were building. The assumption had always been that it should be possible to build this by tracking data that had changed and then processing the affected columns one at a time, in proper order. After several weeks of painstakingly working through countless examples, I was able to prove that this assumption was wrong.
I must admit that I was close to throwing the towel at that time. But I didn’t. I now knew the problem. I knew we had built on the wrong assumption. So why not change the assumption, build a new foundation, and take it from there?
Because I already had put in so much effort, I knew exactly where the old assumption failed. In this specific system, the choice to base the processing order on “processing one column at a time” was wrong. Instead, the processing should be based on “performing one process at a time”, and now I suddenly had a much easier solution to my original problem. Finding the correct order for processes was easy. Yes, there were still cases where I ended up finding a loop but that was always because the input had a loop (did I already say that there were hardly any restrictions on the input?) – so that was okay.
But the entire foundation of how we tracked the information, how the processing was orchestrated, would have to change. That was a lot of work and I did not even want to start it before making sure that it would actually have the intended results. So I went back to my Word document, described how the architecture should look like, then stepped through the processing for all the examples I had used to show that all of them would result in the correct result, in the most efficient processing order.
In the end, I spent several months (!!) analyzing the problem, pondering solutions, discussing options with my coworker, whiteboarding ideas, rejecting or refining as needed, prototyping, and mainly being very frustrated before I finally had the project rolling again. It is not often that one gets to invest so much time in a problem. It is luckily also not often that one needs this much time to find a solution. Let’s just say that this was a rather unique project.
This example did teach me that relaxing doesn’t solve every issue. Sometimes it just takes effort. Hard work, and lots of time are sometimes the only way to overcome an obstacle. And when that obstacle needs to be overcome, putting in the work is your only option.
For our profession, we need a mix of knowledge, experience, and creativity. Especially the latter can’t be forced. So if you feel that you run into a brick wall and make no progress, it can help to take your mind of the problem for a while.
Go fix another problem. Go home at the end of the day, spend time doing things you like. If time permits, move the issue to next week and go have a great weekend. Sometimes, just taking some time off is sufficient. While we are doing other things, some subconscious areas of our brains are still working on the problem. And these areas often operate far more efficient without the rigid force of needing a solution now.
But not every problem has an easy solution. Sometimes, a problem appears to be incredibly hard because … well, because it IS incredibly hard. At those times, there is no substitute for hard work. We just have to roll up our sleeves, pour an extra cup of coffee and start grinding.