Not happy with Active Record today.

December 18, 2008 by alex

UPDATE 1/13/2009 I ran across an article on InfoQ about Performance Anti- Patterns in Database-Driven Applications. Check out the section titled ‘Load More Data Then Needed’. It’s very relevant to my beef with ActiveRecord, where ‘SELECT *’ is the default.

Using :includes always uses OUTER LEFT joins, even when it really should use INNER joins. No way to configure or change that. Also ignores your :select clause, so if you only want a few fields from the dependent models, too bad! This stinks when you want a text field from a dependent model, and you’re forced to bring a giant GeoRuby polygon along for the ride.

Using :joins does INNER joins, which is great. But if you later reference a dependent model, AR goes back to the db for ‘SELECT * FROM dependents’, even if you’ve selected data from that table in your original :select.

So there just doesn’t seem to be a way to * Load dependent models with INNER joins * and only load a few fields for those models.

My ‘rant in a comment’ from the source code I was fighting looks like this… “`ruby @locations = AlertLocation.userid( ).find( :all, # :select=>‘alertlocations.*,,, uszones.state’,

:joins=>[ :uscounty, :uszone ], # :include rather than :join to prevent

later ‘select * from uszones’, etc… (which is stupid because the data’s already in the resultset) # :include does an outer join when it should do an inner join, but can’t find a way to configure that. # 1 outer join query is better than N*2 inner joins. (1 uszones and 1 uscounties per location) # equally stupid… :include ignores my :select line and gets everything from all tables, including stuff I don’t want like the giant uszones.thegeom. # so which is less bad, way too many little queries or one way-too-bloated query? I choose way-too-bloated for now. :include=>[ :uscounty, :uszone ], :order=>:userlabel ) ”`

A trouble ticket describing this issue is marked as ‘wontfix’. Drag.

☙ ☙ ☙