Topic: Creating SQL Joins to reduce SQL queries

Hi, me again:

Just looking for a way to either create a SQL join to reduce the number of SQL queries run.

Basically, now I have some clients which have an owner (from the users table).

I am producing a list of all clients and I want to print the name of the owner but I don't want to run a query for each client.

I would normally construct a query like:

SELECT c.*, u.surname AS owner_surname, u.forename AS owner_forename
FROM clients c
LEFT JOIN users u ON c.owner = u.id

At the moment I have a belongs_to setup:

belongs_to :owner, :class_name => "User", :foreign_key => "owner"

However, this is running a query for each client which isn't ideal (espically when you have lots of clients)

  Client Load (0.001652)   SELECT * FROM clients WHERE (clients.account_id = 1) ORDER BY name
Rendering  within layouts/default
Rendering clients/index
  Client Columns (0.002466)   SHOW FIELDS FROM clients
  User Load (0.001473)   SELECT * FROM users WHERE (users.`id` = 1)
  User Load (0.001415)   SELECT * FROM users WHERE (users.`id` = 1)

I've had a look through the Agile rails book and a quick google although can't seem to find anything useful.

Thanks

A

Re: Creating SQL Joins to reduce SQL queries

I think this is a pretty good blog post on it:

http://railsexpress.de/blog/articles/20 … attributes

It covers the base case, then using :include to simplify your queries (which looks like it'd fit the bill for you), and then it goes onto piggy backing attributes to speed things up even more.

Good-Tutorials. Now with tutorials for Ruby and for Rails.

Re: Creating SQL Joins to reduce SQL queries

From a database overhead point of view joining the second option is better although from a code management and keeping things neat point of view, include or not bothering at all seems better.

Edit: that post was written in Nov 05, surely somebody has come up with a better solution than whats there.

Last edited by shadow (2007-03-28 18:32:22)

Re: Creating SQL Joins to reduce SQL queries

Actually, after googling around for awhile, I'm not sure what the current status is anymore, really. I've been reading stuff that a new version of Rails a few versions back eliminates the need to do something like this. Can anyone comment on an updated status on the need to perform piggy backed queries nowadays?

Good-Tutorials. Now with tutorials for Ruby and for Rails.

Re: Creating SQL Joins to reduce SQL queries

I would just use the ":include" statement for now. If you find the performance isn't good enough, switch to the second approach.

From what I can tell, the article is just as relevant today as it was when it was written.

Railscasts - Free Ruby on Rails Screencasts

Re: Creating SQL Joins to reduce SQL queries

I just had the same issue, and browsing through the forum I didn't really found a satisfying answer so I bring back this old post wink. So trying around I found this solution ( rails 2.1, haven't tried with older versions ) :

@client = Client.find(:all, :joins => "LEFT JOIN users ON clients.owner = users.id", :select => "clients.*,users.surname AS owner_surname, users.forename AS owner_forename)

this is in the case where clients do not necessarily have an owner, if they always have one, they it can be written like this :
@client = Client.find(:all, :joins => [:owner], :select => "clients.*,users.surname AS owner_surname, users.forename AS owner_forename)

No need to add anything in the client model anymore.

I still don't quite get the interest of :include could someone explain that to me ? As I see it, it just does one more request.

I hope this might help someone.

Re: Creating SQL Joins to reduce SQL queries

:include gives you a full "object tree", so that you can do user.project.due_date for instance. Using :joins, the result would be: user.due_date.

I never user :include, it adds too much overhead and therefore is incredibly slow. Anyone who advices to use :include doesn't have a clue about scaling/optimizing a Rails app.

Re: Creating SQL Joins to reduce SQL queries

How I understand :include is that it actually make a new request to the include model. For instance, if you do

@users = User.find(:all, :include => [:project])

it will do 2 requests, the first one to query all the users and the second one to get all the projects corresponding to all the users. So that later on in your code if you do something like :
<%@users.each do |user| -%>
  <%= user.project.due_date %>
<% end -%>

It won't make any new request, it will use the projects that you pre-loaded in the include. That's what you call a full object tree i guess.

No one actually advised me to use :include, I was just curious.
I m actually quite satisfied with my LEFT JOIN solution, seems quite clean and efficient.

Thanks for your answer.

Re: Creating SQL Joins to reduce SQL queries

It's not a big issue if :include makes an additional request to the DB, MySQL is fast, the problem is that AR must then instantiate an object for each row retrieved, and this takes a huge amount of time.