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.
