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.

Spleenwort

Yes, but it depends on what exactly you're attempting to do, and what RDBMS you're running on. Please provide more information so we can advise you better. – Nubianubian

I'm using PostgreSQL, and I want Sequel to issue a single query for multiple updates, as I'm able to do in plain SQL. – Spleenwort

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.

Spleenwort

This solution is only faster than 'multiple updates' in that you're only making one actual connection/trip to the database for the entire set of updates, as opposed to one-per. Please note that some frameworks perform this sort of behaviour automatically - they'll hold the updates in memory until the transaction is committed. Also, you may be able to receive still greater benefits, if multiple statements can be re-written to one statement. – Nubianubian

That may work in your case, but not all Sequel adapters support multiple queries in Database#run. Sequel actually does not provide an adapter-independent method that accepts multiple queries in a single string (some adapters will work with such a string, others will not). – Milwaukee

For the mysql2 driver, you have to use { flags: ::Mysql2::Client::MULTI_STATEMENTS } as option when connecting – Zoraidazorana

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]])

Exarate

Answer 3

Dataset#update doesn't work for you?

http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-update

Waver