We had a big database job that runs slowly at night. Sometimes it needs to be killed to keep the show running. My boss wanted me to work on this problem. He had a lot of ideas. So did I. My plan was to isolate what is slow, then figure out how to make it fast. I got overruled by the boss.My first task was to document what this big database job does. Done. I got a document that explains in English what the purpose of each part of the job is. Then I was told to find out how each of the parts is related.
I did a bunch of analysis. I determined what pieces depend on which other ones. After some deep analysis, I figured out what are the inputs and outputs for each of the jobs. This was hard because the package that runs the job is 7k lines of code. That package makes use of a lot of other helper packages that are a lot more than 7k lines.
Now my new tasking is to figure out if we can roll any of the many cursors up into a small number of cursors. I have have to determine whether some database indexes could make the job run faster. I have the option to isolate some SQL and pass it off to some DBAs for analysis and tuning.
Who knows where all this is going to end up. We might get some performance gains. Then again, I might be just running around in circles. I would love to be given the mission to just make this thing fast. But somebody else higher up on the food chain wants to drive the task with their ideas. Frustrating. Let's hope this does not turn out to be a fail. My time is money - literally.
