Rails - Jan 2006 - can I update many rows with one call to the database?

Hello,

How would I go about updating 7000 or so rows of my database with
unique values? (In other words, I''m not setting them all to NULL or
incrementing or something... each row gets a new, special value.)

But I can''t seem to get my rails app to do this in less than 7000
connections to the database! I''ve looked very hard (3 horus!), and
can''t find anything about buffering calls to the database, to be done
all at once (much to my surprise).

I tried:

ActiveRecord::Base.connection.execute ''UPDATE things SET bar = 2, baz
= 8 WHERE id = 1; UPDATE things SET bar = 19, baz = 47 WHERE id = 2''
(but much longer).

But it doesn''t work... it doesn''t like the semicolon.

Is there really no way to do this? The problem I''m trying to solve is
that sometimes the acts_as_nested_set data needs updating completely,
but calling add_child on each item individually makes O(n*n)
connections. I can reduce it to O(n) connections, and it went from
taking 25 minutes to only 2 minutes... but I really need to get this
down to a few seconds.

Thanks so much,
Katy

On 1/29/06, Katy Pine <katy@pine.fm> wrote:
> Hello,
>
> How would I go about updating 7000 or so rows of my database with
> unique values? (In other words, I''m not setting them all to NULL
or
> incrementing or something... each row gets a new, special value.)
>
> But I can''t seem to get my rails app to do this in less than 7000
> connections to the database! I''ve looked very hard (3 horus!), and
> can''t find anything about buffering calls to the database, to be
done
> all at once (much to my surprise).
>
> I tried:
>
> ActiveRecord::Base.connection.execute ''UPDATE things SET bar = 2,
baz
> = 8 WHERE id = 1; UPDATE things SET bar = 19, baz = 47 WHERE id =
2''
> (but much longer).
>
> But it doesn''t work... it doesn''t like the semicolon.
>
> Is there really no way to do this? The problem I''m trying to solve
is
> that sometimes the acts_as_nested_set data needs updating completely,
> but calling add_child on each item individually makes O(n*n)
> connections. I can reduce it to O(n) connections, and it went from
> taking 25 minutes to only 2 minutes... but I really need to get this
> down to a few seconds.
>
What database system are you running? Most of them do query caching
(which, I assume, is why Rails hides the ''parse'' method that
DBI
provides.)

How long does this take?
input_data = [ [2,8,1], [19,47,2] ] #etc, etc, for your data.
conn = Things.connection
input_data.each do |inputs|
  conn.execute "update things set bar = #{inputs[0]}, baz #{inputs[1]}
where id = #{inputs[2]}"
end

If that takes too long, you might need to directly use the DBI driver
itself, which will let you turn the update into a prepared statement,
and loop over it by binding your values to it.

I''ve run into this sort of thing myself; hopefully someone with more
ActiveRecord know-how will chime in and let me know I''ve been wasting
my time. :)

--Wilson.

Wilson,

Thanks for your thoughts; my husband found the answer -- if you are
interested, here is the link to the thread on the Textdrive forum.

http://forum.textdrive.com/viewtopic.php?pid=74997#p74997

thanks again!
Katy

On 1/30/06, Wilson Bilkovich <wilsonb@gmail.com>
wrote:
> On 1/29/06, Katy Pine <katy@pine.fm> wrote:
> > Hello,
> >
> > How would I go about updating 7000 or so rows of my database with
> > unique values? (In other words, I''m not setting them all to
NULL or
> > incrementing or something... each row gets a new, special value.)
> >
> > But I can''t seem to get my rails app to do this in less than
7000
> > connections to the database! I''ve looked very hard (3
horus!), and
> > can''t find anything about buffering calls to the database, to
be done
> > all at once (much to my surprise).
> >
> > I tried:
> >
> > ActiveRecord::Base.connection.execute ''UPDATE things SET bar
= 2, baz
> > = 8 WHERE id = 1; UPDATE things SET bar = 19, baz = 47 WHERE id =
2''
> > (but much longer).
> >
> > But it doesn''t work... it doesn''t like the
semicolon.
> >
> > Is there really no way to do this? The problem I''m trying to
solve is
> > that sometimes the acts_as_nested_set data needs updating completely,
> > but calling add_child on each item individually makes O(n*n)
> > connections. I can reduce it to O(n) connections, and it went from
> > taking 25 minutes to only 2 minutes... but I really need to get this
> > down to a few seconds.
> >
>
> What database system are you running? Most of them do query caching
> (which, I assume, is why Rails hides the ''parse'' method
that DBI
> provides.)
>
> How long does this take?
> input_data = [ [2,8,1], [19,47,2] ] #etc, etc, for your data.
> conn = Things.connection
> input_data.each do |inputs|
>   conn.execute "update things set bar = #{inputs[0]}, baz >
#{inputs[1]} where id = #{inputs[2]}"
> end
>
> If that takes too long, you might need to directly use the DBI driver
> itself, which will let you turn the update into a prepared statement,
> and loop over it by binding your values to it.
>
> I''ve run into this sort of thing myself; hopefully someone with
more
> ActiveRecord know-how will chime in and let me know I''ve been
wasting
> my time. :)
>
> --Wilson.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>