Hi Chris, I agree with Rob here. Handling 350 updates is pretty trivial. Luckily, Rails will optimize updates so that only the fields that change are included in the update statement for each record, which should help a bit.
Performance should ideally be your last task when writing code. And I would hope you have unit tests for this. It will make updating it for performance reasons a lot easier once you decide you actually need it to be lightning fast. If you write your tests correctly, you could implement a burly UPDATE SQL command that will do what you want, or you can write a SQL stored procedure that has the same effect, all while still allowing your tests to pass! Performance testing is also built into Rails' testing framework, so if your results of doing it with pure ActiveRecord updates are too slow, you can implement a more SQL-centric approach and compare the times. - Adam On Wed, Jan 18, 2012 at 12:11 AM, Chris McCann <[email protected]>wrote: > SD Ruby, > > What's the most efficient way to update a large batch of ActiveRecord > model instances where the same fields are not being updated for each > model? > > Here's the setup: > > I've got a database of members (about 13,000) and a CSV file that has > contact information (3 phone numbers and 3 email addresses) for a > subset of the members (say, 350). Each row of the CSV file may have > between 0 and 6 of the contact fields filled in. > > There also might be data in those same fields in each database entry > for the same member. What I'm trying to do is update any field in the > database that's empty with the corresponding attribute in the CSV file > for that member that isn't blank. > > The brute-force approach is to just loop through all 350 members in > the CSV file (which contains the primary key of the associated > database table) and update the attributes with the non-null CSV data. > Each SQL update statement will be different since there are 6 fields > that could potentially be updated. This seems painfully inefficient > to me. > > An ActiveRecord udpate_all call won't work here since each update > statement has to be uniquely constructed. > > Can anyone suggest a better approach? > > Cheers, > > Chris > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
