SQLite3 with batch inserts and caching

Here's a C++ example using SQLite3 with batch inserts and caching:

#include <sqlite3.h>
#include <vector>
#include <iostream>
#include <string>

struct Data {
    int id;
    std::string name;
};

const size_t BATCH_SIZE = 1000;

void insertBatch(sqlite3* db, sqlite3_stmt* stmt, std::vector<Data>& cache) {
    if (cache.empty()) return;

    char* errMsg = nullptr;
    int rc = sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "BEGIN TRANSACTION failed: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return;
    }

    for (const auto& item : cache) {
        sqlite3_bind_int(stmt, 1, item.id);
        sqlite3_bind_text(stmt, 2, item.name.c_str(), -1, SQLITE_STATIC);

        rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE) {
            std::cerr << "INSERT failed: " << sqlite3_errmsg(db) << std::endl;
            sqlite3_exec(db, "ROLLBACK", nullptr, nullptr, nullptr);
            break;
        }

        sqlite3_reset(stmt);
    }

    if (rc == SQLITE_DONE) {
        rc = sqlite3_exec(db, "COMMIT", nullptr, nullptr, &errMsg);
        if (rc != SQLITE_OK) {
            std::cerr << "COMMIT failed: " << errMsg << std::endl;
            sqlite3_free(errMsg);
        }
    }

    cache.clear();
}

int main() {
    sqlite3* db;
    int rc = sqlite3_open(":memory:", &db);
    if (rc != SQLITE_OK) {
        std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }

    // Create table
    const char* createTableSQL = 
        "CREATE TABLE IF NOT EXISTS MyTable ("
        "id INTEGER PRIMARY KEY,"
        "name TEXT NOT NULL);";
    
    char* errMsg = nullptr;
    rc = sqlite3_exec(db, createTableSQL, nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "CREATE TABLE failed: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }

    // Prepare insert statement
    const char* insertSQL = "INSERT INTO MyTable (id, name) VALUES (?, ?);";
    sqlite3_stmt* stmt;
    rc = sqlite3_prepare_v2(db, insertSQL, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        std::cerr << "Prepare failed: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_close(db);
        return 1;
    }

    std::vector<Data> cache;
    cache.reserve(BATCH_SIZE);

    // Generate sample data
    for (int i = 1; i <= 12345; ++i) {  // Test with 12345 items
        cache.push_back({i, "Name_" + std::to_string(i)});
        
        if (cache.size() >= BATCH_SIZE) {
            insertBatch(db, stmt, cache);
        }
    }

    // Insert remaining items
    insertBatch(db, stmt, cache);

    // Cleanup
    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;
}

Key features of this implementation:

  1. Batch Insertion:

    • Accumulates inserts in a cache (vector)
    • Flushes cache when reaching BATCH_SIZE
    • Uses transactions to group inserts
    • Reuses prepared statements
  2. Performance Optimizations:

    • Transactions reduce disk I/O operations
    • Prepared statements avoid SQL parsing overhead
    • Memory reserve for vector prevents reallocations
  3. Error Handling:

    • Proper transaction rollback on errors
    • SQLite error message reporting
    • Resource cleanup on failures
  4. Memory Management:

    • Uses SQLITE_STATIC binding for strings
    • Proper cleanup of SQLite resources
    • Vector clearing after batch insert

To compile and run:

g++ -std=c++11 example.cpp -lsqlite3 -o example
./example

Note:

  • Adjust BATCH_SIZE based on your needs (typical values 500-5000)
  • For file-based database, replace ":memory:" with a filename
  • Add additional error checking as needed for your use case
  • Use SQLITE_TRANSIENT instead of SQLITE_STATIC if binding temporary values
posted @ 2025-03-13 19:03  julian-zhang  阅读(38)  评论(0)    收藏  举报