Hunter Peress
2007-Aug-15 05:58 UTC
[Betternestedset-talk] Why so many select * when adding child?
Hi, all i am doing is adding a new child: new = Menu.new new.save new.move_to_child_of Menu.find(params[:menu][:id] Yet, there are so many select * ... why? SQL (0.001530 ) BEGIN SQL (0.002496) SELECT max(rgt) AS max_rgt FROM menus WHERE (1 = 1) Menu Create (0.044471) INSERT INTO menus ("updated_at", "lft", "lock_version", "parent_id", "rgt", "created_at") VALUES(''2007-08-14 2 3:28:23.611885'', 9, 0, NULL, 10, ''2007-08-14 23:28:23.611885'') SQL (0.048153) SELECT currval(''menus_id_seq'') SQL (0.032054) COMMIT Menu Load (0.026600) SELECT * FROM menus WHERE (menus."id" = 3) SQL (0.001428) BEGIN Menu Load (0.048451) SELECT * FROM menus WHERE (menus."id" = 5) Menu Load (0.132441) SELECT * FROM menus WHERE (menus."id" = 3) Menu Update (0.026690) UPDATE menus SET lft = CASE WHEN lft BETWEEN 8 AND 8 THEN lft + 2 WHEN lft BETWEEN 9 AND 10 THEN lft + -1 ELSE lft END, rgt = CASE WHEN rgt BETWEEN 8 AND 8 THEN rgt + 2 WHEN rgt BETWEEN 9 AND 10 THEN rgt + -1 ELSE rgt END, parent_id = CASE WHEN id = 5 THEN 3 ELSE parent_id END WHERE (1 = 1) Menu Load (0.002911) SELECT * FROM menus WHERE (menus."id" = 5) Menu Load (0.001854) SELECT * FROM menus WHERE (menus."id" = 3) SQL (0.006460) COMMIT -------------- next part -------------- An HTML attachment was scrubbed... URL: http://rubyforge.org/pipermail/betternestedset-talk/attachments/20070814/6f39e055/attachment.html
Krishna Dole
2007-Aug-15 16:02 UTC
[Betternestedset-talk] Why so many select * when adding child?
Hi Hunter, Starting from the second ''BEGIN'', both the child and parent must be reloaded from the database to ensure that we are concurrency-safe (the lft/rgt values could have been altered by adds/moves/deletes of other objects). After the database is updated, they must be reloaded again, because the update happens only in the db, not the objects. Does that make sense? It is true that selecting ''*'' is unnecessary-- I''m looking at passing :select => "#{left_col_name}, #{right_col_name}, #{parent_col_name}" to #reload, which should give a performance boost to models with lots of columns/data. Krishna On 8/15/07, Hunter Peress <hunterp at gmail.com> wrote:> Hi, all i am doing is adding a new child: > > new = Menu.new > new.save > new.move_to_child_of Menu.find(params[:menu][:id] > > Yet, there are so many select * ... why? > > SQL (0.001530 ) BEGIN > SQL (0.002496) SELECT max(rgt) AS max_rgt FROM menus WHERE (1 = 1) > Menu Create (0.044471) INSERT INTO menus ("updated_at", "lft", > "lock_version", "parent_id", "rgt", "created_at") VALUES(''2007-08-14 2 > 3:28:23.611885'', 9, 0, NULL, 10, ''2007-08-14 23:28:23.611885'') > SQL (0.048153) SELECT currval(''menus_id_seq'') > SQL (0.032054) COMMIT > Menu Load (0.026600) SELECT * FROM menus WHERE (menus."id" = 3) > SQL (0.001428) BEGIN > Menu Load (0.048451) SELECT * FROM menus WHERE (menus."id" = 5) > Menu Load (0.132441) SELECT * FROM menus WHERE (menus."id" = 3) > Menu Update (0.026690) UPDATE menus SET lft = CASE WHEN lft BETWEEN 8 > AND 8 THEN lft + 2 WHEN lft BETWEEN 9 AND 10 THEN lft + -1 ELSE > lft END, rgt = CASE WHEN rgt BETWEEN 8 AND 8 THEN rgt + 2 WHEN rgt BETWEEN > 9 AND 10 THEN rgt + -1 ELSE rgt END, parent_id = CASE WHEN id > = 5 THEN 3 ELSE parent_id END WHERE (1 = 1) > Menu Load (0.002911) SELECT * FROM menus WHERE (menus."id" = 5) > Menu Load (0.001854) SELECT * FROM menus WHERE (menus."id" = 3) > SQL (0.006460) COMMIT > > _______________________________________________ > Betternestedset-talk mailing list > Betternestedset-talk at rubyforge.org > http://rubyforge.org/mailman/listinfo/betternestedset-talk > >