I've encountered an unusual problem with SQLite performance under
Windows and am hoping someone might have an idea of what might be happening.
We are developing a Delphi application which uses SQLite compiled into
the program. During one round of tests we discovered a section of code
which was executing a number of INSERTS outside of the context of a
transaction. The issue reported to me was that this section of code was
running extremely slowly. When I researched the issue, I could not
replicate the slowdown. After several hours of experimentation, here is
my summary:
We tested the procedure under performance analysis on seven different
Windows machines, ranging from Windows 2000, Windows XP and Vista. In
five of the cases, inserting 128 rows outside of the context of a
transaction takes 11 - 17 seconds. In the other two cases, a Windows XP
machine and a Windows 2000 machine running under VMWare, the exact same
operation takes between 0.8 and 1.1 seconds.
To eliminate hardware specific issues, I copied the VMWare W2K machine
to one of the "slower" machines, a Windows XP laptop. The VM ran the
operation in roughly 1.2 seconds. The host machine reports 15 seconds
for the exact same operation.
Antivirus is identical on five of the example machines. Indexing
Service is disabled on all machines. One of the "slower" units in
question is a state of the art, quad core machine.
I decided to isolate the issue from our application by creating a small
Ruby program which uses the 3.14 dll. The code is below:
--------
require 'dbi'
require 'sqlite3'
require 'benchmark'
db = SQLite3::Database.new( "stest.sldb3" )
db.execute(<<eof
CREATE TABLE IF NOT EXISTS RESPONSES (
RESPONSE_ID INTEGER PRIMARY KEY,
SURVEY_ID INTEGER,
QUESTIONNAIRE_ID TEXT,
DATA_ELEMENT_NAME TEXT,
CURRENT_VALUE VARCHAR(1000),
FIRST_VALUE VARCHAR(1000)
);
eof
)
db.execute('CREATE UNIQUE INDEX RESPONSE_IDX1 ON RESPONSES
(QUESTIONNAIRE_ID, DATA_ELEMENT_NAME);')
include Benchmark
bm(10) do |x|
x.report("insert") {
1000.times do |i|
db.execute("INSERT INTO RESPONSES VALUES (null, 1, 1, 'FOO#{i}',
'wibble, wobble, woo', null)")
end
}
end
--------
Execution time on my VM is:
user system total real
insert 0.093000 2.484000 2.577000 ( 8.125000)
Execution time on the HP laptop is:
user system total real
insert 0.453000 1.828000 2.281000 (125.719000)
As we can see, the benchmarking indicates that SQLite itself is unlikely
to be the culprit. Examination of task manager during operation shows
3% CPU time to Ruby in the slower case and 30% in the faster. The only
other process receiving significant time is System Idle. Examination of
task manager while executing our application on the different machines
showed similar results.
As a concluding note, the section of code which initiated this research
has already been enclosed in a start/end transaction. Therefore, the
question at hand is not "how do I solve this specific performance
problem" rather it is "what the heck is happening that is creating a
better than order of magnitude difference in execution time on five out
of seven Windows machines?".
Any suggestions for further investigation are appreciated.
John Elrick
Fenestra Technologies
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
