Advanced Rails - Building Industrial-Strength Web Apps in Record Time

ActiveRecord Performance | 167

Invoking theusermethod on eachPersonobject will force a separate load from the
database. If the people with IDs 4, 17, 36, and 39 matched thefind_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+Nproblem. This code issues one query to
find the IDs of the objects to retrieve (or the values of a foreign key, as shown here)
andNqueries 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 sin-
gle row. Most of the time is wasted in constructingNqueries, 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 toUser.find to create a join:

logins_from_illinois = User.find(:all, :include => :person,

:conditions => "people.state = 'IL'").map(&:login)

Note the pluralization: in the:includeparameter, we use the singular, as it is the
name of the association (aUserhas-one Person). But since the:conditions are
directly injected into the SQL statement, the table name is used (...WHERE people.
state = 'IL').

Indexing

Another area in which you have to be careful of your database is indexing. Improper
indexing is a common problem; indexing is easily overlooked when creating a data-
base, and you often do not see the results until there is a major performance impact
on the application.

Unfortunately, this is an area where Rails does not (and cannot) help you. Indexing is
quite application-specific. Other than primary keys (which are usually automatically
indexed by the database), you must remember to create the necessary indexesyourself.

• The actual queries have been simplified for clarity. Most of the Rails database adapters reflect on the data-
  base’s metadata for information about available tables, columns, and data types.