SQLite for ESP32 slow when using "ON CONFLICT DO UPDATE"

Question

I am using this library for ESP32 to add a SQLite database to my project: https://github.com/siara-cc/esp32_arduino_sqlite3_lib

I have used the "bulk_data_insert" example, adding "BEGIN TRANSACTION" / "END TANSACTION" and I am using a SPI SD card (WEMOS MINI D1 ESP32 with SD shield) instead of a SD MMC configuration. I can insert more than 800 rows per second in a database with:

INSERT INTO test_table (mac_id,status,type) VALUES (?, ?, ?)

or update more than 800 rows per second with:

UPDATE test_table set status=? WHERE mac_id=?

However if I add "ON CONFLICT (mac_id) DO UPDATE set type=?":

INSERT INTO test_table (mac_id,type,status) VALUES (?, ?, ?) ON CONFLICT (mac_id) DO UPDATE set status=?

I will only get 22 rows per second (I am not using any Indexes). Any idea how I could improve this? I am still new to SQLite and c++ programming.

Has anyone a suggestion how to speed this up? I will mostly do updates. But now and than I will have to insert a new row.

This is the code I used for testing, based on the "bulk_data_insert" example:

/*
    This example demonstrates how SQLite behaves
    when memory is low.
    It shows how heap defragmentation causes
    out of memory and how to avoid it.
    At first it asks how much memory to occupy
    so as not be available to SQLite.  Then
    tries to insert huge number of records
    and shows how much free memory available
    after each insert.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SD.h"
#include "SPIFFS.h"

char *dat = NULL;
void block_heap(int times) {

  while (times--) {
     dat = (char *) malloc(4096);
  }

}

const char* data = "Callback function called";
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   Serial.printf("%s: ", (const char*)data);
   for (i = 0; i<argc; i++){
       Serial.printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   Serial.printf("\n");
   return 0;
}

int openDb(const char *filename, sqlite3 **db) {
   int rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.printf("Can't open database: %s\n", sqlite3_errmsg(*db));
       return rc;
   } else {
       Serial.printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;
int db_exec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   Serial.print(F("Time taken:"));
   Serial.println(micros()-start);
   return rc;
}

int input_string(char *str, int max_len) {
  max_len--;
  int ctr = 0;
  str[ctr] = 0;
  while (str[ctr] != '\n') {
    if (Serial.available()) {
        str[ctr] = Serial.read();
        if (str[ctr] >= ' ' && str[ctr] <= '~')
          ctr++;
        if (ctr >= max_len)
          break;
    }
  }
  str[ctr] = 0;
  Serial.println(str);
}

int input_num() {
  char in[20];
  int ctr = 0;
  in[ctr] = 0;
  while (in[ctr] != '\n') {
    if (Serial.available()) {
        in[ctr] = Serial.read();
        if (in[ctr] >= '0' && in[ctr] <= '9')
            ctr++;
        if (ctr >= sizeof(in))
          break;
    }
  }
  in[ctr] = 0;
  int ret = atoi(in);
  Serial.println(ret);
  return ret;
}

void displayPrompt(const char *title) {
  Serial.print(F("Enter "));
  Serial.println(title);
}

void displayFreeHeap() {
   Serial.printf("\nHeap size: %d\n", ESP.getHeapSize());
   Serial.printf("Free Heap: %d\n", heap_caps_get_free_size(MALLOC_CAP_8BIT));
   Serial.printf("Min Free Heap: %d\n", heap_caps_get_minimum_free_size(MALLOC_CAP_8BIT));
   Serial.printf("Max Alloc Heap: %d\n", heap_caps_get_largest_free_block(MALLOC_CAP_8BIT));
}

char *random_strings[] = {"Hello world", "Have a nice day", "Testing memory problems", "This should work", "ESP32 has 512k RAM", "ESP8266 has only 36k user RAM", 
     "A stitch in time saves nine", "Needle in a haystack", "Too many strings", "I am done"};
char sql[1024];
sqlite3 *db1;
sqlite3_stmt *res;
const char *tail;
int rc;

void setup() {
   Serial.begin(115200);
   if (!SPIFFS.begin(true)) {
     Serial.println(F("Failed to mount file Serial"));
     return;
   }

   randomSeed(analogRead(0));

 SPI.begin();
   SD.begin();

   
   displayFreeHeap();
   displayPrompt("No. of 4k heap to block:");
   block_heap(input_num());
   displayFreeHeap();


}

void loop() {

   // Open database 1
   sqlite3_initialize();
      if (openDb("/sd/test.db", &db1))
     return;


   displayFreeHeap();
   
   //"CREATE TABLE IF NOT EXISTS test_table (mac_id DATETIME NOT NULL PRIMARY KEY UNIQUE, status INTEGER, type INTEGER)"
   //"CREATE TABLE IF NOT EXISTS test_table1 (mac_id INTEGER NOT NULL PRIMARY KEY, U1 INTEGER, U2 INTEGER, U3 INTEGER, U4 INTEGER, U5 INTEGER, U6 INTEGER, U7 INTEGER, U8 INTEGER, U9 INTEGER, U10 INTEGER)"
   rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS test_table (mac_id DATETIME NOT NULL PRIMARY KEY UNIQUE, status INTEGER, type INTEGER)");
   if (rc != SQLITE_OK) {
     sqlite3_close(db1);
     return;
   }

   //displayFreeHeap();

   int rec_count;
   displayPrompt("No. of records to insert:");
   rec_count = input_num();
   //"INSERT INTO test_table (mac_id,sensor_type,status) VALUES (?, ?, ?) ON CONFLICT (mac_id) DO UPDATE set status=?"
   //"INSERT INTO test_table (mac_id,status,type) VALUES (?, ?, ?)"
   //"INSERT INTO test_table1 (mac_id,type,U1,U2,U3,U4,U5,U6,U7,U8,U9,U10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (mac_id) DO UPDATE set type=?
   char *sql = "UPDATE test_table set status=? WHERE mac_id=?";
   rc = sqlite3_prepare_v2(db1, sql, strlen(sql), &res, &tail);
   if (rc != SQLITE_OK) {
     Serial.printf("ERROR preparing sql: %s\n", sqlite3_errmsg(db1));
     sqlite3_close(db1);
     return;
   }
   sqlite3_exec(db1, "BEGIN TRANSACTION", NULL, NULL, &zErrMsg);
  int intvalue=10123100;
               
   char *value;
   while (rec_count--) {
    //value = rec_count;
    
    Serial.println(intvalue);
      //for:UPDATE daily_temp_ap set status=? WHERE mac_id=?   
      sqlite3_bind_int  (res, 1, random(10,12));
      sqlite3_bind_int  (res, 2, intvalue);
      /*
      //for:INSERT INTO test_table (mac_id,status,type) VALUES (?, ?, ?)
      sqlite3_bind_int  (res, 1, intvalue);
      sqlite3_bind_int  (res, 2, random(0,2));
      sqlite3_bind_int  (res, 3, random(1,2));    
      */
      /*
      //for:INSERT INTO test_table1 (mac_id,type,U1,U2,U3,U4,U5,U6,U7,U8,U9,U10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
      sqlite3_bind_int  (res, 1, intvalue);
      sqlite3_bind_int  (res, 2, random(1,2));
      sqlite3_bind_int  (res, 3, random(0,2));    
      sqlite3_bind_int  (res, 4, random(0,2));
      sqlite3_bind_int  (res, 5, random(0,2));   
      sqlite3_bind_int  (res, 6, random(0,2));
      sqlite3_bind_int  (res, 7, random(0,2));   
      sqlite3_bind_int  (res, 8, random(0,2));
      sqlite3_bind_int  (res, 9, random(0,2));   
      sqlite3_bind_int  (res, 10, random(0,2));
      sqlite3_bind_int  (res, 11, random(0,2));  
      sqlite3_bind_int  (res, 12, random(0,2));
      //for: ON CONFLICT (mac_id) DO UPDATE set type=?
      //sqlite3_bind_int  (res, 13, random(4,6));
      */
     
      intvalue++;      
      if (sqlite3_step(res) != SQLITE_DONE) {
        Serial.printf("ERROR executing stmt: %s\n", sqlite3_errmsg(db1));
        sqlite3_close(db1);
        return;
      }
      sqlite3_clear_bindings(res);
      rc = sqlite3_reset(res);
      if (rc != SQLITE_OK) {
        sqlite3_close(db1);
        return;
      }
      Serial.println(rec_count);
      //displayFreeHeap();
   }
    sqlite3_exec(db1, "END TRANSACTION", NULL, NULL, &zErrMsg);
   sqlite3_finalize(res);
   Serial.write("\n");

   rc = db_exec(db1, "Select count(*) from test_table");
   if (rc != SQLITE_OK) {
     sqlite3_close(db1);
     return;
   }

   sqlite3_close(db1);
   displayFreeHeap();

   displayPrompt("Press enter to continue:");
   input_num();

}