Rails Performance Diary

Although this blog post is titled Rails Performance Diary, it actually has more to do with SQL. For many websites, much of the work is being done by the database server. Such is the case for a simple dog site that I am working on. Having done quite a few websites using Ruby on Rails, I was surprised to find this simple site giving me some major performance problems. Pages were loading slow and sometimes timing out and there was no traffic to the site, other than the developers.

Fortunately, the Rails log outputs all of the queries by default, which is always the first place that I look. And here's what I found -- 2 queries:

1) SELECT * FROM breeds

2) SELECT breeds.id AS t0r0, breeds.name AS t0r1, breeds.alias AS t0r2, breeds.lookup AS t0r3, breeds.info AS t0r4, breeds.line_breaks AS t0r5, breeds.has_pics AS t0r6, breeds.has_videos AS t0r7, pictures.id AS t1r0, pictures.breed_id AS t1r1, pictures.original AS t1r2, pictures.large AS t1r3, pictures.medium AS t1r4, pictures.thumbnail AS t1r5, pictures.caption AS t1r6, pictures.email AS t1r7, pictures.notes AS t1r8, pictures.disabled AS t1r9, pictures.order_field AS t1r10, pictures.created_at AS t1r11, pictures.filename AS t1r12, pictures.breed_lookup AS t1r13, videos.id AS t2r0, videos.breed_id AS t2r1, videos.title AS t2r2, videos.description AS t2r3, videos.code AS t2r4, videos.created_at AS t2r5, videos.orig_url AS t2r6, videos.embed_src AS t2r7, videos.thumbnail AS t2r8, videos.time_length AS t2r9, videos.length_seconds AS t2r10, videos.youtube_id AS t2r11, videos.lookup AS t2r12 FROM breeds LEFT OUTER JOIN pictures ON pictures.breedid = breeds.id LEFT OUTER JOIN videos ON videos.breedid = breeds.id WHERE (breeds.lookup = 'airedaleterrier' )

The next step was to run each of these queries in the MySQL Query Browser, so I could see how long it took the queries to run. The first query took 13 seconds to execute, so I felt that I had found the problem. Much to my chagrin, however, I found the second query to take over a minute!!! Clearly, neither one of these queries are acceptable, even with caching.

The First Query : SELECT * FROM breeds

This query was being used simply to get a list of the dog breeds so that I could link to each of their info pages, yet the query was returning the entire record, which includes a long "description" column containing a lot of text. This query was generated by ActiveRecord from the following code: @breeds = Breed.find(:all) To solve the problem, let's force a specific query that doesn't include the description field: @breeds = Breed.find_by_sql("SELECT id, name FROM breeds") Immediately, the query dropped from 13 seconds to a fraction of a second.

Second Query: SELECT breeds.id AS t0_r0, breed....

Here was the stumper. This vast expanse of SQL was generated from the following Ruby: @breeds = Breed.find_by_lookup(lookup, :include => [pictures, videos]) Typically, including relations speeds things up because it allows an SQL join to be used rather than multiple SQL queries. But, in this case, the join only reduces 3 queries to 1 query and incurs a large overhead to do so. Join tables multiply the number of records for each table joined, so in this case the number of results was extremely large. Now, I'm sure a veteran database wizard could enlighten you with SQL magic to solve this problem. But, not being such a wizard, I simply broke the query into 3 separate queries as shown below: @breeds = Breed.find_by_lookup(lookup) @pictures = Picture.find(:all, :conditions => "breed_id = #{@breed.id}") @videos = Video.find(:all, :conditions => "breed_id = #{@breed.id}") Voila! Each of the 3 queries executes in less than a second!

Note: although it's not mentioned above, I also added appropriate indexes to each of the database tables to speed up the queries. These did not help much, but are always a good practice.