SQLite alternative with concurrent writing (Delphi)

What is your application speed if:

• You use only one DB connection for all your threads;
• You protect your DB connection access with a global critical section.

Then you can try our Sqlite3 static binding which was compiled without thread mutex:

#define SQLITE_THREADSAFE 2//  assuming multi-thread safety is made by caller - in our framework, there is// only one thread using the database connection at the same time, but there could// be multiple database connection at the same time (previous was 0 could be unsafe)#define SQLITE_OMIT_SHARED_CACHE 1// no need of shared cache in a threadsafe calling model

We use such a model in our mORMot ORM framework, and, associated with four levels of cache:

• Statement cache for reuse of SQL statements, and bound parameters on the fly;
• Global JSON result cache at the database level, which is flushed globaly on any INSERT/UPDATE;
• Tuned record cache at the CRUD/RESTful level for specified tables or records on the server side;
• Tuned record cache at the CRUD/RESTful level for specified tables or records on the client side.

Resulting performance are not bad at all - it scales well in multi-thread access, even with a global critical section. Of course, SQlite3 was not designed to scale as well as Oracle! But I've used SQlite on real applications, with a lot of client. You may consider using FireBird which has a more complex (and tuned) architecture for client-server.

About making writing faster, you can group your writings into a transaction, then it will be much faster. This is what I use for speed-up writing and you can extend this concept with multiple clients: on the server side, you regroup your writes into a shared transaction, which is to be committed after a timeout period (e.g. one second).

SQLite3 is very fast for such adding (even more with a prepared INSERT statement with bound parameters), but slow for individual addings, because it has to lock the whole file using low-level API, which is damn slow. In order to make it ACID, ensure that the commit is always processed. In fact, other DB engines achieve good concurrent speed with a similar process, hidden in the background. SQLite3 default writing method is expected to be such, in order to ensure access to the same file from multiple processes - but in your Client-Server application, you can just rely on the fact that you'll be the only one to access to the SQLite3 database file, so it will be just safe.

Would something like the Embedded version of Firebird DB be of any help?

FirbirdSQL.org Downloads Page

I've used this with success in the past.

Just split your tables (which could be written concurrently) into separate SQLite database files and attach them all together using your main connection.