But with large cache there is too much fragmentation observered (pgin/pgout) over a period of time. Do u see this behaviour?
My CacheSize: 32KB, PgSize: 8KB (to the limits) regards ragha ****************************************************************************************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! ***************************************************************************************** ----- Original Message ----- From: Joe Wilson <[EMAIL PROTECTED]> Date: Monday, September 17, 2007 11:07 am Subject: Re: [sqlite] Sqlite insertion performance > --- "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] > ------------------------------------------------------------------- > ---------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
