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