I am not sure how many of us have had this kind of an experience but this happens often to me. Over the last couple of days I have been reading about good performance tuning tips and within myself started wishing that I would get an opportunity to fix one.
And (reminds me of Paulo Coelho in The Alchemist - “The whole Universe conspires to give it to you”) the very next day an opportunity presented itself.

An issue suddenly cropped up with one of our RoR applications. All seemed ok after the first week of going live but as more legacy data was migrated into the new app a gradual increase in reponse time became apparent till it reached a point where the response times were clearly unacceptable.

I began the surgery by looking at the code and found that issue was related to the way some ActiveRecord objects were used to render data on some screens. On one of the screens it looked like a collection (about 2000 rows) of an entity with 6+ associations was being rendered on a screen.
Also while rendering on a grid it looked like the user wanted to see not just the root enitiy’s attributes but also some attibutes of its children and grandchildren.
While normally a single query would be enough (and this was the fix I put in) to pull all the 2000 rows, here in this case a naive usage of ActiveRecord caused it to lazy load most of its associations and consequently about 6 queries were being fired for each row. Hmmm….happens.

While this event turned out be not so technically challenging after all it certainly offers a lesson in technical judgement. The problem is not a new one but nevertheless the most repeated mistake I have seen. And the causes of this are more to do with lack of understanding of how to use the tools you work with and the step motherly treatment we give to SQL.

It the desire to build a good OO application (which is hard enough) without worrying about how it is represented in a Relational schema that has created for us a special class of tools that we call ORMs. Different ORMs solve this problem for you in somewhat different ways.
But after working with a couple of different ORMs I am increasingly begining to feel that they are most useful in transactional use cases and anywhere else where you have a show-only/reporting/search requirements you are better off with PO-SQL.

I understand why someone prefers just working with objects than go through the pain of managing connections, creating prepared statements, going through result sets etc. Yeah its so early 2000-ish to do that. Also most ORMs have now acknowledged the fact that there are situations where the user would like to have complete control over the SQL that needs to be executed.
Hibernate goes so far as to let you map a PL-SQL procedure to an object.
ActiveRecord provides a find_by_sql and the nicest thing about it is you can put any query in there like this

list_of_foos =
Foo.find_by_sql(”select foo.name,foo.age, b.city,c.zip from foo,b,c where…”)

And this list_of_foos contains a collection of Foo objects from which you can retrieve name, age, city and zip as though it were its own attributes.
This often surprises people, especially ones doing rails for the first time. Understandable.
And then there is iBatis which is complete freedom in some sense and yeah I am loving it.
With all these goodies one is more tempted to stay away from SQL and treat SQL as a step child. We are so spoilt that today any ORM that “helps” the “object programmer” from dealing with SQL is “perceived” as “better”.
I think here is where the mindset needs to change. While I agree that for most use cases the SQL that is used is routine, learning to write good SQL should be a priority on a pragmatic programmer’s list. Also be careful when you use ORMs. They are great, help reduce lines of code and thus boost productivity but not every use case of your application needs it, atleast not to be used in same way, especially search and reporting use cases.

‘Nuff said !!. Me off to pester the DBA for an SQL Bootcamp!!