Like everyone else, I make mistakes. While the results can sometimes be unfortunate, it’s also a truth that shouldn’t be ignored. A recurring problem though is that as a designated “expert” sometimes people don’t bother to test what I’ve given them. They just roll with it and then are surprised when their production installation goes awry.
I just ran into this situation again a few days ago. I was asked to help with a query that didn’t ever finish. I worked on it for a little while and came up with something that finished in a few seconds. Since the original didn’t finish, I didn’t have a predetermined set of results to test against. I manually walked through some sample data and my results seemed to tie out… so, it seemed like I was on the right track. I showed the client what I had and they were elated with the speed improvement.
I gave a brief description of what I had attempted to do and why it ran quickly. Then I asked them to test and contact me again if there were any questions.
The next day I got a message that they were very happy with the speed and were using it. I was glad to hear that but I also had been thinking that my query was extremely complicated, so even though it has apparently passed inspection I spent a few more minutes on it and came up with a simpler approach. This new method was almost as fast the other one but more significantly it returned more rows than my previous version. Clearly, at least one of them was incorrect.
With the simplified logic of the new version, it was much easier to verify that this second attempt was correct and the older more complicated version was wrong. I reached out to my client again and notified them of the change in query and problem I found. Then suggested they rerun more extensive tests anyway because I still could be wrong.
Fortunately, this second attempt did appear to be truly correct and the performance was still more than adequate.