800 inserts/updates per second versus 22 inserts per second when adding "ON CONFLICT DO UPDATE"

[Fischkopppp]

Hi Arun, first of all thank you for this great library! I absolutely love it.

I have used your "bulk_data_insert" example, adding "BEGIN TRANSACTION" / "END TANSACTION" and I am using a SPI SD card (WEMOS MINI D1 ESP32 with SD shield). I can insert or update more than 800 rows per second in a database. However if I add "ON CONFLICT (mac_id) DO UPDATE set type=?" to INSERT 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.

By the way, I have ordered a 4 Mbit / 512 KBytes FRAM (MB85RS4MT Adafruit), that can be read/written 10,000,000,000,000 times :) It is not here yet, but I am very excited to get this running on a FRAM. Although I am unsure if this is possible at all. Does someone has any suggestion where to start to get this running?

With best regards, Alex

#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_table1 (mac_id INTEGER NOT NULL PRIMARY KEY, type INTEGER, U1 INTEGER, U2 INTEGER, U3 INTEGER, U4 INTEGER, U5 INTEGER, U6 INTEGER, U7 INTEGER, U8 INTEGER, U9 INTEGER, U10 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_table1 set type=? 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 type=? 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_table1");
   if (rc != SQLITE_OK) {
     sqlite3_close(db1);
     return;
   }

   sqlite3_close(db1);
   displayFreeHeap();

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

}

@siara-cc @siara-in

[siara-cc]

In SQLite, tables are also indexes which have the primary key as ROWID, which is invisible. So if you are updating using mac_id upon conflict, it would have no choice other than searching the table sequentially. I would suggest using without rowid clause to force mac_id as primary key of the table instead of ROWID to improve speed. Example:

create table t1 (c1 primary key, c2, c3) without rowid;
insert into t1 values ('v1', 'v2', 'v3');
insert into t1 values ('v1', 'v2', 'v3') on conflict (c1) do update set c3= 'v4'

[siara-cc]

For using the FRAM you would have to change esp32.c to write to FRAM using API for whatever file system it supports. If it is raw data storage then you would have to implement your own way of storing and retrieving files.

Alternatively you could also look at https://github.com/siara-cc/sqlite_micro_logger_arduino which has callback methods for IO which lets you store/retrieve from whichever storage medium as you please. However there are a lot of limitations. SQL not supported. It was made mainly for storing sensor data.

[Fischkopppp]

Hi Arun, your suggestions made it a tiny bit faster ;) However I don't think I can make it any quicker with a SPI SD configuration. The real deal breaker seems to use a SD MMC configuration. I wrote a little program that fetch information from the SQL database, stores them in variables, shows them on a serial Monitor. And afterwards manipulates the SQL data to change the output in the next loop run. I am using two SQL prepare statements (one for SELECT + JOIN) and one for (UPDATE to manipulate by random). With an SD SPI card and about 40 rows / 7 columns it took about 5 to 7 second. While the sqlite3_step(res) part runs quite fast (a few Milliseconds). Before and after that it took quite some time to process the statements (between 2 - 4 seconds). My goal is to use the SQL database for graphical information. Storing all graphical information in the database and to be able to manipulate it with a PC via SD Card.

BUT ;-) If I use ESP32 with an SD MMC configuration the whole process takes 400 milliseconds without a "Begin Transaction" statement in the setup. Instead of 5 to 7 seconds! Using the "Begin Transaction" the total time needed shrinks to 30 milliseconds! That is great! ON CONFLICT (...) DO UPDATE statements are also much, much faster with an SD MMC configuration. In the case of the example in my first post I can "upsert" instead of 22 rows per seconds about 400 to 500 rows per second.

Summary: SPI SD = 5000-7000 milisec. SDMMC without transaction: 400 milisec. SDMMC with transaction: 30 milisec! (22 for Select+Join and 8 for Update)

This brings me to another question. Where the hell do I get ESP32 boards with SDMMC configuration?! ;-). I have tried to find the board from your picture without success. I have also tried soooo many boards that on paper looked promising. I also tried the Waveshare Micro SD Storage Board, that has all the right resistors in place. However I was not able to get it running. Only the AI-Thinker ESP32-CAM is working out of the box. Is it possible to change the pin mapping for SD MMC? I would like to try the layout from the ESP32 documentation: https://docs.espressif.com/projects/esp-idf/en/latest/esp32/api-reference/peripherals/sdmmc_host.html

[siara-cc]

Thanks for sharing your experience!! I purchased the esp32+sdmmc board from banggood.com but I am unable to find it now either.

[Fischkopppp]

Should I close this issue? Or should I let it open? By the way. I copied the database to spiff and tried the SPIFF and LITTLEFS library. Without the "Begin Transaction" statement updates were quite slow (nearly a second for each row). With it I am getting the following results:

total time for all processes (described in the post above:Select+Join & Update) spiff with SPIFF library; 350ms (very inconsistent between 170 to 500ms) spiff with LITTLEFS library: 140ms (very consistent between 135 to 145ms: about 6ms for Updating and 130 for select+join)

To get LITTLEFS running I had to add the solutions posted here: LittleFS.begin(false, "/spiffs") + modifying esp32.cpp

[siara-cc]

I will fix it for LITTLEFS during weekend and close both. Thanks for the info!!

[pasleto]

Hi, any update on LittleFS fix?

[siara-cc]

@pasleto I fixed the issue and also added an example for Little FS. Thanks @Fischkopppp !!

[bmurctrebor]

Hi everyone thank you so much for this SQLITE use on my ESP32 working great for a couple of years with UPDATE and INSERT but I tried INSERT ON CONFLICT DO UPDATE to simplify my code and I just can't get it running!!

In Platformio with an esp32doit-devkit-v1, with SQLITE3Esp32 v2.3 from 2020 march files, with database on LittleFS, I get "syntax error near update" with something like INSERT INTO marchandises(navire, marchandise, tonnage, CD) VALUES('a','b',c,'C') ON CONFLICT DO UPDATE set marchandise=excluded.marchandise,tonnage=excluded.tonnage;

and "parser stack overflow" with INSERT INTO marchandises(navire, marchandise, tonnage, CD) VALUES('a','b',c,'C') ON CONFLICT(navire,CD) DO UPDATE set marchandise=excluded.marchandise,tonnage=excluded.tonnage; where (navire,CD) is unique index.

Only INSERT INTO marchandises(navire, marchandise, tonnage, CD) VALUES('a','b',c,'C') ON CONFLICT DO NOTHING; is working for me.

executed with sqlite3_exec() or sqlite3_prepare_v2() doesn't change anything Thoses SQLITE requests are working with python3 console from .platformio with import SQLITE3 and sqlite3.connect() etc...

I suppose the trouble is coming from my config_ext.h ? which is

define BUILD_sqlite -DNDEBUG

define SQLITE_OMIT_LOAD_EXTENSION 1

define SQLITE_DQS 0

define SQLITE_OS_OTHER 1

define SQLITE_NO_SYNC 1

define SQLITE_TEMP_STORE 1

define SQLITE_DISABLE_LFS 1

define SQLITE_DISABLE_DIRSYNC 1

define SQLITE_SECURE_DELETE 0

define SQLITE_DEFAULT_LOOKASIDE 512,64

define YYSTACKDEPTH 20

define SQLITE_SMALL_STACK 1

define SQLITE_DEFAULT_PAGE_SIZE 512

define SQLITE_SORTER_PMASZ 4

define SQLITE_DEFAULT_CACHE_SIZE -1

define SQLITE_DEFAULT_MEMSTATUS 0

define SQLITE_DEFAULT_MMAP_SIZE 0

I tried different variants but never got the insert working! by the way I have 50 warnings "assignment discards 'const' qualifier from pointer target type" on build...

please if you could enlighten me!! best regards

[siara-cc]

You might try increasing YYSTACKDEPTH and making SQLITE_SMALL_STACK 0 but beyond that I am not sure if anything else will solve this.

[bmurctrebor]

thank you for your help but I didn't manage to get that INSERT Conflict working after 2 days of different tries (new database, new table without rowid, index, primary key, and options in config_ext.h and sqlite3_exec or prepare_v2 and controlling heap...)! So I give up and go back to UPDATE then INSERT OR IGNORE!