Bulk insert data in SQLite with prepare statements in C++

Question

I have a std::vector with more than 100,000 JSON records. I need to insert those all records into the SQLite database in quick. First I tried to insert one by one to the database, but it takes more than 1hour to complete.

Then I search is there any method to insert bulk data (batch data) in once with SQLite. But there are lack of sources for it. Then I have tried the following code. For the data insertion, I'm using sqlite3_prepare statements with binding parameters. I need to extend this method for bulk insertions.

void insertSAData(vector<json> saData){
        sqlite3_mutex_enter(sqlite3_db_mutex(db));
        char* errorMessage;
        sqlite3_exec(db, "PRAGMA synchronous=OFF", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA journal_mode=MEMORY", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA temp_store=MEMORY", NULL, NULL, &errorMessage);

        sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);

        char const *szSQL = "INSERT INTO SA_DATA (DATA_ID,P_KEY,AMOUNT,AMOUNT_INDEX) VALUES (?,?,?,?);";
        int rc = sqlite3_prepare(db, szSQL, -1, &stmt, &pzTest);

        if( rc == SQLITE_OK ) {
            for(int x=0;x<saData.size();x++){
                // bind the value
                sqlite3_bind_int(stmt, 1, saData[x].at("lastSAId"));
                std::string hash = saData[x].at("public_key");
                sqlite3_bind_text(stmt, 2,  hash.c_str(), strlen(hash.c_str()), 0);
                sqlite3_bind_int64(stmt, 3, saData[x].at("amount"));
                string amount_index = saData[x].at("amount_idx");
                sqlite3_bind_int(stmt, 4, atoi(amount_index.c_str()));

                sqlite3_step(stmt);
                sqlite3_finalize(stmt);
            }
        }else{
            fprintf(stderr, "SQL error: %s\n", zErrMsg);
            sqlite3_free(zErrMsg);
        }

        sqlite3_mutex_leave(sqlite3_db_mutex(db));
    }

But this code isn't working.

Or is there any other method to achieve my task? I need to insert all the JSON data into the database faster.

Answer 1

This code solved my problem.

void insertSAData(vector<json> saData){
        sqlite3_mutex_enter(sqlite3_db_mutex(db));
        char* errorMessage;
        sqlite3_exec(db, "PRAGMA synchronous=OFF", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA journal_mode=MEMORY", NULL, NULL, &errorMessage);
        sqlite3_exec(db, "PRAGMA temp_store=MEMORY", NULL, NULL, &errorMessage);

        sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);

        char const *szSQL = "INSERT INTO SA_DATA (DATA_ID,P_KEY,AMOUNT,AMOUNT_INDEX) VALUES (?,?,?,?);";
        int rc = sqlite3_prepare(db, szSQL, -1, &stmt, &pzTest);

        if( rc == SQLITE_OK ) {
            for(int x=0;x<saData.size();x++){
                // bind the value
                sqlite3_bind_int(stmt, 1, saData[x].at("lastSAId"));
                std::string hash = saData[x].at("public_key");
                sqlite3_bind_text(stmt, 2,  hash.c_str(), strlen(hash.c_str()), 0);
                sqlite3_bind_int64(stmt, 3, saData[x].at("amount"));
                string amount_index = saData[x].at("amount_idx");
                sqlite3_bind_int(stmt, 4, atoi(amount_index.c_str()));

                int retVal = sqlite3_step(stmt);
                if (retVal != SQLITE_DONE)
                {
                    printf("Commit Failed! %d\n", retVal);
                }

                sqlite3_reset(stmt);
            }

            sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);
            sqlite3_finalize(stmt);
        }else{
            fprintf(stderr, "SQL error: %s\n", zErrMsg);
            sqlite3_free(zErrMsg);
        }

        sqlite3_mutex_leave(sqlite3_db_mutex(db));
    }