SQLite .Net Performance

Question

I am trying to use sqlite in my application as a sort of cache. I say sort of because items never expire from my cache and I am not storing anything. I simply need to use the cache to store all ids I processed before. I don't want to process anything twice.

I am entering items into the cache at 10,000 messages/sec for a total of 150 million messages. My table is pretty simple. It only has one text column which stores the id's. I was doing this all in memory using a dictionary, however, I am processing millions of messages and, although it is fast that way, I ran out of memory after some time.

I have researched sqlite and performance and I understand that configuration is key, however, I am still getting horrible performance on inserts (I haven't tried selects yet). I am not able to keep up with even 5000 inserts/sec. Maybe this is as good as it gets.

My connection string is as below:

Data Source=filename;Version=3;Count Changes=off;Journal Mode=off;
     Pooling=true;Cache Size=10000;Page Size=4096;Synchronous=off

Thanks for any help you can provide!

Mitch Wheat · Accepted Answer · 2011-08-16 01:08:54Z

5

If you are doing lots of inserts or updates at once, put them in a transaction.

Also, if you are executing essentially the same SQL each time, use a parameterized statement.

Have you looked at the SQLite Optimization FAQ (bit old).

SQLite performance tuning and optimization on embedded systems

answered Aug 16, 2011 at 1:08

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chané Clark Gilliam Over a year ago

Thanks...yes I am using a transaction but I am creating a new one each time. I am also using a parameterized statement.

Samuel Neff · Accepted Answer · 2011-08-16 03:48:59Z

1

If you have many threads writing to the same database, then you're going to run into concurrency problems with that many transactions per second. SQLite always locks the whole database for writes so only one write transaction can be processed at a time.

An alternative is Oracle Berkley DB with SQLite. This latest version of Berkley DB includes a SQLite front end that has a page-level locking mechanism instead of database level. This provides much higher numbers of transactions per second when there is a high concurrency requirement.

http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html

It includes the same SQLite.NET provider and is supposed to be a drop-in replacement.

answered Aug 16, 2011 at 3:48

Samuel Neff

75.3k18 gold badges144 silver badges186 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:26:53Z

0

Since you're requirements are so specific you may be better off with something more dedicated, like memcached. This will provide a very high throughput caching implementation that will be a lot more memory efficient than a simple hashtable.

Is there a port of memcache to .Net?

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Aug 16, 2011 at 3:50

Samuel Neff

75.3k18 gold badges144 silver badges186 bronze badges

Collectives™ on Stack Overflow

SQLite .Net Performance

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related