10

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.

RooSoft
  • 1,481
  • 2
  • 17
  • 32
  • 1
    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. – Clockwork-Muse May 03 '12 at 15:46
  • 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. – RooSoft May 03 '12 at 16:35

3 Answers3

5

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.

RooSoft
  • 1,481
  • 2
  • 17
  • 32
  • 1
    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. – Clockwork-Muse May 03 '12 at 17:45
  • 2
    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). – Jeremy Evans May 03 '12 at 18:58
  • 1
    For the mysql2 driver, you have to use { flags: ::Mysql2::Client::MULTI_STATEMENTS } as option when connecting – tothemario Oct 29 '14 at 03:16
3

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]])
Chris Kolodin
  • 1,685
  • 1
  • 11
  • 3
-2

Dataset#update doesn't work for you?

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

flyer88
  • 1,073
  • 3
  • 15
  • 33