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
>
>