It's a familiar story, a tale as old as time itself. Well not quite that old but you know what we mean. Shiny new database with a lovely new process in place, running smoothly day after day, until one evening it takes so long to run that all the subsequent processes fail. Databases always grow. You will always have more data tomorrow than you have today. You need to constantly monitor processing times so you can spot potential problems and be prepared to refactor your code.
Process run times had started to grow longer as happens with increased data loads over time. Spreading out the job start times had worked in the past but this was no longer possible due to the increased processing time needed to execute the jobs. Something else needed to give and there are only two options, increase the spec of the server at increased cost or optimise and re-write the code.
After analysing the SQL Agent job step run times a number of procedures were discovered to be performing a lot worse than before. Several procedures needed to be rewritten to optimise the data retrieval. The process also needed to be changed to do incremental data loads minimising the amount of time needed to complete. By optimising the code, we were able to bring one process time from 30 mins to under 2 mins.