Forum: Ruby on Rails [newbie] Redundant SELECTs?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Edbd5f1c2f535b14165ae883fa7c3f37?d=identicon&s=25 Jens Alfke (Guest)
on 2006-01-16 17:46
(Received via mailing list)
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 Alfke

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
 > 0) 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
This topic is locked and can not be replied to.