SQLite3 slow execution (ESP32 & C++)?

Question

I have setup the SQLite3 C Interface on a ESP32 (Compiler: Arduino IDE) with a CPU frequency of 240Mhz.

All the functions working properly, except the sqlite3_step() function. This command is "incredible" slow.

My Function for insert 3 data-pairs in a vector which has a size of 1k (vector<struct DATA>):

 struct DATA{
     pair<int, byte> id;         
     pair<int, byte> value;
     pair<string, byte> s_timestamp;
   };

bool ESPDB::insert_vector(const vector<struct DATA> &data_buffer) {
  char *errmsg;

  if(sqlite3_open(("/sdcard/" + database_).c_str(), &db) != SQLITE_OK){
    return 0;
  }

  sqlite3_stmt *stmt;

  sqlite3_prepare_v2(db, ("INSERT INTO " + table_ + " " + columns_ + " VALUES " + 
  placeholder_).c_str(), -1, &stmt, 0);
  sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &errmsg);

  for (const struct DATA &data_pair : data_buffer){
    if(data_pair.id.first !=0 && data_pair.id.second !=0){
      sqlite3_bind_int(stmt, data_pair.id.second, data_pair.id.first);
    }
    if(data_pair.value.first !=0 && data_pair.value.second !=0){
      sqlite3_bind_int(stmt, data_pair.value.second, data_pair.value.first);
    }
    if(data_pair.s_timestamp.first.length() > 0 && data_pair.s_timestamp.second !=0){
      sqlite3_bind_text(stmt, data_pair.s_timestamp.second, data_pair.s_timestamp.first.c_str(), 
    data_pair.s_timestamp.first.length(), SQLITE_STATIC);
    }
    
    sqlite3_step(stmt);
    sqlite3_reset(stmt);
  }
  sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &errmsg);
  sqlite3_finalize(stmt);
  sqlite3_close(db);
 
  return 1;
}

The average result of the performance test was around 3000ms for 1k vector - (a few times, its excecuting it in less than 300ms). The step command itself taking around 16-25ms for each row insert. The strange thing is, that the sqlite3_step() command speeds up after around 100 inserts to finally 0ms (<1ms) for each row.

The sequence of the code is following:

• -open database
• -drop table
• -create table if not exists
• -single insert function (single DATA struct)
• -select query
• -insert the vector (size = 1000)
• -select query in JSON

in ALL functions which handling the database connection, i open and close the database by following code: Drop-Table Example:

bool ESPDB::drop_table(const string &table){
  if(sqlite3_open(("/sdcard/" + database_).c_str(), &db) != SQLITE_OK){
    return 0;
  }
  sql_stmt_(("DROP TABLE IF EXISTS " + table).c_str());
  sqlite3_close(db);
  return 1;
}

Update:

• opened database only once
• ensured 240MHz CPU speed

Here a chart for visualisation:

(the issue is, lateron i have a vector size of 250 due to small available heap size in my code - this makes it really slow - as you can see, the first ~130 taking around 15ms. after this its operating really fast)

chart3: time of insert a vector with size 100 in a while loop - total excecutions 85~]