Sqlite C++ Insertion is slow

Question

I have following code that does work of inserting data in Sqlite Database but it is too slow. Sqlite FAQ says that it can support of 50K insertion at a time. I have attached the following code.

SqliteBuffer.h

#ifndef SQLITE_BUFFER_H #define SQLITE_BUFFER_H #include "sqlite3.h" #include <iostream> class SqliteBuffer { std::string db_name_; sqlite3 *db_; sqlite3_stmt *insert_stmt_; bool has_transaction_begun; public: SqliteBuffer(std::string db_name); ~SqliteBuffer(); int CreateTable(); void SaveMessage(std::string msg); void BeginTransaction(); void EndTransaction(); }; #endif

SqliteBuffer.cc

#include "SqliteBuffer.h" int SqliteBuffer::CreateTable() { sqlite3_stmt *create = NULL; if(sqlite3_prepare_v2(db_,"CREATE TABLE mytable (sif INTEGER PRIMARY KEY, log VARCHAR);",-1,&create,NULL)==SQLITE_OK){ sqlite3_step(create); sqlite3_finalize(create); } return 0; } SqliteBuffer::SqliteBuffer(std::string db_name) { int rc = sqlite3_open_v2(db_name.c_str(), &db_, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL); if(rc!=SQLITE_OK){ sqlite3_close_v2(db_); } CreateTable(); rc = sqlite3_prepare_v2(db_, "INSERT INTO mytable(log) VALUES (@LOG)", -1, &insert_stmt_, NULL); } void SqliteBuffer::SaveMessage(std::string message) { BeginTransaction(); sqlite3_bind_text(insert_stmt_,1,message.c_str(),-1,SQLITE_TRANSIENT); sqlite3_step(insert_stmt_); sqlite3_clear_bindings(insert_stmt_); sqlite3_reset(insert_stmt_); } void SqliteBuffer::BeginTransaction() { if(has_transaction_begun == false){ has_transaction_begun = true; sqlite3_exec(db_, "BEGIN TRANSACTION;",NULL,NULL,NULL); } } void SqliteBuffer::EndTransaction() { if(has_transaction_begun == true){ has_transaction_begun = false; sqlite3_exec(db_, "END TRANSACTION;",NULL,NULL,NULL); } }

main.cc

#include "SqliteBuffer.h" int main() { SqliteBuffer *sql = new SqliteBuffer("E:\\asdf.db"); for(int i(0); i<2000; i++){ sql->SaveMessage("HELLO WORLD"); } return 0; }

I have tested these code in Visual Studio 10 and they works but the insertion is very slow. Am I missing something here.

Since you never initialise has_transaction_begun, the program is undefined. — molbdnilo
– molbdnilo, Commented Nov 17, 2014 at 14:01
You must check the return value of all sqlite3_* functions. Due to the error shown by molbdnilo, the last sqlite3_exec will fail (because there is no active transaction). — CL.
– CL., Commented Nov 17, 2014 at 14:04
Yes I will be checking the errors in full code. As I am making prototype, this works fine except it is very slow. — Mahadeva
– Mahadeva, Commented Nov 17, 2014 at 14:08

cdhowie · Accepted Answer · 2014-11-17 13:57:45Z

Unless you do these operations in a transaction, each query implicitly happens inside of its own transaction. For an INSERT operation, this requires at least two flush+sync operations: one to write the data to the journal (rollback or WAL), one to write the data to the database itself, and possibly one more depending on the journal type. Sync operations on a file are very slow as they require the OS to empty the file's write buffer and wait for the disk to report that the data has been written.

Try starting a transaction before the loop and committing it immediately afterwards. You should see a significant gain in performance. The downside to this technique is that an application or system crash in the middle of the loop will cause all pending changes to be rolled back.

@SarvagyaPant OFFS...call BeginTransaction() make all of your calls to SaveMessage() then call EndTransaction().
@Captain Obvlious Even adding BeginTransaction() and EndTransaction() before savemessage is not working.
@cdhowie I fixed the issue. During the initialization of the SqliteBuffer, I added has_transaction_begun as false. This solved the issue. Thanks for your insight.
@SarvagyaPant Good catch, I completely missed the call to BeginTransaction() in SendMessage(). So the program had undefined behavior. And, if it did begin the transaction then there would be no changes in the DB since you never committed it before the program ends. As a side note, you never delete the SqliteBuffer object, and if you tried you would get a link-time error because you never defined the destructor.

Collectives™ on Stack Overflow

Sqlite C++ Insertion is slow

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related