Slow work of insert data

[Rita858]

Hi @siara-cc , I found it is so slow for ESP3 to insert data. More detail: Test:

• ESP32 inserted 100 records with four-column.
• ESP32 search a specific record from 10000 records.

Result:

• Esp32 took about 0.486s to retrieve while the insert took about 8s.

Question:

• Why is ESP32 insert so slow?
• How can I solve it?

I'm new to SQLite. Hope you reply when you are available. Thank you.

[siara-cc]

I think you have index in your database and whenever insertion happens in table index also has to be updated. Thats probably why it is slow. To solve the problem you could try tables using WITHOUT ROWID keyword and use the columns that need to be indexed as primary keys.

[Rita858]

I think you have index in your database and whenever insertion happens in table index also has to be updated. Thats probably why it is slow. To solve the problem you could try tables using WITHOUT ROWID keyword and use the columns that need to be indexed as primary keys.

But the thing is I have never used index in my table.

[GithubPatrick91]

I have the same issue. Even when i try WITHOUT ROWID, its not faster. Sometimes the INSERT Transaction takes around 27seconds for just 1000 integer.

[siara-cc]

@Rita-GE @GithubPatrick91 I will try on my side. Meanwhile, if you are trying to store sensor data, this library: https://github.com/siara-cc/sqlite_micro_logger_arduino is more suitable. It can both read and write SQLite dbs, but it does not support SELECT, INSERT etc. You will have to use the given API. Please see the examples.

[GithubPatrick91]

I already tried this library - and like your lightweigth and fast µlogger lib.

But unfortunately the speed of the "finalize()" command decrease also continuously. When the database has around 1 million rows (3 columns) the finalize takes around ~13seconds ("partial_finalize()" is very fast and stable).

(info: i used the SELECT command of your sqlite3 library to display the table)

[siara-cc]

Have you tried using Prepared Statement to insert the records?

[ram-prasanth-9394]

Hi @siara-cc It was very useful to use your code

But i need your help I was inserting and updating large amount of data using sd card in sqlie3 db I was facing some errors in the SELECT query and will post the code below.

Code `#include <SD.h>

#include <stdio.h>

#include <stdlib.h>

#include <sqlite3.h>

#include <FS.h>

#include <WiFi.h>

#include <HTTPClient.h>

#include <ArduinoJson.h>

const char * host = "API KEY";

const char * wifiName = ""; const char * wifiPass = ""; bool Flag = false; int rc; sqlite3 * db;

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 1; } void listDir(fs::FS & fs, const char * dirname, uint8_t levels) { Serial.printf("Listing directory: %s\n", dirname);

File root = fs.open(dirname); if (!root) { Serial.println("Failed to open directory"); return; } if (!root.isDirectory()) { Serial.println("Not a directory"); return; }

File file = root.openNextFile(); while (file) { if (file.isDirectory()) { Serial.print(" DIR : "); Serial.println(file.name()); if (levels) { listDir(fs, file.name(), levels - 1); } } else { Serial.print(" FILE: "); Serial.print(file.name()); Serial.print(" SIZE: "); Serial.println(file.size()); } file = root.openNextFile(); } }

void testFileIO(fs::FS & fs, const char * path) { File file = fs.open(path); static uint8_t buf[512]; size_t len = 0; uint32_t start = millis(); uint32_t end = start; if (file) { len = file.size(); size_t flen = len; start = millis(); while (len) { size_t toRead = len; if (toRead > 512) { toRead = 512; } file.read(buf, toRead); len -= toRead; } end = millis() - start; Serial.printf("%u bytes read for %u ms\n", flen, end); file.close(); } else { Serial.println("Failed to open file for reading"); }

file = fs.open(path, FILE_WRITE); if (!file) { Serial.println("Failed to open file for writing"); return; }

size_t i; start = millis(); for (i = 0; i < 2048; i++) { file.write(buf, 512); } end = millis() - start; Serial.printf("%u bytes written for %u ms\n", 2048 * 512, end); file.close(); }

int openDb(const char * filename, sqlite3 ** db) { int rc = sqlite3_open(filename, db); if (rc) { Serial.printf("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"); } return rc; }

void setup() { Serial.begin(115200); delay(10); Serial.println();

Serial.print("Connecting to "); Serial.println(wifiName);

WiFi.begin(wifiName, wifiPass);

while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); }

Serial.println(""); Serial.println("WiFi connected");

if (!SD.begin()) { Serial.println("Card Mount Failed"); return; } uint8_t cardType = SD.cardType();

if (cardType == CARD_NONE) { Serial.println("No SD card attached"); return; }

Serial.print("SD Card Type: "); if (cardType == CARD_MMC) { Serial.println("MMC"); } else if (cardType == CARD_SD) { Serial.println("SDSC"); } else if (cardType == CARD_SDHC) { Serial.println("SDHC"); } else { Serial.println("UNKNOWN"); }

uint64_t cardSize = SD.cardSize() / (1024 * 1024); Serial.printf("SD Card Size: %lluMB\n", cardSize);

listDir(SD, "/", 0);

char * zErrMsg = 0;

sqlite3_initialize();

}

void loop() { HTTPClient http;

http.begin(host);

int httpCode = http.GET(); String payload = http.getString();

Serial.println(httpCode); //200
Serial.println(payload);

if (openDb("/sd/NFC.db", & db)) return;

rc = db_exec(db, "CREATE TABLE IF NOT EXISTS NFCCardDetails (Physicalid String PRIMARY KEY ,WalletAmount Float)"); if (rc != SQLITE_OK) { sqlite3_close(db); return; }

if (httpCode == 200) { const size_t capacity = JSON_ARRAY_SIZE(23) + 23*JSON_OBJECT_SIZE(9) + 4930; DynamicJsonBuffer jsonBuffer(capacity);

JsonArray& root = jsonBuffer.parseArray(payload);
int sizeofArray = root.size();

for (int i = 0; i < sizeofArray; i++) {
  JsonObject & root_0 = root[i];
  const char* root_0_Physicalid = root_0["Physicalid"]; 
  int root_0_Walletamount = root_0["Walletamount"];

  String diff, sqlOpen, sqlClose, moduleOne, versionOne, Final, Where, sqlSelect;
  int Checker;
  String quote = ("'");
  moduleOne = String(root_0_Physicalid);
  versionOne = String(root_0_Walletamount);
  sqlSelect = String("SELECT * FROM NFCCardDetails where Physicalid=");
  Final = sqlSelect + quote + moduleOne + quote;
  int str_len = Final.length() + 1;
  char char_array[str_len];
  Final.toCharArray(char_array, str_len);
  
  Checker = db_exec(db, char_array);
  Serial.println(Checker);
   
  if(Checker == 0)
  {
    Serial.println("Inserting Value");
  sqlOpen = String("INSERT INTO NFCCardDetails VALUES (");
  diff = String(",");
  sqlClose = String(")");
  Final = sqlOpen + quote + moduleOne + quote + diff + quote + versionOne + quote + sqlClose;

  int str_len = Final.length() + 1;
  char char_array[str_len];
  Final.toCharArray(char_array, str_len);

  rc = db_exec(db, char_array);
  if (rc != SQLITE_OK) {
    sqlite3_close(db);
    return;
  }
  }else{
    Serial.println("Updating Value");
    sqlOpen = ("UPDATE NFCCardDetails SET WalletAmount = ");
    String quote = ("'");
    versionOne = String(root_0_Walletamount);
    Where = (" WHERE Physicalid = ");
    moduleOne = String(root_0_Physicalid);
    Final = sqlOpen + quote + versionOne + quote + Where + quote + moduleOne + quote;

    int str_len = Final.length() + 1;
    char char_array[str_len];
    Final.toCharArray(char_array, str_len);

    rc = db_exec(db, char_array);
    if (rc != SQLITE_OK) {
    sqlite3_close(db);
    return;
    }                
}    

} sqlite3_close(db); } }`

Error in serial monitor **Values are inserting and updating correctly, but im not ok with error Called

SQL error: query aborted

**

WiFi connected SD Card Type: SDHC SD Card Size: 15000MB Listing directory: / DIR : /System Volume Information FILE: /NFC.DB SIZE: 12288 **HERE i will print API data ** Opened database successfully CREATE TABLE IF NOT EXISTS NFCCardDetails (Physicalid String PRIMARY KEY ,WalletAmount Float) Operation done successfully SELECT * FROM NFCCardDetails where Physicalid='62:8a:2f:2e' Callback function called: Physicalid = 62:8a:2f:2e WalletAmount = 205.0

SQL error: query aborted 4 Updating Value UPDATE NFCCardDetails SET WalletAmount = '205' WHERE Physicalid = '62:8a:2f:2e' Operation done successfully SELECT * FROM NFCCardDetails where Physicalid='62:8a:2f:2e' Callback function called: Physicalid = 62:8a:2f:2e WalletAmount = 205.0

SQL error: query aborted 4 Updating Value UPDATE NFCCardDetails SET WalletAmount = '205' WHERE Physicalid = '62:8a:2f:2e' Operation done successfully SELECT * FROM NFCCardDetails where Physicalid='04:6b:43:3a:ec:64:80' Callback function called: Physicalid = 04:6b:43:3a:ec:64:80 WalletAmount = 88.0

Please help, Thanks in advance.