I was the original author of the OUTPUT code and the performance improvement is important for my use case.
What's the compatibility issue you are encountering? Perhaps we can come up with a more targeted change. On Tue, Nov 2, 2010 at 1:58 PM, Gary Doades <[email protected]> wrote: > As a follow up to my issue 311 report I've produced a crude benchmark > for insert on SQL Server 2008 both with and without the OUTPUT clause. > The code below inserts 1000 records in a transaction and records the > time with and without the OUTPUT clause. > > Using jruby 1.5.3 on Windows 7 > > Benchmark.measure output: > Using OUTPUT: > 2.356000 0.000000 2.356000 ( 2.356000) > Using SCOPE_IDENTITY: > 3.510000 0.000000 3.510000 ( 3.510000) > > The difference is less marked when going over the network rather then > using localhost. > > Using OUTPUT: > 2.940000 0.000000 2.940000 ( 2.940000) > Using SCOPE_IDENTITY: > 3.720000 0.000000 3.720000 ( 3.720000) > > Using the output clause is clearly faster, but not astonishingly so. > In a real world program it's likely to make only a small difference, > unless your program does do many times more inserts than other > operations. Personally I would like to make the use of the OUTPUT > clause an option and not the default thereby making the default > compatible with virtually any database. Turning on the OUTPUT clause > could be an option if you have one of those programs where the > difference would be significant. > > Regards, > Gary. > > TEST CODE: > require 'rubygems' > require 'sequel' > require 'logger' > require 'benchmark' > > CONN_TEMPLATE = "jdbc:sqlserver://%s;databaseName=%s;user=sa;password= > %s" > > defaultDB = CONN_TEMPLATE % [ 'localhost','testdb','testing'] > > DB = Sequel.connect(defaultDB) > > DB.create_table :tests do > primary_key :id > String :s1, :size => 50 > integer :i1 > String :s2, :size => 50 > integer :i2 > String :s3, :size => 50 > integer :i3 > String :s4, :size => 50 > integer :i4 > String :s5, :size => 50 > integer :i5 > String :s6, :size => 50 > integer :i6 > end > > class Test < Sequel::Model > end > > s = '12345678901234567890123456789012345678901234567890' > > #DB.loggers << Logger.new($stdout) > > puts 'Using OUTPUT:' > puts Benchmark.measure {DB.transaction do > 1000.times do > t = Test.new(:s1 => s, :i1 => 1,:s2 => s, :i2 => 1,:s3 => s, :i3 => > 1,:s4 => s, :i4 => 1,:s5 => s, :i5 => 1,:s6 => s, :i6 => 1) > t.save > end > end} > > DB.run('truncate table tests') > > Test.dataset.disable_insert_output! > > puts 'Using SCOPE_IDENTITY:' > puts Benchmark.measure {DB.transaction do > 1000.times do > t = Test.new(:s1 => s, :i1 => 1,:s2 => s, :i2 => 1,:s3 => s, :i3 => > 1,:s4 => s, :i4 => 1,:s5 => s, :i5 => 1,:s6 => s, :i6 => 1) > t.save > end > end} > > DB.drop_table :tests > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sequel-talk?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
