SQLite.Net Performance

Question

I am trying to use sqlite in my application as a kind of cache. I’m saying that elements never expire from my cache, and I didn’t store anything. I just need to use the cache to store all the identifiers that I processed before. I do not want to recycle anything twice.

I enter items in the cache with 10,000 messages per second for a total of 150 million messages. My table is pretty simple. It contains only one text column in which identifiers are stored. I did all this in memory using a dictionary, however I process millions of messages and, although it is fast, I ran out of memory after a while.

I have studied sqlite and performance, and I understand that configuration is key, however, I still get terrible performance on inserts (I have not tried selecting yet). I can not keep up even with 5000 inserts / sec. Maybe it's as good as it gets.

My connection string is as follows:

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!

Answer 1

If you make many inserts or updates at once, put them in a transaction.

In addition, if you execute essentially the same SQL each time, use a parameterized statement.

Have you considered SQLite optimization FAQs (bit old).

Tuning and optimizing SQLite performance on embedded systems

Answer 2

If you have many threads associated with one database, you will encounter concurrency problems with so many transactions per second. SQLite always locks the entire database for writing, 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 the SQLite interface, which has a page-level locking mechanism rather than a database level. This provides a much larger number 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 should be a replacement.

Answer 3

Since your requirements are so specific, you might be better off with something more dedicated like memcached. This will provide very high caching bandwidth, which will be much more memory efficient than a simple hash table.

Is there a memcache port for .Net?