Slow work of insert data with SQLite3

[GE_Rita]

Arduino IDE version: 1.8.10
ESP32 packages version: 1.0.4
SQLite3 database lib: https://github.com/siara-cc/esp32_arduino_sqlite3_lib

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.Is there anybody can help? Thank you.

[tommeyers]

My curiosity led me to this:

https://stackoverflow.com/questions/171 ... -of-sqlite

[markxr]

Probably you are inserting each record in one transaction. Don't do that, it generates several writes to the filesystem, the flash devices are not very fast.

Instead write them all in one transaction, then it will need a lot fewer writes- it will be faster and wear out your flash more slowly.

[tommeyers]

Please post your del for the table and your code with insert.

One thing that I am interested in is if you have an index and if so do you need it when writing or is rowid enough.

Tom