Forum: Ruby on Rails can I update many rows with one call to the database?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
8fc42b4504648d15f47cef1cc3ef556e?d=identicon&s=25 Katy Pine (Guest)
on 2006-01-30 01:09
(Received via mailing list)
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
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-01-30 17:01
(Received via mailing list)
On 1/29/06, Katy Pine <katy@pine.fm> wrote:
>
> 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.
8fc42b4504648d15f47cef1cc3ef556e?d=identicon&s=25 Katy Pine (Guest)
on 2006-01-30 22:44
(Received via mailing list)
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
This topic is locked and can not be replied to.