Hi – I’m getting started with Rails by rewriting a little web-app I
wrote last year in PHP. (I have a bit of prior Ruby experience, and
have been working with SQLite for about a year.)
My app is up and running, but on looking through the development.log
I noticed that Rails is making a lot of identical SELECT queries
while handling a single request. These are just the simple queries
that load the attributes of an ActiveRecord object. In the transcript
below, the record with id 0 gets fetched eight times during one
request! This will become a terrible performance problem – scaling
as O(n^2) – as the database grows.
The table in question, ‘sections’, uses acts_as_tree. In rendering
each section, the app walks up the tree to find the path to the root,
using the accessor Section#parent. I’m guessing that what’s happening
is that Section#parent always allocates a new Section object (and
fetches its contents from the db), even if a Section with that id has
already been allocated during this query.
Is this true? If so, is there a way to make Rails cache ActiveRecord
objects by id (during a single request, of course)?
–Jens A.
PS: I’m using Rails 1.0 with sqlite3.
Processing StoryController#index (for 69.12.133.193 at 2006-01-15
22:42:06) [GET]
Parameters: {“action”=>“index”, “controller”=>“story”}
Section Load (0.043420) SELECT * FROM sections WHERE (parent_id
= 0) ORDER BY created_at desc
Section Load (0.034256) SELECT * FROM sections WHERE (parent_id
- ORDER BY created_at desc LIMIT 7
Rendering actionlistlayoutfalse within layouts/application
Rendering story/list
SQL (0.004732) PRAGMA table_info(sections)
Section Load (0.026646) SELECT * FROM sections WHERE
(sections.id = 6) LIMIT 1
Section Load (0.020160) SELECT * FROM sections WHERE
(sections.id = 4) LIMIT 1
Section Load (0.092672) SELECT * FROM sections WHERE
(sections.id = 3) LIMIT 1
Section Load (0.059666) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.022138) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.020480) SELECT * FROM users WHERE (users.id = 1)
LIMIT 1
SQL (0.004280) PRAGMA table_info(users)
Rendered story/_section (0.27284)
Section Load (0.024274) SELECT * FROM sections WHERE
(sections.id = 7) LIMIT 1
Section Load (0.024292) SELECT * FROM sections WHERE
(sections.id = 5) LIMIT 1
Section Load (0.023304) SELECT * FROM sections WHERE
(sections.id = 2) LIMIT 1
Section Load (0.023952) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.033721) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.023158) SELECT * FROM users WHERE (users.id = 1)
LIMIT 1
Rendered story/_section (0.16228)
Section Load (0.020262) SELECT * FROM sections WHERE
(sections.id = 5) LIMIT 1
Section Load (0.004865) SELECT * FROM sections WHERE
(sections.id = 2) LIMIT 1
Section Load (0.043344) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.027540) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.030145) SELECT * FROM users WHERE (users.id = 5)
LIMIT 1
Rendered story/_section (0.13487)
Section Load (0.034976) SELECT * FROM sections WHERE
(sections.id = 4) LIMIT 1
Section Load (0.035468) SELECT * FROM sections WHERE
(sections.id = 3) LIMIT 1
Section Load (0.027500) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.018929) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.021662) SELECT * FROM users WHERE (users.id = 5)
LIMIT 1
Rendered story/_section (0.14809)
Section Load (0.035305) SELECT * FROM sections WHERE
(sections.id = 2) LIMIT 1
Section Load (0.030107) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.023888) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.031557) SELECT * FROM users WHERE (users.id = 2)
LIMIT 1
Rendered story/_section (0.12815)
Section Load (0.032956) SELECT * FROM sections WHERE
(sections.id = 3) LIMIT 1
Section Load (0.022948) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.023233) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.022067) SELECT * FROM users WHERE (users.id = 1)
LIMIT 1
Rendered story/_section (0.11056)
Section Load (0.023485) SELECT * FROM sections WHERE
(sections.id = 1) LIMIT 1
Section Load (0.036884) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.032837) SELECT * FROM users WHERE (users.id = 1)
LIMIT 1
Rendered story/_section (0.10125)
Section Load (0.046128) SELECT * FROM sections WHERE
(sections.id = 0) LIMIT 1
User Load (0.026971) SELECT * FROM users WHERE (users.id = 1)
LIMIT 1
Rendered story/_section (0.07784)
Completed in 1.27026 (0 reqs/sec) | Rendering: 0.10756 (8%) | DB:
1.13421 (89%) | 200 OK