Hello,
Just noticed that move_to_child_of was running pretty slow (around 20
seconds) on a table with about 2.2 million rows, but was only updating
about 13000 records.
I''ve looked at the SQL move_to produces, and it seems to be doing a
full table scan in it''s update, so I made this change to limit it to
only the rows that would be updated (sorry for no patch, my copy of
better_nested_set.rb is pretty messy at the moment from debugging):
update_scope_condition = " AND ((#{left_col_name} BETWEEN #{a} AND
#{b}) OR (#{left_col_name} BETWEEN #{c} AND #{d}) OR
(#{self.class.primary_key} = #{self.id}))"
base_set_class.update_all([existing SQL], new_scope_condition +
update_scope_condition)
The query now runs in under 1 second, and seems to produce the same result.
I''ll do some more testing & provide a patch if there is interest.
--
Lindsay Evans
http://linz.id.au/
oops, that second line should read: base_set_class.update_all([existing SQL], scope_condition + update_scope_condition I need more coffee :p -- Lindsay Evans http://lindsayevans.com/
Hi Lindsay, Thanks for passing that along. At this point it looks like you''re the only one working with significantly large tables, but I''m sure others will benefit from what you''ve discovered. I''ve opened a ticket on Trac for this to make sure it doesn''t get buried. I''m curious: since you''ve worked with large tables, what indexing setup do you find gives the best performance with nested sets? A single index on (lft, rgt), or something else? Krishna On 3/28/07, Lindsay Evans <lindsaye at gmail.com> wrote:> oops, that second line should read: > > base_set_class.update_all([existing SQL], scope_condition + > update_scope_condition > > I need more coffee :p > > -- > Lindsay Evans > http://lindsayevans.com/ > _______________________________________________ > Betternestedset-talk mailing list > Betternestedset-talk at rubyforge.org > http://rubyforge.org/mailman/listinfo/betternestedset-talk >
Hi Krishna, I''ve mostly been using children and all_children, so I''m only indexing lft & parent_id at the moment, just did a quick test with an index on (lft, rgt) and the speed seems comparable to just using lft.>From a quick look at the code, it looks like (lft, rgt) would be bestfor things like self_and_ancestors and the move_to_* methods I''m no expert on indexes, but I imagine (lft, rgt) would slow down updates on lft & rgt a bit, so there might not be any performance gains for move_to_*. One of these days I''ll get motivated & test all these other methods out to see how they go on ridiculously large tables :) (btw, I''m a he, not a her - heaps of people make that mistake :p) On 4/5/07, Krishna Dole <dontfall at gmail.com> wrote:> Hi Lindsay, > > Thanks for passing that along. At this point it looks like you''re the > only one working with significantly large tables, but I''m sure others > will benefit from what you''ve discovered. I''ve opened a ticket on Trac > for this to make sure it doesn''t get buried. > > I''m curious: since you''ve worked with large tables, what indexing > setup do you find gives the best performance with nested sets? A > single index on (lft, rgt), or something else?-- Lindsay Evans http://lindsayevans.com/