Skip to content

Includes & Eager Loading

June 8, 2009

I’m figuring out something in my rails app.  I’m figuring out which one’s the worst:

Eager loading 100 rows of data you don’t actually need at the moment

or

10 queries giving 10 result set at once?

Here’s the code to explain it.


@event = Event.find(params[:event_id])

@event_attendees = @event.attendees.paginate(:page => params[:page], :per_page => 10)

In the view, for each attendees, it’s calling a purchase record. Since I’m using will_paginate and setting 10 records per page, for every 10 record of attendees, there’s 10 queries for a purchase record. Here’s the following SQL that will be generated:

SELECT * FROM `attendees` WHERE (`attendees`.event_id = 4)

SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 1) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 2) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 3) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 4) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 5) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 6) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 7) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 8) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 9) LIMIT 1
SELECT * FROM `purchases` WHERE (purchasable_type = 'Attendee' and purchasable_id = 10) LIMIT 1

Clearly you can that see the above code creates a n + 1 problem.

So I tried to modified it using eager loading like the following:

@event_attendees = @event.attendees.paginate(:include => :purchase, :page => params[:page], :per_page => 10)

Here’s the following SQL query generated:

SELECT * FROM `attendees` WHERE (`attendees`.event_id = 4)
SELECT `purchases`.* FROM `purchases` WHERE (`purchases`.`purchasable_id` IN (1,2,3,4..100) and `purchases`.`purchasable_type` = 'Attendee')
 # query is shorten for brevity

You can see that it’s using 1 query that fetches 100 rows of data. At first I thought that it will limit the eager loading with my will_paginate per page settings. But clearly for every page that I’m viewing, it’s calling that query hence returning 100 rows of data for each page I’m viewing.

At the moment I’m using the query without eager loading. Simply because I’m concern the app is fetching 100 rows that I don’t need at once. I have no idea how to limit an eager loading association base on will _paginate per_page. Or probably I could eager load once and keep in cache. Or does rails does that automatically?

Will investigate though. Oh well, back to work!

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: