--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > >>> > >>> I have been struggling with the performance of insertion in sqlite. > >>> > >>> Here we have a very simple case : > >>> > >>> A table with an integer autoincrement primary key and a text > >>> field that is > >>> unique. > >>> > >>> CREATE TABLE my (id PRIMARY KEY, url); > >>> > >>> CREATE UNIQUE INDEX myurl ON my(url); > >>> > >>> > >>> My application requires inserting up to 10 million records in > >>> batches of > >>> 20 thousand records. > > For each group of 20000 records, first insert them into a TEMP table. > Call the temp table t1. Then transfer the records to the main table > as follows: > > INSERT OR IGNORE INTO my(url) SELECT url FROM t1 ORDER BY url;
I had no performance improvement with that temp store staging table technique in my testing - actually it was slower. http://www.mail-archive.com/sqlite-users@sqlite.org/msg22143.html Mind you, the table I was testing against had 4 indexes, whereas the above table has 2. I also wasn't using "OR IGNORE". There might be a difference. Just setting pragma cache_size to a huge value and inserting into the table normally in large batches resulted in better performance in my case. It may have already been mentioned, but having a big database page_size value helps minimize the disk writes as well. ____________________________________________________________________________________ Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. http://mobile.yahoo.com/go?refer=1GNXIC ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
