Execute sql queries asynchronously in C++

Question

I have an application that reads data into a map and writes them to a sqlite database. The application does a lot of other things and is asynchronous (uses boost asio) except for writing to db part. Here is the sample pseudo code:

struct records;
std::map<int, records> list;
void read() {
    // Dump(read) X records into list
    // If record id doesn't exist, add it. dirty = true
    // If it exists, update all other fields. dirty = true if any fields changed.
}

void writeDB() {
    sqlite3_prepare_v2();
    for(const auto& record : list) { // iterate map
        if(!record.dirty())
            continue;
        sqlite3_bind(); // bind record fields
        sqlite3_step(); // execute sql 
        sqlite3_reset(); // reset
    }
    sqlite3_finalize();
}

main() {
    while(1) {
        read();
        writeDB();
    }
}

My initial plan was to spawn a new thread inside the for loop which would write each record in the database in parallel. There are two issues.

1) May need to implement db locking.

2) The sqlite bind(), step() and reset() operate on the same statement. So, the records have to be written sequentially.

I am using C++ 17. Any suggestions on making the database calls async? Thanks for helping!

You might consider having a thread dedicated to database inserts, with a message queue that other threads can use to send requests to it and it then handles them. Better than trying to have multiple threads write at the same time. — Shawn, Oct 12 '19 at 22:50

Mike Dinescu · Accepted Answer · 2019-10-12T22:49:36.817

You don't have to implement locking. SQLite does provide ACID guarantees which means that you don't have to implement locking around your inserts.

That being said, spawning a thread for each insert operation is for sure overkill. You can consider splitting your list into a number of parts equal to the number of cores on the machine and executing N parallel loops to insert. But anything beyond that is probably overkill in my opinion.

But don't take my word for granted. Consider instrumenting your code and benchmarking both the single-threaded version you have now as well as any parallel implementation you may choose. You can test for various # of threads (but keep in mind that there is a relation between multi-threaded code performance and # of available cores) as well as the concurrency implementation: either C++17 std parallel execution, or boost variants etc.

Good point. I will benchmark both the approaches. If I were to keep the application single threaded, any suggestions on making it async? — psy, Oct 13 '19 at 17:37
It depends on the consistency requirements of your application. A single background thread for performing all inserts might be a good place to start. Or simply executing the writeDB call on a thread in a threadpool each time.. — Mike Dinescu, Oct 13 '19 at 17:50

Execute sql queries asynchronously in C++

1 Answers1