How to decide, what’s worth optimization

I want to talk about one optimization I did on Friday, and about why I’ve spent literally half a day on this optimization.

We all know, that we can’t “optimize the whole world”, and that each time we want to optimize something, we should first try to estimate, whether the optimization effort is worth the gains we’ll get.

When we have a long report, which runs for 2 hours and eats up all server resources, we know we should optimize this report.

When we have an application controller action which is executed for 30 seconds, because the screen rendering requires 500 database calls, we know that we need to optimize the Ruby code and reduce the number of calls by executing “smarter” SQL statement.

But what if a background job is slow? Most of the time we want to optimize a batch job, if it is executed often and effects other jobs, for example, if a job runs every 15 min, and it takes 5 min per execution. If a batch is only executed once a day, most of the time we say “it’s OK if it’s slow”, unless it’s really slow.

Now, guess what I was doing on Friday: I’ve spent more than three hours to optimize the background job, which only runs once a day with the execution time about 2.5 min.

Why this job absolutely needed to be optimized? Because of the way it was written: the first statement reads virtually all payments for all loans which exist in the system. It was taking “only” 2.5 min, because currently we have a very small number of loans in the system. If we plan to increase this number a thousand times within the next year (which we plan), the execution time would increase proportionally.

So the most important thing in my optimization was not the fact, that I’ve improved the execution time from 2.5 min to 6 seconds, but the fact that now this execution time will increase very insignificantly, when the number of loans in the system will increase. Besides, the new job is using way less resources (the original job was creating three huge temporary tables, which almost blocked the possibility to test it in the development environment).

And I am really glad I caught this situation early enough it didn’t cause us serious problems!

Advertisements

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s