I try to run execution statistics on our application controllers every day, or at least every other day. Each time I see some controller action produces excessive number of database calls, I am bringing this to my squad attention, and we create a task to investigate.

However, I am not a purist:), so I am OK, if a controller is not “perfect” and not completely optimized. Usually I am OK. But not in case of our payment presenter. The problem was, that the page time was way more than database time, which should not be the case: the page time should be only a little bit more than the db time. Moreover, after playing with statistics I realized, that the more db calls is executed by controller action, the bigger is the said difference, which sounded completely ridiculous! So, I pushed for investigation.

This was something like a mistery novel, but I will spear my readers from the details and proceed directly to our findings. The first thing we found was, that each payment had to read a loan type, and as usual “could not memorize it”, so for each payment it was reading a new copy of a loan object! No wonder we had memory issues to severe, I had to implement pagination on the database side! So, my coworker Richard fixed this by making the method referencing only one loan object.

But that was not it. I hope Richard will eventually create a pretty diagram for me, but for a time being let my try to explain it without a visual aid.

In my function I was returning the “payment status code”, which was an ach status code if ach is present and “some other status” otherwise. So – I returned this value using COALESCE function. Guess what was happening in the payments presenter: it would search for a “description” of this code, which would be an ach code description, if there is an ach and null otherwise! So in reality we only need to join with one table! And moreover, we already had this join (to get “a code”), in all other cases it will be NULL anyway, but no model could figure this out! And moreover – even if it could figure this out, it will still have to go “one select per returned line”, because this was a “different” join, not the one specified in the model in the payments – ach relationship. So – eager loader could not be used….

Solution: I added one more field to the function – ach code description. There was zero extra db work, because the table was already present in the FROM list, we just didn’t se;ect this particular field.

Result: steadily 2 db calls per controller action! I will report production statistics later, when this solution will be in production (on Wed, I guess).