Rails - Apr 2006 - Fast way of finding new position for model object?

I have a model object that acts as a list. The position of each object
is determined by two factors: number of votes ascending and age of
object descending. When a user adds or deletes a vote I need to
quickly update the associated model object''s position. Right now this
is done with the following method:

  def update_position
    position = nil

    Bug.find(:all, :order => "votes_count DESC, id
ASC").each_with_index{ |bug,i|
      if bug.id == self.id
        position = i
        break
      end
    }

    self.insert_at(position)
  end

Basically, find all of the bugs in the order in which they''re wanted
and then iterate through each until the new position of this bug is
found. When it''s found, save the position, break out of the loop and
insert the current model into the correct position.

Unfortunately, this method is rather slow. I broke the
Bug.find().each_with_index() line into two separate lines so that I
could easily benchmark the method and see what was sucking up all of
my time. Here are the results:

                     user     system      total        real
find_all         1.250000   0.030000   1.280000 (  1.308910)
each_with_index  0.070000   0.000000   0.070000 (  0.076316)

The find_all can take anywhere from 1.1 - 2.3 seconds on a table with
2000 rows, in my experiments.

Is there a more efficient way to determine the new position of a row?
I had hoped there was some magical sql query I could use like `SELECT
POSITION(*) FROM bugs WHERE id = #{bug.id}` that would spit out
correct position in one quick call. Or that I could find the rows
around a specific record when they''re sorted in the order I need.
Those both seem to be pipe dreams, however.

Anyone have any suggestions as to how I can quickly find the new
position of an object in a list, with my given sorting criteria?

--
Ryan

can i ask why you need to treat this as a list?  what are you using position
for?



On 4/3/06, Ryan Nielsen <rpnielsen@gmail.com>
wrote:
>
> I have a model object that acts as a list. The position of each object
> is determined by two factors: number of votes ascending and age of
> object descending. When a user adds or deletes a vote I need to
> quickly update the associated model object''s position. Right now
this
> is done with the following method:
>
>   def update_position
>     position = nil
>
>     Bug.find(:all, :order => "votes_count DESC, id
> ASC").each_with_index{ |bug,i|
>       if bug.id == self.id
>         position = i
>         break
>       end
>     }
>
>     self.insert_at(position)
>   end
>
> Basically, find all of the bugs in the order in which they''re
wanted
> and then iterate through each until the new position of this bug is
> found. When it''s found, save the position, break out of the loop
and
> insert the current model into the correct position.
>
> Unfortunately, this method is rather slow. I broke the
> Bug.find().each_with_index() line into two separate lines so that I
> could easily benchmark the method and see what was sucking up all of
> my time. Here are the results:
>
>                      user     system      total        real
> find_all         1.250000   0.030000   1.280000 (  1.308910)
> each_with_index  0.070000   0.000000   0.070000 (  0.076316)
>
> The find_all can take anywhere from 1.1 - 2.3 seconds on a table with
> 2000 rows, in my experiments.
>
> Is there a more efficient way to determine the new position of a row?
> I had hoped there was some magical sql query I could use like `SELECT
> POSITION(*) FROM bugs WHERE id = #{bug.id}` that would spit out
> correct position in one quick call. Or that I could find the rows
> around a specific record when they''re sorted in the order I need.
> Those both seem to be pipe dreams, however.
>
> Anyone have any suggestions as to how I can quickly find the new
> position of an object in a list, with my given sorting criteria?
>
> --
> Ryan
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060403/73750abc/attachment.html

The position column is basically a cache of the bug''s ranking. Having
this model act as a list let''s me slice and filter the objects while
still being able to quickly retrieve and display each object''s ranking
in the overall list.

--
Ryan


On 4/3/06, Chris Hall <christopher.k.hall@gmail.com>
wrote:
> can i ask why you need to treat this as a list?  what are you using
position
> for?
>
>
>
>
> On 4/3/06, Ryan Nielsen < rpnielsen@gmail.com> wrote:
> >
> I have a model object that acts as a list. The position of each object
> is determined by two factors: number of votes ascending and age of
> object descending. When a user adds or deletes a vote I need to
> quickly update the associated model object''s position. Right now
this
> is done with the following method:
>
>   def update_position
>     position = nil
>
>     Bug.find(:all, :order => "votes_count DESC, id
> ASC").each_with_index{ |bug,i|
>       if bug.id == self.id
>         position = i
>         break
>       end
>     }
>
>     self.insert_at(position)
>   end
>
> Basically, find all of the bugs in the order in which they''re
wanted
> and then iterate through each until the new position of this bug is
> found. When it''s found, save the position, break out of the loop
and
> insert the current model into the correct position.
>
> Unfortunately, this method is rather slow. I broke the
> Bug.find().each_with_index() line into two separate lines so that I
> could easily benchmark the method and see what was sucking up all of
> my time. Here are the results:
>
>                      user     system      total        real
> find_all         1.250000   0.030000   1.280000 (  1.308910)
> each_with_index  0.070000   0.000000   0.070000 (  0.076316)
>
> The find_all can take anywhere from 1.1 - 2.3 seconds on a table with
> 2000 rows, in my experiments.
>
> Is there a more efficient way to determine the new position of a row?
> I had hoped there was some magical sql query I could use like `SELECT
> POSITION(*) FROM bugs WHERE id = #{bug.id}` that would spit out
> correct position in one quick call. Or that I could find the rows
> around a specific record when they''re sorted in the order I need.
> Those both seem to be pipe dreams, however.
>
> Anyone have any suggestions as to how I can quickly find the new
> position of an object in a list, with my given sorting criteria?
>
> --
> Ryan
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>