0

My C application uses SQLite 3.36. I am trying to move journal mode from OFF to WAL. But insertion in WAL takes 4 times longer. In the empty table, If insertion of N records taking 7 secs in Journal Mode OFF, in WAL insertion of N records takes ~27 secs. I was expecting faster insertion in WAL mode.

My application contains the structure of the record in an array/list and insertion is done inside a loop. In existing code sqlite3_prepare_v2 is inside the loop.

Below is the sample code -

int get_stmt_id() {
    //Based on application logic return id of the SQL statement.
}
sqlite3_stmt prepare_statment(int stmtid){
  //1. Search stmtid in the prepared statement array.If found return stmt.
  //2. If not found prepare a statement and add it to the prepared statement array. Return newly prepared stmt.
}
int insert_nodes_to_table(Node* node){
   ...
   ...
   stmtid = get_stmt_id();
   stmt = prepare_statment(stmtid);
   sqlite3_prepare_v2(..,...,stmt,....);
   sqlite3_bind_int(.....);
   ..
   ..
   sqlite3_bind_text(....);

   ..
   ...
   status = sqlite3_step(stmt);
   ...
   sqlite3_reset(stmt);
   sqlite3_clear_bindings(stmt);
}

sqlite3_exec(db_write, "BEGIN TRANSACTION", NULL,NULL, NULL)
for (node = head; node != NULL; node = inode->next) {
    insert_nodes_to_table(node);
    ....
    ....
}
sqlite3_exec(db_write, "END TRANSACTION", NULL,NULL, NULL)

Please help me understand, Is the prepared statement inside loop causing the slow insertion? If yes, why are insertions fast in journal mode OFF/Normal and not in WAL mode with same code ?

rg665n
  • 163
  • 1
  • 8
  • 1
    sqlite3_prepare_v2 should not be inside the insert loop. Chances are you may not need sqlite3_clear_bindings at all. Have you studied https://stackoverflow.com/questions/1711631 ? – PChemGuy Nov 24 '22 at 13:22
  • Yes. I went through stackoverflow.com/questions/1711631. I wanted to know why there is performance degradation in WAL only, due to sqlite3_prepare_v2 inside the loop. I will move sqlite3_prepare_v2 outside the loop and will share the results. – rg665n Nov 25 '22 at 08:18
  • No, this is not WAL related. The code will be slower when sqlite3_prepare_v2 is inside the loop either way. – PChemGuy Nov 25 '22 at 08:20
  • With WAL, insertion took 27 secs and without WAL it was taking only 7 secs. So, I am not able to understand the root cause for it. – rg665n Nov 25 '22 at 08:25
  • I get it. I am not providing an explanation. I am saying that you need to refactor you code regardless. – PChemGuy Nov 25 '22 at 09:01
  • I realized that my sqlite3_prepare_v2 is conditional and called only when the particular statement is not already prepared. So we can consider it outside the loop only. Please help me understand why insertion in WAL is taking more time? – rg665n Dec 01 '22 at 12:42
  • Really!? I do not see any conditionals. – PChemGuy Dec 01 '22 at 12:46
  • Yes. I have edited the code snippet. I have multiple SQL statements. Based on various conditions one particular SQL is selected and the statement is prepared and added to the prepared statement array. In the next iteration, if a statement already exists in the prepared statement array no need to prepare it. I cannot share the complete code here, hence adding psuedo/sample code only – rg665n Dec 01 '22 at 13:00
  • Well, then you are welcome to solve your own problems yourself. – PChemGuy Dec 01 '22 at 16:08

0 Answers0