On Fri, 23 May 2008 15:21:42 +0000 (GMT), Morten wrote: > Hi > > I've been puzzled for some days. I believed I had a > hang in my system, but it turns out using the .read > <file.sql> is horribly slow. I believed it to be an > improper trigger, but removing it doesn't help a bit. > 1min36sec to read 1000 lines with 6 simple columns > into one table. > > I know insert with autocommit was slow, but not that > slow. Is this filesystem-io based? > it works very hard on the filesystem. I also tried > simplifying the primary key, but timing is the same. > Memory caching, etc? I can emulate file-inserts > faster in a palmpilot simulator. > > Nice if someone could share some light on this. > > I'm easily going around this bug with commit, so it > is not much of a problem once you know. > > SQLite 3.3.8 on linux 32bit. > > % wc -l test_autocommit.sql test_commit.sql > 1024 test_autocommit.sql > 1026 test_commit.sql > 2050 total > % time echo ".read test_autocommit.sql" | sqlite3 test.db > > real 1m36.116s > user 0m0.408s > sys 0m0.988s > > % rm -rf test.db > > If I add Begin transaction and commit it is blasting fast: > % time echo ".read test_commit.sql" | sqlite3 test.db > > real 0m0.309s > user 0m0.168s > sys 0m0.040s > > > sqlite> .schema > CREATE TABLE secanaconfig ( > name TEXT not null, > value TEXT not null, > type TEXT not null, > grp TEXT, > desc TEXT, > updated DATE, > PRIMARY KEY (name, type) > ); > CREATE TABLE subtypes ( > type TEXT PRIMARY KEY, > desc TEXT > ); > CREATE TRIGGER fki_secanaconfig > before insert on secanaconfig > FOR EACH ROW BEGIN > SELECT RAISE(ABORT, 'insert into secanaconfig foreign-key type does not > exists in subtypes table') > WHERE (SELECT type FROM subtypes WHERE type = NEW.type) IS NULL; > END;
It is expected behaviour, not a bug. For every transaction there are several fsync() calls to the operating system to flush buffers of both journal and database to disk. See the documentation for more details. http://www.sqlite.org/atomiccommit.html If you don't care about SQLites ACID features you can avoid some delays by applying the right PRAGMAs. http://www.sqlite.org/pragma.html#pragma_journal_mode http://www.sqlite.org/pragma.html#pragma_synchronous > Thanks > -- > Morten Bjoernsvik, Oslo, Norway -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
