SQLITE multiple insert example

3 million insertions in 2.3s
mail@pastecode.io avatar
unknown
c_cpp
a year ago
2.4 kB
1
Indexable
Never
// I'd like a C++ code that uses sqlite3 library and performs multiple insertion
// into a table using transactions. Use BEGIN TRANSACTION and and END
// TRANSACTION
//
// Install lib:
// sudo apt-get install sqlite3 libsqlite3-dev sqlitebrowser
// Compile with:
// rm -f ./test.db && g++ main2.cpp -l sqlite3 -o main2 && time ./main2
// sqliteborwser ./test.db

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

using namespace std;

int main() {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rc;

  // Open database
  rc = sqlite3_open("test.db", &db);

  if (rc) {
    cerr << "Error opening SQLite database: " << sqlite3_errmsg(db) << endl;
    return 1;
  }

  // sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
  // sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, NULL);
  // sqlite3_exec(db, "PRAGMA page_size = 100000", NULL, NULL, NULL);

  string createStmt = "CREATE TABLE IF NOT EXISTS TestTable ("
                      "ID INTEGER PRIMARY KEY,"
                      "Name TEXT NOT NULL,"
                      "Age INTEGER);";

  // Create table
  rc = sqlite3_exec(db, createStmt.c_str(), NULL, NULL, NULL);

  if (rc) {
    cerr << "Error creating table: " << sqlite3_errmsg(db) << endl;
    return 1;
  }

  // Prepare statement for inserting data
  string insertStmt = "INSERT INTO TestTable (Name, Age) VALUES (?, ?)";

  rc = sqlite3_prepare_v2(db, insertStmt.c_str(), -1, &stmt, NULL);

  if (rc) {
    cerr << "Error preparing insert statement: " << sqlite3_errmsg(db) << endl;
    return 1;
  }

  // open transaction
  rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
  if (rc) {
    cerr << "Error creating table: " << sqlite3_errmsg(db) << endl;
    return 1;
  }

  // multiple insertions (total of about 50MB, 3 million insertions in 2.5s)
  const char *name = "John";
  for (int val = 1; val <= 3000000; val++) {

    // Bind text values and insert row
    sqlite3_bind_text(stmt, 1, name, strlen(name), SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, val);

    sqlite3_step(stmt);
    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);
  }

  // do the CREATE INDEX here

  // close transaction
  rc = sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
  if (rc) {
    cerr << "Error creating table: " << sqlite3_errmsg(db) << endl;
    return 1;
  }

  return 0;
}