The class where I learned method-R performance tuning also taught me something else, something I call the consultant's gambit. This is that story.
Was a three-day in-seat program with canned exercises. We learned a little bit about the back-end of Oracle performance, than sat down with a small program, ran it, and learned tools to show how long the software took in each line of code. Basic method-R stuff, but my interest was in the consulting aspect -- what did the trainer do when he was onsite? What did he find?
I wanted to hear the story.
Oh, I know, every client is different. I know, what works for one company might not work for another -- still, tell me stories. Let's forget about theory for a bit and talk about a few specific examples.
The trainer stiffened. He looked as if he were struggling.
This surprised me; shouldn't he have a dozen of these stories on the tip of his tongue?
After a bit of an awkward pause, the trainer (who I will call Bob) spoke, and told us one story.
Was a typical big company with a large IT department sprawled over several buildings, not to mention, I have the impression, a fair amount of outsourcing, mostly offshore. The company had some new applications, and they were slow.
Dog slow. 'Doesn't make any sense' slow.
So the company asked Bob to stick around after training, to do some consulting. Bob would be looking at real problems and try to solve them, sort of like the pseudo heroes in House of Lies. (I say pseudo, because, well, they aren't really heroes. More about that the show another time.)
After training, Bob dug in, and found the company was relying excessively on views, which are stored queries that look like tables, instead of actual table.
Now by stored query, I mean just that:
Create View OVER_65 AS Select * from employee where BirthDate < (sysdate - 65 years);
Having views allows programmers a sort of shorthand; they can start from over65 and then get the fields or data they need. If you are continually requesting the same subset of data, a view makes sense.
The problem with this customer is that each department, and sometimes each programmer, was using the technique, creating views of views of views, sometimes ten or eleven deep. In order to respond to a query, the software had to calculate each view in the chain and do the compares.
This meant that each query wasn't one, is was ten. Add in some joined tables in one view that are removed in the next, a few tables with millions of rows, and you end up with a system with incredibly poor performance.
Bob took the team back to first principles, starting with actual data tables, and forbidding views more than one or two deep, and fixed the problem.
What struck me with the story was this: The problem was one single goofy thing. Just one bottleneck.
All the things we learned in class about tuning and optimization didn't matter, as long as you can find the goofy thing and fix it.
That is the consultant's gambit.
An Outsiders Perspective
The consultant is betting that clients, being human, will have problems, but they won't be able to see them as problems, or see solutions, because of the culture.
"We've always done it that way"
"That's what I was told"
"Of course the employees would say that."
Gerald M. Weinberg, author of Secrets of Consulting, refers to something like this in his five minute rule: That customers always know what the problem is, and will tell you the problem within five minutes of your arrival. The job of the consultant is to be expensive enough, and viewed as objective enough, to make the reasons why the solution "can't" work go away.
Now i've done some consulting in my time, and it isn't usually as easy that, but there is certainly wisdom in the five minute rule. Take Bob's story, for example. Do you really think that no one in the IT department realized that computational explosion of views was a real problem?
No, it is likely that they knew.
It also is likely that each individual programmer had a deadline to hit, and the fastest way to hit that deadline was to use the four views he already knew about to create a fifth view, then to build a simple query. "Rebooting" the project, starting over from scratch, that's the stuff of pipe dreams, the kind of thing you can only do if you have power, like a director or consultant might have.
I don't know if that's what actually happened, but I suspect it might be something like that.
Now I am not writing this to knock on consultants; after all, I am one of them! There are a lot of ways a consultant can add value, and many do good work with real expertise that they pass on to the client. If Bob hadn't found the views problem, I am sure he would have rolled up his sleeve to look at the the most mission-critical applications, the queries they ran, how those performance, where the slow pieces were, what indexes those hit, and so on.
The thing was, Bob didn't have to. The gambit paid off.
The Performance Lesson
The first time there is a software performance problem, the answer will probably be obvious. It is likely a single bottleneck, a single problem, that is limiting performance. Once you have done a few rounds of performance improvement, you'll be left with a lot of little problems, not a single big one.
The first time, though, it'll be obvious. Take a break. Get a cup of coffee. Go for a drive at lunch; give yourself the freedom to let your mind wander, and you'll come up with the answer you need.
Now, getting someone to listen? Getting a change to happen? Yes, a consultant can help with that, but don't forget -- you are the most powerful person in the room.