Hi,
I have tried converting a program from a homebrew "database" to sqlite3
for easier maintenance and hopefully better performance. While the
former is easily achieved, the performance is not making me happy. The
system is a "semi-embedded" small form-factor x86 machine with 128MB of
RAM booting and running off CF. OS is a 2.4.18-based linux built from
scratch.
I have run several tests outlined below and I can't get decent
UPDATE-Performance out of my database. Apparently I am doing something
horribly wrong. Can someone enlighten me?
The DB consists of a single table I am creating like this:
CREATE TABLE IF NOT EXISTS KFZ (
kfznr TEXT PRIMARY KEY,
saeule TEXT,
berechtigung2 TEXT,
berechtigung TEXT,
a_km TEXT,
max_km TEXT,
kont TEXT,
pincode TEXT,
CRC32 INTEGER,
verweis BLOB,
sperrung TEXT,
isNew INTEGER,
mustTrans INTEGER,
kennzeichen TEXT,
kontingentierung INTEGER);
CREATE INDEX IF NOT EXISTS IDX_KFZ_MUSTRANS ON KFZ (mustTrans);
CREATE INDEX IF NOT EXISTS IDX_KFZ_CRC32 ON KFZ (CRC32);
Then I insert about 300000 records in the context of a transaction. That
takes a while, but works reasonably well. The result is a DB file of
about 30MB.
The problem is with bulk-updating:
> # time sqlite3 kfzdb 'update kfz set musttrans=3'
> real 10m 7.75s
> user 8m 49.73s
> sys 0m 24.29s
10 minutes is too long.
I must be doing something wrong. My database is on CF memory, and I
suspected that to be the problem. To verify that I mounted a tmpfs,
copied the DB there (taking 5.7s), and reran the test. Using memory
instead of disk brings the total down to just under 9 minutes.
So disk-I/O is propably not the cause. It's dominated by user-space time
and while the command is running the CPU is used to 99% by sqlite3.
Next I tried several of the suggestions from the SQLite Optimization
FAQ[1]. I timed the final combination of most of them:
># time sqlite3 kfzdb 'pragma synchronous=OFF ; pragma
count_changes=OFF ; pragma
journal_mode=OFF ; pragma temp_store=MEMORY ; update kfz set musttrans=3'
>off
>real 8m 29.87s
>user 8m 17.64s
>sys 0m 8.10s
So no substantial improvement.
Finally I repeated the test using a simpler table consisting only of the
column musttrans and 300000 records. Updating that took abount the same
amount of time.
Ciao, MM
[1] http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
