Trying to hack my approach to writing SQLite3 at the same time, is the best way to do this?

Question

I am using Delphi XE2 along with DISQLite v3 (which is basically a SQLite3 port). I like everything about SQLite3, except for the lack of concurrent writing , especially since I actively rely on multithreading in this project :(

My profiler made it clear that I needed to do something, so I decided to use this approach:

• Whenever I need to insert a record in the database, instead of doing an INSERT, I write SQL query in a special file, i.e.

  WriteToFile_Inline(SPECIAL_FOLDER_PATH + '\' + GUID, FileName + '|' + IntToStr(ID) + '|' + Hash + '|' + FloatToStr(ModifDate) + '|' + ...);

• I added a timer (in the main thread of the application) that fires every minute , parses these files, and then INSERT QUESTIONS using a transaction.

• Delete these temporary files at the end.

Result . I like the 500% performance boost . Plus, this ACID method, since I can always check SPECIAL_FOLDER_PATH after a power failure and execute the found INSERTs.

Despite the good results, I am not very happy with the method used (hacks, to say the least), I continue to think that if I had a generics-like with quick search access, thread-safe, ACID list, that would be much cleaner (and maybe faster?)

So my question is: do you know anything like this for Delphi XE2?

---

PS. I hope that many of you, having read the above code, will be shocked and begin to insult me ​​at this moment! Please be my guest, but if you know a better (i.e. faster) ACID approach, share your thoughts !

Answer 1

Your idea of ​​sending inserts to the queue, which will rearrange the inserts and join them through the prepared instructions, is very good. Using a timer in the main thread or a separate thread is up to you. This will avoid blocking.

Remember to use a transaction, and then commit it every 100/1000 inserts, for example.

For high performance using SQLite3, see, for example, this blog article (and the image below) :

In this graphic, the best performance (file off) comes from:

• PRAGMA synchronous = OFF
• Using prepared statements
• Inside a transaction
• In WAL mode (especially in concurrency mode)

You can also change the page size or log size, but the settings above are the best. See https://stackoverflow.com/search?q=sqlite3+performance

If you do not want to use a background thread, make sure that WAL is enabled, prepare your statements, use batches and rearrange your process to release SQLite3 lock as soon as possible.

Best performance will be achieved by adding a Client-Server layer, as with mORMot.

Answer 2

With files, you organized an asynchronous job queue with saving. This avoids one-by-one and uses the batch approach (group of records) to insert records. Comparing one-by-one and batch :

• First, it works in automatic commit mode (possibly) for each record, the second one wraps the packet in one transaction and gives the best performance.
• first prepares the INSERT command each time you need to insert a record (possibly), a second time per batch, and a second time increase the value.

I don't think SQLite concurrency is a problem in your case (at least not a major problem). Since in SQLite one insertion is relatively fast and concurrency performance problems, you will get a high workload. You will likely get similar results with other DBMSs such as Oracle.

To improve the batch approach, consider the following:

• Consider setting journal_mode to WAL and disabling shared caching mode .
• use the background thread to process the queue. Instead of a fixed time interval (1 min), check SPECIAL_FOLDER_PATH more often. And if the queue has more than X KB of data, then start processing. Or use the number of entries in the queue and the event to notify the thread that the queue should begin processing.
• use the multy-record prepared by INSERT instead of a single INSERT record. You can build an INSERT for 100 records and process your queue data in one batch, but for 100 records.
• consider writing / reading binary field values ​​instead of text values.
• Consider using a set of files with a predefined size.
• etc.

Answer 3

sqlite3_busy_timeout quite inefficient because it does not return immediately when the table waiting for it is unlocked.

I would try to create a critical section ( TCriticalSection ?) To protect each table. If you enter a critical section before inserting a row and exit it immediately afterwards, you will create better table locks than SQLite provides.

Not knowing your access patterns, it's hard to say whether this will be faster than finalizing minute inserts into individual transactions.