The power and pain of ActiveRecord…
Ok, this came up in discussion last night when we were hanging out at Cafe Gecko after the Refresh Dallas meeting. I like the speed and flexibility of writing against the ActiveRecord system, and inside the MVC model. It makes coding less work than the design, which is certainly new for those of us coming from old-school PHP and Python coding. Take this example:
There are four tables that are used regularly in SimpleTicket:
- Tickets
- Users
- Journals
- Customers
The tables are all tied like so; Tickets has_many Users, Journals. Customers has_many Users. etc. etc. etc. So when finding the name of a user who owns a certain ticket, I can write:
< %= Ticket.find_by_id(id).user.name %>
However, there is a minor concern with this. That simple statement, while fun to write, generates two SELECT statements against the SQL database. Seems kinda like overkill? It gets worse. There are many different ways that I use this model set to paint the pages shown in the admin utility, and the My Tickets page can churn out some really heavy SELECT statements, in large numbers. So while it is easy to code, how do you control the sheer dearth of queries to make your application behave responsibly? Yes, you can do things like create objects on the fly with find_by_sql and such, but that really takes away the power of the MVC model.
If any of you have any thoughts on this, I would love to hear them.
January 12th, 2006 at 6:16 pm
I’ve had two thoughts about this since we talked last night.
1) Are you embedding the find_by_sql call in the controller or in the model? My thoughts are that if you’re doing it in the model than you havn’t broken MVC.
2) No language/framework combination is perfect and there isn’t a “best” one, but a choice in tradeoffs. Here’s how I think it boils down.
* You can use PHP, which will be nice to the developer (easy to code), nice to the hardware (low resource usage), but terrible to the software (usually void of best practices).
* You can use Java, which will be nice to the software and the hardware, but terrible to the developer by making him or her jump through lots of hoops.
* You can use Ruby+Rails, which will be nice to the softare and the developer, but terrible on the hardware like you mentioned.
You’ll have to decide which you can afford most to make suffer. Seeing as I’m a developer (me first), and developer’s arn’t cheap (good ones anyway), and that the quality of the software directly affects the maintainability (i.e. the developer again), I’m inclined to let the hardware suffer. The tired line is that hardware is getting cheaper (cheaper != cheap), but it is a much better situation than during the last bubble. Throw another database server on the fire.
January 12th, 2006 at 6:38 pm
If Ticket has_many users then shouldn’t it be:
Also I think this will only generate one SQL query (using a JOIN)
:user ).user.name %>
January 12th, 2006 at 6:40 pm
hmm.. apparently wordpress isn’t auto escaping html entities. I’ll try again.
If Ticket has_many users then shouldn’t it be:
< %= Ticket.find_by_id(id).users(:first).name % >
Also I think this will only generate one SQL query (using a JOIN)
< %= Ticket.find(id, :include => :user ).user.name % >
January 12th, 2006 at 9:07 pm
Myles: I will have to test that. I am interested to see if that would do just one query. And you are right on the has_many thing. Its actually the other way around… Tickets :belongs_to users, and User :has_many tickets. In any event, it still generates like WOW amounts of SQL statements.
Chris: Great to meet you last night brother. I hope you catch a gig soon!
January 12th, 2006 at 9:35 pm
“You can use Java, which will be nice to the software and the hardware”
Java’s nice to the hardware? Really? Especially if you’re using MVC and an O/R mapper I can’t see how it could be…
January 12th, 2006 at 9:43 pm
How about something like:
Ticket.find(id, :include => :user).user.name
Looking at the docs this should do something like:
SELECT …
FROM tickets
LEFT OUTER JOIN users …
So one query does it all
Ref: http://ar.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
(see Eager loading of associations)
January 12th, 2006 at 10:15 pm
Similarly, let’s say you have an admin page displaying all tickets (or all tickets that match condition X)
Then you would use Ticket.find(:all, :include => :user) (or maybe add :conditions as well) then print them all out. Only one query would be sent to the DB:
SELECT …
FROM tickets LEFT OUTER JOIN users ON users.id = tickets.user_id
(just tested it)
January 13th, 2006 at 8:56 am
Hmm… Looks like I have to test this…
January 17th, 2006 at 2:04 pm
As people have mentioned, alot of this has worked. Funny thing is that it hasn’t necessarily sped up the application, likely because more data is being retreived at once, but I appreciate the education that I got on this post! Thanks everyone!