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:
-
Batch Insertion:
- Accumulates inserts in a cache (vector)
- Flushes cache when reaching BATCH_SIZE
- Uses transactions to group inserts
- Reuses prepared statements
-
Performance Optimizations:
- Transactions reduce disk I/O operations
- Prepared statements avoid SQL parsing overhead
- Memory reserve for vector prevents reallocations
-
Error Handling:
- Proper transaction rollback on errors
- SQLite error message reporting
- Resource cleanup on failures
-
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