Tech, Ramblings, and Intellectual Detritus

I run into a fairly common problem in rails views when I'm pulling data from multiple tables.  To optmize on db access, I use :select to pre-load the data from the joined tables.  For example, let's say I want to list all users with their respective cities:

Then I don't have to load the Address model to display a user's city:

The problem is that my user partial is now coupled with my named scope:  if I render the partial without using the scope, I'll throw an exception.  To avoid the problem, I add an accessor method to the User model that will pull the pre-loaded data if available, and revert to using the Address model:

The partial will be inefficient, but I'd prefer to find the error when profiling view times rather than through exceptions.

Rails, does it scale? 

That horse has been beaten to death, and there is ample evidence that it does scale with a little elbow grease where necessary.  

It's also commonly known that many of the rails convenience methods aren't terribly good at making complex queries.

What I've just realized, is that some of the seemingly simple operations are implemented with horrible efficiency (the current tech-lingo would be "non-performant" which makes me puke a little into my mouth when I write it). 

Today I found two big performance problems:

HABTM relation creation

Take the following:

You would expect that code to do a single insert for all the join entries.  In reality you get something like this, repeated for each bar:

bars_foos Columns (1.1ms) SHOW FIELDS FROM bars_foos SQL (0.6ms) INSERT INTO bars_foos (bar_id, foo_id) VALUES (100, 117200)

I hand coded sql to do the same thing, and found a 7x speedup:

HABTM relation deletion

This one should be a layup:

@foo.bars = []

but I was shocked to see this in my sql log:

I hand coded the trivial sql and got a 10x speedup:

In Conclusion

These aren't difficult or complex operations, and they're extremely common.

I looked into ar-extensions, but there doesn't seem to be any support for HABTM relation creation.

I guess for now these queries should be hand-coded once tables get to a certain size, but I'm still in minor disbelief.  Anyone with a better "Rails Way" to do this, please clue me in.