Eager Find by SQL Pagination in Rails

Sometimes there is just no way around it, you have to write your own customized SQL queries for your app. I recently ran into this case while working on a fairly obscure view for my project. I was trying to do a multi-way join on 4 models (reasonably sized ~1000 to ~3000 records) to extract the information I needed and Rails query builder just wasn't working out. The performance was horrible, it took on average about 6 seconds to run the query through the database. After an hour of wrestling with Rails find method and applying tweaks to my MySQL config I got it down to 3.5~4 seconds, but that wont do. It's good enough for a one-off query but this was an important part of my app, it had to be fast.

So, I wrote my own query and voila, a quick test in SQL console showed 0.56s - almost an order of magnitude lower than the Rails produced equivalent. Great, I had the result that I wanted, so I put the find_by_sql query in my controller and hit refresh. And then it set in, I didn't account for pagination! (Or lack of thereof). Doh.

I was using paginating_find and my window helper function I wrote about in "Faster Pagination in Rails" across my entire app and the prospect of writing custom pagination code for my find_by_sql query didn't look all that exciting. So, after some head scratching I came up with a quick hack (no other way to describe it, really) to give me all the features I needed to use my helper with a find_by_sql query. The magic happens in a single ClassMethod I added to paginating_find:

> paginating_sql_find.rb 

# Add this to the ClassMethods of paginating_find plugin
 
def paginating_sql_find(count_query, query, options)
      count_query = sanitize_sql(count_query)
      query = sanitize_sql(query)
 
      # execute the count query - need to know how many records we're looking at   
      count = count_by_sql(count_query)
 
      PagingEnumerator.new(options[:page_size], count, false, options[:current], 1) do |page|
          # calculate the right offset values for current page and page_size
          offset = (options[:current].to_i - 1) * options[:page_size]
          limit = options[:page_size]
 
          # run the actual query - Note: do not include LIMIT statement in your query          
          find_by_sql(query + " LIMIT #{offset},#{limit}")
      end
end
 

> find_by_sql_controller.rb 

 
# In your contoller, write your queries...
#  1) You need a separate count query for pagination
#	   - This query does not have to include all the same tables, use joins only relevant to your results 
#		 (same set should be retrieved from the database - avoid tables that provide additional information to 
#		  keep the cost of the query down)  
#  2) Write your actual select query 
 
# In example below, I dont include table1 in the count query as it is irrelevant for the ordering and size of the retrieved set
count_query = "SELECT count(widgets.id) from widgets, table2 WHERE ... join conditions ... ORDER BY widgets.created_at DESC"
query = "SELECT widgets.* from table1, table2, widgets WHERE ... join conditions ... ORDER BY widgets.created_at DESC"
 
# Now we can retrive our results
@widgets = Widget.paginating_sql_find(count_query, query, {:page_size => 10, :current => params[:page]})
 
#voila, your default pagination code (for paginating_find) should work now.
 

You need to provide two queries, a count query to find how many records there are in total, and your actual query for the model plus your page_size and current_page parameters. Warning - This misses a lot of extra features built into paginating_find, you can probably massage my code to regain some of the functionality but I'm releasing it as it is. Now you can simply do Model.paginating_sql_find(params) and in return you will get an enumerator which you can in turn pass to the helper and print your pagination partial. Wohoo!

Note: Faster Pagination in Rails has my window helper function to go with paginating_find

Now, let's go for some bonus points. One nice thing about the default find method in Rails is the ability to do nested includes. In my case it was a difference between 6 queries to render a page vs 38, it was something worth fighting for. So, I needed to massage the includes into my original find_by_sql query, talk about a fun way to spend your evening!

Here is how it works in Rails - when the query is built with includes the primary model gets 't0_rM' name for each field, where t0_r0 is the primary key and M represents the (M-1)th field. Same process is repeated for each include, except t0 becomes t1, t2, etc. Knowing this, we can write a Rails equivalent of an include query ourselves. Or, if you're two steps ahead of me, just write your find query with includes in Rails, run it, check your development.log and copy the select part of the query, it'll save you a lot of time and headaches, trust me. (Note: you don't have to include all tN_rM fields, just make sure you keep the correct order). Awesome, throw the query into your controller, reload.. and it doesn't work, the fields are selected but not recognized by Rails. Oi! Now, for the grand finale we pull out the rabbit, err.. ruby file to save the day: eager_custom_load.rb

Load it into your lib directory, add a require in your environment.rb and now we can do the following:

> paginating_sql_find-mod.rb 

# Load eager_custom_load.rb into your lib directory, and add a require to your environemnt.rb
# Then, in the original paginating_sql_find method, do the following:
 
- find_by_sql(query + " LIMIT #{limit} OFFSET #{offset}")
+ find_by_sql(query + " LIMIT #{limit} OFFSET #{offset}", {:include => options[:include]})
 
# we've simply forcing Rails to recognize the include options provided with the custom SQL query.
 

> find_by_sql_controller_eager.rb 

 
# In your contoller, write your queries...
#  1) You need a separate count query for pagination
#      - This query does not have to include all the same tables, use joins only relevant to your results
#        (same set should be retrieved from the database - avoid tables that provide additional information to
#         keep the cost of the query down)
#  2) Write your actual select query
 
# Note: I'm sitll using the basic count query, no point in adding includes to it!
count_query = "SELECT count(widgets.id) from widgets, table2 WHERE ... join conditions ... ORDER BY widgets.created_at DESC"
 
# Here comes the really long select query with includes, equivalent of adding :include => :ratings in your find method
query = "SELECT widgets.`id` AS t0_r0, widgets.`title` AS t0_r1, widgets.`description` AS t0_r2, widgets.`created_at` AS t0_r3, ratings.`id` AS t1_r0, ratings.`rating` AS t1_r1, ratings.`rateable_id` AS t1_r4, ratings.`user_id` AS t1_r5 FROM widgets LEFT OUTER JOIN ratings ON ratings.rateable_id = widgets.id AND ratings.rateable_type = 'Widget' WHERE ... conditions ... ORDER BY ... DESC"
 
# Retrieve the results with eager loading:
 @widgets = Widget.paginating_sql_find(count_query, query, {:page_size => 10, :current => params[:page], :include => [:ratings]})
 
# There you have it! paginated, eager loaded, find_by_sql query!
 

Magic! The 'mod' allows us to add an include hash to find_by_sql to make it recognize our extra selected fields as such. Refresh your controller view again, and voila! Eager loading on a custom find_by_sql with full pagination support. Was it worth it? You be the judge:

Before: Completed in 8.23000 (0 reqs/sec) | Rendering: 0.53100 (19%) | DB: 6.13500 (75%) | 200 OK [http://localhost/]
After: Completed in 1.01500 (0 reqs/sec) | Rendering: 0.53100 (52%) | DB: 0.21900 (21%) | 200 OK [http://localhost/]

My DB prof. would be proud!

About this entry

Published:
22.09.06 / 10pm
Category:
Ruby on Rails
Tagged:
, , , , ,


Related Posts


Subscribe to igvita via RSS
Subscribe to igvita via Email