Databases
StackOverflow2013
Postsdbo
POSQLite slows down machine on bulk insert
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite slows down machine on bulk insert
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBraden
    1. USBraden
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLite slows down machine on bulk insert
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre both the of the table structures in Prod and QA identical? Are the using the same fileversion for Sqlite?
      singulars
      1. POSQLite slows down machine on bulk insert
      1. USMy Other Me
    2. COPlease provide more information about the schema, the indexes you are using, the order in which data is being retrieved from the web service and the total number of records you wish to cache. It may be more efficient to use an in-memory database for initially seeding from your web service.
      singulars
      1. POSQLite slows down machine on bulk insert
      1. USdwurf
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload