It does not look like you are using transactions. Marian Aldenhoevel wrote: > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
