ActiveRecord Performance | 167
Invoking the user method on each Person object will force a separate load from the
database. If the people with IDs 4, 17, 36, and 39 matched the find_all_by_state
method, the following queries would be issued:*
SELECT * FROM people WHERE state = 'IL';
-- returns people with ID in (4, 17, 36, 39)
SELECT * FROM users WHERE person_id = 4;
SELECT * FROM users WHERE person_id = 17;
SELECT * FROM users WHERE person_id = 36;
SELECT * FROM users WHERE person_id = 39;
This is why this problem is called the 1+ N problem. This code issues one query to
find the IDs of the objects to retrieve (or the values of a foreign key, as shown here)
and N queries to retrieve the actual objects. If there were 1,000 objects matching the
query, this method would require 1,001 queries.
This is a very inefficient method of retrieving data. All but the first query return a single
row. Most of the time is wasted in constructing N queries, transmitting them to
the database server, parsing them (Rails does not use prepared statements, so each
statement must be compiled and planned individually), and retrieving the results.
The solution is to use a SQL join for the first and only query:
SELECT * FROM people LEFT JOIN users ON people.id = users.person_id
WHERE people.state = 'IL';
That is much faster as it retrieves all needed data in one SQL statement. Rails makes
this easy: we can use the :include option to User.
Pages:
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259