| Message: 24304 |
 |
BY: Neil Faiman (neilfaiman)
DATE: 2007-06-27 01:17
SUBJECT: RE: Very slow inserts I've studied this some more. The short story is that there seems to be a factor of 10 - 60x overhead for doing SQLite3 inserts through Ruby.
I created a file, test.commands, with 120 lines like the following:
INSERT INTO Lot VALUES(NULL, 'A-47-2', '54 STAGECOACH ROAD', 'KLOSE REV TRUST 4/21/95, J B', '', 'JACQUELINE B KLOSE, TRUSTEE', '54 STAGECOACH ROAD', 'WILTON, NH 03086');
INSERT INTO Lot VALUES(NULL, 'D-26', '109 HOLT ROAD', 'COX, MICHAEL D', '', '109 HOLT ROAD', 'PO BOX 543', 'WILTON, NH 03086');
INSERT INTO Lot VALUES(NULL, 'D-42-2', '80 HOLT ROAD', 'MARKAVERICH, JENNIFER L', 'CASSIDY, BRIAN J', '80 HOLT ROAD', '', 'WILTON, NH 03086');
INSERT INTO Lot VALUES(NULL, 'K-107', '87 MAPLE STREET', 'BRANCHI JR, JOANNE C & PAUL', '', '87 MAPLE STREET', '', 'WILTON, NH 03086');
Then I ran the following script:
require 'rubygems'
require 'sqlite3'
require 'benchmark'
def make_db
File.delete 'abutters.db' if File.exists? 'abutters.db'
db = SQLite3::Database.new 'abutters.db'
db.execute <<-EOS
CREATE TABLE Lot (
lotID integer unique not null primary key,
number varchar(20) unique not null,
address varchar(40),
owner1 varchar(20),
owner2 varchar(20),
ownerAddr1 varchar(40),
ownerAddr2 varchar(40),
ownerAddr3 varchar(40)
);
CREATE UNIQUE INDEX Lot_number on Lot (number);
EOS
return db
end
Benchmark.bm do |x|
db = make_db
db.close
x.report { system 'sqlite3 abutters.db < test.commands' }
cmds = File.readlines 'test.commands'
db = make_db
db.close
x.report { cmds.each { |cmd| system %{sqlite3 abutters.db "#{cmd}"} } }
db = make_db
x.report { cmds.each { |cmd| db.execute cmd } }
db.close
db = make_db
x.report { db.execute_batch cmds.join("\n") }
end
Doing the 'system' call to tell sqlite3 to read the entire file at once only took 0.4 seconds elapsed time. Doing 120 'system' calls, doing one insert each, took 2.5 seconds. But doing the inserts through an Sqlite3::Database object -- either as 120 individual execute calls, or one execute_batch call whose argument was a string containing all the insert commands -- took 25 seconds elapsed time!!
So something in SQLite3::Database#execute is imposing huge amounts of elapsed time overhead. (The problem is all in the elapsed time. The CPU time is .16 seconds for the single system call, 1 second for either execute or execute_batch, and 1.6 seconds for the 120 system calls.) | |
Start New Thread
Very slow inserts