How to save 15,000 database calls in 10 minutes

The other day I was looking at the statistical data – the number of execution call per controller action, which I try do if not every day, then at least every other day. To my surprise. I saw one “new” controller making it’s way to the top of list. Two things surprised me. First – the fact, that it appeared out of nowhere, and for the best of my knowledge was the simplest thing possible. Second – the the difference between min and max db calls per execution was ginormous – from 1 to over 15,000! (averaging 53 – that’s why it started to make a top).

So I asked the application developers, and they told me – there is nothing there, jus one select. I checked the Ruby code myself, and it was literally 3 lines, which were the straight ORM, and should have result in generating a straightforward:

SELECT * FROM notes WHERE customer_id=<?>

So – where are those 15,000 calls coming from?!

I’ve asked my teammate how we can find this out, and in five minutes we knew the answer: each of the “notes” returned by this SELECT statement, would generate additional SELECT statement, which would go like this:

SELECT * FROM employees WHERE id=<?>,

where id was an employee id from the note!

Please note :), that the association between notes and employees was already in the model, so the only thing the developer had to do was to turn on the eager loading, which would generate correct SELECT statement with one JOIN. The next five minutes where spend on making this change in the method, and testing that it produces exactly the result we expected.

So, after 10 minutes we were able to reduce the number of database call in this controller action from 15,000 … to one!

Leave a comment

Filed under Data management, Systems

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s