Are you sure you're using BEGIN/COMMIT on your transactions? I just used my benchmark data and inserted another 100,000 rows into the database in 2.3 seconds.
I made 1,100,000 records and cut the last 100,000 into a seperate file with BEGIN/COMMIT on both. time sqlite3 index.db <index.sql 20.552u 6.115s 0:27.43 97.1% 0+0k 0+0io 0pf+0w time sqlite3 index.db < indexa.sql 2.315u 1.014s 0:04.44 74.7% 0+0k 0+0io 0pf+0w Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fabian [fabianpi...@gmail.com] Sent: Wednesday, November 09, 2011 12:04 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] INDEX Types 2011/11/9 Simon Slavin <slav...@bigfraud.org> > > Didn't someone recently note that entering the first million records was > fast, but if he then closed and reopened the database, entering the next > 100,000 records was slow ? > > Yes, and there is still no real explanation for it, other than slow disk reads. But even with very slow random disk I/O, 30 seconds seems still way too slow for a 100MB file. But today I made a new observation: if I create the same table as a virtual FTS4 table, I can add the additional rows within 1 second (even on an un-cached database file). So if the reason for the slowness is disk-reads, the FTS4 way of creating/updating the index requires much less reads? Maybe because it allows for seperate tree-branches? FTS is overkill for my simple requirements, but if it's so much faster than a regular index, why not? The only things that's holding me back from switching to FTS for this table is: - I cannot use the UNIQUE constraint, to disallow duplicate values. - I cannot search efficiently for rows that DON'T match a certain value, because FTS doesn't allow a single NOT operator. So can someone explain what FTS is doing behind the scenes that makes these additional inserts so much faster? _______________________________________________ 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
