SQLite Alternate Write (Delphi)

Question

(Using Delphi 2010 + latest SQLite in WAL mode)

I am using DISQLite (SQLite Delphi Port) with my multi-threaded client application (not yet released, so I can change the DB mechanism if I really need to)

My profiler explicitly says that this is a stupid solution, I traced this to 2-3 very simple SQL statements that fly when executed in a single-threaded application, but since there are lock / wait threads (SQLite really doesn't work well with multiple threads trying to write at the same time)

I did my best to optimize my code / avoid bottlenecks, but after several weeks of hard work, I now wonder if it is easier to reset SQLite and choose a different DB mechanism (?)

My requirements:

• ACID
• Very good support for simultaneous read / write (write level).
• (Very) Fast and stable database engine
• B-tree
• Support for Delphi 2010

I use only the basic INSERT / UPDATE / DELETE commands with indexes, nothing unusual. Thus, my SQL requirements are relatively basic (I don’t need joins or other “more advanced” SQL files).

I am also open to NQL solutions if it supports the above requirements.

My research leads to DB Berkley, which, if I understand correctly, a modified version of SQLite with support for parallel writing, but the problem is not that it really is not for delphi.

I also read about the Kyoto Cabinet, but then again, no delphi support :(

Any suggestion would be more than welcome,

Thanks!

Answer 1

FWIW, I finally decided to stick with DISQLite along with this ugly, hacky solution:

• Some (minor) changes have been made to minimize the record in the database inside the threads (two database inserts required in each thread)

• When I absolutely had to write something to DB while working inside threads, I took the SQL query parameters and wrote them in a special folder (writing to files is very fast), i.e.

C: \ My-project \ pending-SQL \ insert_SOME-GUID.txt

Each file will look like this:

Param1 | Param2 | Param3 | Param4 |

• As soon as I ended up with threads (or if my application crashed), I called a routine that checked this folder, retrieved the SQL parameters and ran them using prepared statements (wrapped inside a transaction).

• Any file containing less than, say, 4 parameters will be considered damaged and will be skipped.

This is one of the features of a hacky ugly algorithm (shame on me!), But it works, it is fast, it is (kind of) an ACID, and I don’t need to spend months studying another DB mechanism that may (or may not) work.

I just wanted to thank everyone for their help, temporary pressure does not allow me to switch to another database engine, at least for this project.

Answer 2

What is the speed of your application if:

• For all your threads, only one database connection is used;
• You protect access to the database with the global critical section.

Then you can try our static Sqlite3 binding , which was compiled without a thread mutation:

#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 this model in our mormot ORM structure and are associated with four cache levels:

• Application cache for reusing SQL statements and bound parameters on the fly;
• A global database-level JSON cache of cache dumps globally on any INSERT / UPDATE;
• CRUD / RESTful level configured entry cache for specific tables or server-side entries;
• CRUD / RESTful level configured entry cache for specific client-side tables or records.

The resulting performance is not bad - it scales well in multi-threaded access, even with a global critical section. Of course, SQlite3 was not designed to scale, nor was it for Oracle! But I used SQlite for real applications with a large number of clients. You might consider using FireBird with a more complex (and customized) client server architecture.

About creating emails faster, you can group your entries into a transaction, then it will be much faster. This is what I use to speed up writing , and you can extend this concept with several clients: on the server side, you will regroup your records into a general transaction, which should be committed after a waiting period (for example, one second).

SQLite3 is very fast for such an addition (even more so with a prepared INSERT statement with bound parameters), but slow for individual additions, since it should block the entire file using a low-level API, which is cursed slowly. To make this an ACID, make sure the commit is always handled. In fact, other database engines achieve good parallel speed with a similar process hidden in the background. The default write method of SQLite3 is supposed to be one that allows access to a single file from several processes, but in Client-Server you can simply rely on being the only one who can access the SQLite3 Database File, so he will be safe.

Answer 3

Would there be something like an embedded version of Firebird DB of any help?

FirbirdSQL.org Download Page

I have used this with success in the past.

Answer 4

Just split your tables (which can be written at the same time) into separate SQLite and attach database files all together using your main connection.

Answer 5

This is my point:

An absolute database is a good alternative.

Answer 6

NexusDB can do all this, and the Embedded version is free . He supports Delphi as a first-class citizen.