Topic: Query Performance

I come from a background of writing my own DB queries for web applications, and I'm still not entirely comfortable with Rails doing SELECT * on everything.

A couple questions:

* If I know in advance that I will only be using a specific column or two from a model, is there a find() syntax that allows that without resorting to custom SQL? I'd also be curious to know if there's a way to grab just one column from a second table through eager loading. Or will a model freak out if it is only partially populated with data?

* MySQL caches queries. So if two of the same query are submitted in a short time, the second query result will come out of the cache. Has anyone done any testing/experimentation to see if a complicated join query created by eager loading is really faster than multiple simple queries without eager loading? Eager loading could be faster for the first query, but might not be advantageous if other pages would execute the same simple queries, but a completely different eager loading-style query.

I was thinking it would be cool if Rails had some sort of built-in optimizer which would examine controller/model/view code to see which data columns are being used and only query the DB for those columns.

I'm just thinking out loud. My Rails app is too new for me to worry about much optimization, but I'm starting to look where I might be hitting the DB too hard.

Re: Query Performance

boone wrote:

* If I know in advance that I will only be using a specific column or two from a model, is there a find() syntax that allows that without resorting to custom SQL? I'd also be curious to know if there's a way to grab just one column from a second table through eager loading. Or will a model freak out if it is only partially populated with data?

You can use the :select option if you are just doing a simple find. I don't think this works with eager loading though.

Item.find(:all, :select => "id, name")

boone wrote:

* MySQL caches queries. So if two of the same query are submitted in a short time, the second query result will come out of the cache. Has anyone done any testing/experimentation to see if a complicated join query created by eager loading is really faster than multiple simple queries without eager loading? Eager loading could be faster for the first query, but might not be advantageous if other pages would execute the same simple queries, but a completely different eager loading-style query.

I've done some minor testing. Usually the biggest benefit of eager loading is through a belongs_to association. This is because many models will likely share the same parent. See the railscasts episode on eager loading for some details.

A time where you would not want eager loading is when you only perform a count on child objects. See the Counter Cache Column episode for details.

boone wrote:

I was thinking it would be cool if Rails had some sort of built-in optimizer which would examine controller/model/view code to see which data columns are being used and only query the DB for those columns.

In Rails 2.0 there is some minor optimization through query caching, so if you do two queries which are similar it won't bother hitting the database. However it doesn't do any checking to see which columns are used. That would be cool but also near impossible because of the dynamic nature of Ruby.

boone wrote:

I'm just thinking out loud. My Rails app is too new for me to worry about much optimization, but I'm starting to look where I might be hitting the DB too hard.

Right, it's best to wait and optimize when the application gets closer to deployment. I recommend doing some stress testing in an environment similar to production. This will give you a good idea of performance and where the bottlenecks are.

Railscasts - Free Ruby on Rails Screencasts

Re: Query Performance

There is actually a gem to allow you to do :select with :include, available here.

Alex

Re: Query Performance

Alex,
I am getting close to wrapping up a large multi-module web application and thought I would finally figure out how I'm going to allow my users' the ability to search (and report).
I presumed I would build a SQL method in my table model.rb and somehow pass the fields the user qualifies into this. Then I thought the results of the query would pass back into the index action form.
I hope I don't sound too 'spotty' here but thought maybe you might be able to throw me an example to all this.
I saw your recommendation above..and wondered if this is 'on point' for what I need?
Thank you,
Kathleen
KathysKode@gmail.com

Re: Query Performance

Alex,
I am getting close to wrapping up a large multi-module web application and thought I would finally figure out how I'm going to allow my users' the ability to search (and report).
I presumed I would build a SQL method in my table model.rb and somehow pass the fields the user qualifies into this. Then I thought the results of the query would pass back into the index action form.
I hope I don't sound too 'spotty' here but thought maybe you might be able to throw me an example to all this.
I saw your recommendation above..and wondered if this is 'on point' for what I need?
Thank you,
Kathleen
KathysKode@gmail.com

Re: Query Performance

Hi Kathy,

Are you asking about :select and :include specifically, or do you mean how to run the search generally?

If the former, you can do it like this:

User.find(params[:user_id], :include => "friends", :select => "first_name, last_name, username, friends.name"

If the latter, a simple search is pretty easy to do, in Rails. If you just want to find A = B, X = Y, you could try something like:
# View

<%= text_field :search, :first_name %>
<%= text_field :search, :last_name %>
<%= text_field :search, :username %>
<%= submit_tag "Search" %>

# Controller, or as the code is quite length, the Model

# Get rid of fields the user hasn't entered
search_conditions = params[:search].delete_if {|k, v| v.empty?}

# Then search for them
User.find(:all, :conditions => search_conditions)


A more complicated example, where you want to match the username exactly, but want to find first names and last names that are LIKE their database values:
# Controller

search_conditions = params[:search].delete_if {|k, v| v.empty?}

search_fields = search_conditions.keys.collect do |k|
  case k
  when "first_name", "last_name"
    return "#{k} LIKE %?%"
  when "username"
    return "#{k} = ?"
  end
end

User.find(:all, :conditions => [search_fields(" AND "), *search_conditions.values])


Sorry if I haven't answered your question, but I hope I've given you some kind of starting point wink.

Alex

Last edited by alexpt (2007-07-27 06:45:56)