title: SQLite slows down machine on bulk insert
body: <p>I am using the system.data.sqlite wrapper for an application that caches a large amount of data.  Almost all of this data goes into 1 table with 3 columns.  The data from the cache is pulled down from a web service call and inserted into the database in a loop until the full data set has been cached.  Each call to the web service pulls down 3000 records and inserts them to the database in 1 transaction.</p>
  
  <p>After the database grows to about 8MB the inserts start to slow down and there is a 2-3MB journal file created AFTER the commit of each transaction.  This causes massive I/O which actually slows down the operating system(Windows XP) to the point of being unresponsive on the transaction commit.</p>
  
  <p>I have read that SQLite does not use clustered indexes so I'm unsure how the data is being ordered or if it is being ordered on insert.  The data is not being inserted in ordered way at all.</p>
  
  <p>The most confusing thing if have found with this problem is that this only happens when we are calling the production web service, QA's web service does not cause this large journal file to be created on every transaction commit (The one in QA is about 2KB-14KB).  The web service is retrieving data from a SQLServer database.  The only difference between production and QA is that QA's database is a back up of production from about a year ago.  The caching algorithm will start with the oldest data first in which case both of these databases(QA and Prod) should be 99% the same.</p>
  
  <p>Does SQLite create this large journal file because it is inserting unordered data and it is having to split pages?  Would upgrading to WAL alleviate this problem?</p>