 |
Forums |
Admin Discussion Forums: help Start New Thread
By: Neil Smith
 RE: Very slow inserts [ reply ]
2007-07-18 15:20
|
I don't think the problems in Ruby specifically though - it's implementation level. I encountered this today doing large scale bulk inserts of IP mapping data.
According to this article under 'Give it Some Gas'
http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
Wrap your INSERTs in a transaction's BEGIN / COMMIT. Without that, SQLite writes each row to disk as the INSERT is executed. With transations enabled, it only writes to disk once the COMMIT is executed.
If you add a transaction and watch the directory containing your DB**, you'll see a temporary `dbname.journal` file being built as the transaction proceeds (assuming it runs long enough).
** In my case (PHP5 / Apache2 / win32) :
You might find that is created in a known /tmp directory, I've not checked the API to know what influences that location.
|
By: Neil Faiman
RE: Very slow inserts [ reply ]
2007-06-27 01:17
|
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.)
|
By: Neil Faiman
Very slow inserts [ reply ]
2007-06-23 15:23
|
I've made moderate use of SQLite on OS X in the past, but this is the first that I've played with the SQLite3-Ruby gem. So maybe I'm doing something very wrong. The problem is very, very slow INSERTs (about one second per insert!) from SQLite3::Database#execute.
I'm not using Rails, or much of anything else. I have a small Ruby application, which is creating an SQLite3 database on my desktop machine.
I create the database file and a connection to it like this:
File.delete 'abutters.db' if File.exists? 'abutters.db'
db = SQLite3::Database.new 'abutters.db'
Then I create a table:
db.execute_batch <<-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
This all works ok. Then I start adding records to the table, corresponding to entries in an internal data structure:
tax_lots.each do |lot|
db.execute <<-EOS
INSERT INTO Lot VALUES(
NULL,
'#{ lot.number.to_s.sub(%{'}, %{''}) }',
'#{ lot.address.sub(%{'}, %{''}) }',
'#{ lot.owner1.sub(%{'}, %{''}) }',
'#{ lot.owner2.sub(%{'}, %{''}) }',
'#{ lot.addr1.sub(%{'}, %{''}) }',
'#{ lot.addr2.sub(%{'}, %{''}) }',
'#{ lot.addr3.sub(%{'}, %{''}) }'
);
EOS
lot.id = db.last_insert_row_id
printf "%4d : %s\n", lot.id, lot.number.to_s
end
And I get about one insert operation per second.
I'm running on OS X (10.4.10)
SQLite3 V3.3.7
ruby 1.8.6 (2007-03-13 patchlevel 0) [powerpc-darwin8.9.0]
sqlite3-ruby (1.1.0)
Thanks for any advice,
Neil Faiman
|
|
|
