CODEWITHSUNDEEP
c#sql.netsqlite
blizz
blizz

Reputation: 4168

How to query an SQLite db in batches

I am using C# with .NET 4.5. I am making a scraper which collects specific data. Each time a value is scraped, I need to make sure it hasn't already been added to the SQLite db.

To do this, I am making a call each time a value is scraped to query against the db to check if it contains the value, and if not, I make another call to insert the value into the db.

Since I am scraping multiple values per second, this gets to be very IO-intensive, with constant calls to the db.

My question is, is there any better way to do this? Perhaps I could queue the values scraped and then run a batch query at once? Is that possible?

Upvotes: 1

Views: 3937

Answers (1)

MPelletier
MPelletier

Reputation: 16687

I see three approaches:

  1. Use INSERT OR IGNORE, which will reject an entry if it is already present (based on primary key and unique fields). Or plainly INSERT (or its equivalent (INSERT or ABORT) which will return SQLITE_CONSTRAINT, a value you will have to catch and manage if you want to count failed insertions.
  2. Accumulate, outside the database, the updates you want to make. When you have accumulated enough/all, start a transaction (BEGIN;), do your insertions (you can use INSERT OR IGNORE here as well), commit the transaction (COMMIT;)
  3. You could pre-fetch a list of items you already have, depending, and check against that list, if your data model allows it.

Upvotes: 1

Related Questions