Is there any disk I/O operation done before the transaction is commited in Sqlite?

Question

My sqlite database has only one table. This is what I am gonna do: Create the database with one table inside it, insert 10,000 records in that table, create the required indices on some columns and then close the connection to the database. I am inserting the records into database within a transaction (between BEGIN and END). I am also creating indices after insert to make the insert operation faster. My question is: Is anything written to disk before I execute the COMMIT command? I need to create the database and its table on the memory, insert records and create indices again on the memory, and then write all the data to the dist altogether at once. Am I achieving my purpose with the following code? If not, how can I improve it?

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char sql[500];

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   rc = sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &zErrMsg);
   rc = sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &zErrMsg);
   rc = sqlite3_exec(db, "BEGIN", NULL, 0, &zErrMsg);

   sql = "CREATE TABLE MyTable (Col1 NUMERIC, Col2 NUMERIC, Col3 NUMERIC);";
   rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);

   /* Create SQL statement */
   for(int i=0; i<10000; i++)
   {
       sprintf(sql, "INSERT INTO MyTable (Col1, Col2, Col3, ..., ColN"
                    "VALUES ( Val1, Val2, Val3, ..., ValN); ");

       /* Execute SQL statement */
       rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

       if( rc != SQLITE_OK ){
           fprintf(stderr, "SQL error: %s\n", zErrMsg);
           sqlite3_free(zErrMsg);
       }else{
           //fprintf(stdout, "Records created successfully\n");
       }
   }

   sql = "CREATE INDEX ix_Col1 ON MyTable(Col1 ASC);"
         "CREATE INDEX ix_Col2 ON MyTable(Col2 ASC);";
   rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);

   rc = sqlite3_exec(db, "COMMIT", NULL, 0, &zErrMsg);
   fprintf(stdout, "Records created successfully\n");
   sqlite3_close(db);

Answer 1

When SQLite's page cache overflows, changed data is written to disk even before the transaction ends. However, this is usually not a problem because that data would have to be written to disk anyway, and it is still in the operating system's file cache if it needs to be read again.

If you really want to increase the page cache size, you can use PRAGMA cache_size. But you have to measure yourself if this will make any difference.