Is it possible to batch updates in Sequel?

Question

Is it possible to make many updates in a single call using Sequel?

For instance, making about 200 updates could take several minutes on my server, but if I forge a single SQL query it runs in a matter of seconds. I wonder if Sequel could be used to forge that SQL query or even better, do the whole operation one shot by itself.

Answer 1

The solution I've come across involves the update_sql method. Instead of doing the operation itself, it output raw SQL queries. To batch multiple updates, just join these with ; in between, call the run method with the resulting string and you're all set.

The batching solution is WAY faster than multiple updates.

Answer 2

You can use Datset#import http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-import "Inserts multiple records into the associated table. This method can be used to efficiently insert a large number of records into a table in a single query if the database supports it. Inserts are automatically wrapped in a transaction."

here's an example of how to use it:

DB = Sequel.connect(...)
DB[:movies].import([:id, :director, :title, :year], [[1, "Orson Welles", "Citizen Kane", 1941],[2, "Robert Wiene", "Cabinet of Dr. Caligari, The", 1920]])