Strange dream leads to improved algorithm

People don't normally remember their dreams for long, so I'm setting this one down before I forget it.

I was with a group of people (I think that we were students on a course together) who had to run around a ship (some structure with at least two floors or decks) and improve our time for the course. Normally it was taking around five minutes, but I managed to run the course one time in four minutes. Speaking to the group (rather pompously, I admit), I started drawing parallels between how I had managed to improve my time by 20% and tuning computer algorithms. Small increases in performance can be achieved by looking at each part of the algorithm and applying local optimisation. In the dream, an example of local optimisation was the way I was descending a ladder between decks; normally I was climbing down the ladder 'properly', putting my foot on each rung. The local optimisation was to slide down the ladder, holding on with my hands but not using my feet.

The problem with local optimisation, I explained to the group, is that there is limited scope for improvement. True, it may be possible to end up with a time which is maybe five times as fast as the original, but in order to gain true improvement, one has to find a better algorithm. In computer terms, this is (for example) one which works in logarithmic relation instead of linear relation.

Then I woke up. Still thinking about changing algorithms, I started thinking about a problem at work. I started programming over twenty years ago, when computers were relatively slow, lack of memory was a major restraint, and it was very important to tune one's algorithms in order to obtain maximum performance. I don't work in a field which still requires high performance from the cpu, so these days, such matters seem unimportant. But I do know one report in one program which could do with some improvement, and not surprisingly I was working on it with no success the other day.

I have a database which maintains data about dead stock at work (defined as parts which haven't been bought for the last six months but the stock amount is more than twelve months usage), and one of the reports which I produce is a list of changes in the last month - what's been added (not good) and what's been used (good). At the moment, I'm using a simple, straight forward and brute strength approach to this problem: for every part in the database, I get the amount for one month and the amount for the previous month. If the amounts are different and not zero, I display the result.

This works, but it is incredibly slow. I've been trying to find a masterful SQL statement which will allow me to do what I want, but so far I've been unsuccessful. This morning I woke up with a new approach which involves three queries and may seem more complicated, but should be much faster than the simple approach, because the number of parts being looked at is much smaller. First of all, get data for the parts which had a value in both months where the values are not the same and not zero (I have a SQL query which will do this). Then get data for the parts which have a value for this month but not for the previous month. Finally get data for the parts which had a value last month but not this month. The resulting combined list will be the result set which I require.

Of course, I will check that the results which I get match the results which I was getting via the simple approach. I will also time both methods and see which one is faster, even though I'm sure that this new approach will be better. What might be construed as funny - or counter instructive - is that I am spending hours on finding a better approach for a report which will save maybe five minutes per month. Looked at in this light, it's not worth the bother to improve the time.

But it's an intellectual challenge, and that's what's important. Keep burning new synaptic connections in the brain. Thinking about thinking can sometimes be more valuable than simply thinking.

I was thinking about thinking, but it didn't really get me very far - Peter Hammill ("Black room")