sqlite3 database stored in Littlefs in esp32 s3 taking too much time in insert and update

[ankitmittal1402]

I am using an ESP32-S3 device and have created a .db file in the LittleFS partition for logging records. Initially, logging records is fast, but after around 1,000 records, it starts taking approximately 5 seconds to write a record. By the time it reaches 40,000 records, each write operation takes around 2 minutes.

My Configuration are:-

And i am using custom partition and my partition is as follows:

Name Type SubType Offset Size

nvs, data, nvs, 0x9000, 0x5000,
otadata, data, ota, 0xe000, 0x2000,
app0, app, ota_0, 0x10000, 0x300000,
app1, app, ota_1, 0x310000, 0x300000,
spiffs, data, spiffs, 0x610000, 0x9E0000,
coredump, data, coredump,0xFF0000, 0x10000,

And my littleFS details is:
#define CONFIG_LITTLEFS_MAX_PARTITIONS 1
#define CONFIG_LITTLEFS_PAGE_SIZE 4096
#define CONFIG_LITTLEFS_OBJ_NAME_LEN 64
#define CONFIG_LITTLEFS_READ_SIZE 128
#define CONFIG_LITTLEFS_WRITE_SIZE 128
#define CONFIG_LITTLEFS_LOOKAHEAD_SIZE 256
#define CONFIG_LITTLEFS_CACHE_SIZE 512
#define CONFIG_LITTLEFS_BLOCK_CYCLES 512
#define CONFIG_LITTLEFS_USE_MTIME 1
#define CONFIG_LITTLEFS_MTIME_USE_SECONDS 1
#define CONFIG_LITTLEFS_MALLOC_STRATEGY_DEFAULT 1
#define CONFIG_LITTLEFS_ASSERTS 1

[ankitmittal1402]

how to reduce this time taking while insert and update the records and i am using VScode PlatformIO
@siara-cc @siara-in

[siara-cc]

@ankitmittal1402 SQLite insert itself should be fast. It could be something that is done prior to insert. For instance if do a max or count of records before insert the time taken will increase proportionately.
For logging data, you could use https://github.com/siara-cc/sqlite_micro_logger_arduino

[ankitmittal1402]

@siara-cc thank you for reply. I am not making any operation like count and max still it take time to insert the records in littlefs but if i am using the sd card then it taking 0.05msec . can you give some suggestion what i have to do.

[siara-cc]

The sqlite micro logger was developed with logging speed and memory consumption in mind. It has limited features.  Please see the examples provided for arduino platform. They can be modified to make it work with littlest.

…

---- On Wed, 16 Oct 2024 15:01:23 +0530 ***@***.*** wrote ----

@siara-cc[https://github.com/siara-cc] Ok.. i want to make a sqlite database in esp32 littlefs and do operation on this so i want to know this https://github.com/siara-cc/sqlite_micro_logger_arduino[https://github.com/siara-cc/sqlite_micro_logger_arduino] giving the same feature as https://github.com/siara-cc/esp32_arduino_sqlite3_lib[https://github.com/siara-cc/esp32_arduino_sqlite3_lib] ... and currently i am using https://github.com/siara-cc/esp32_arduino_sqlite3_lib[https://github.com/siara-cc/esp32_arduino_sqlite3_lib] can you give me some idea what changes i have to make for improve the timing in insert and update — Reply to this email directly, view it on GitHub[#98 (comment)], or unsubscribe[https://github.com/notifications/unsubscribe-auth/ADDCTHD3FWOZDPIBVGFRU33Z3YW6XAVCNFSM6AAAAABQAUPGL6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMJWGI2TANBUGM]. You are receiving this because you were mentioned.Message ID:

[ankitmittal1402]

@siara-cc thank you for the reply and this idea. but i want to make a database in which insert , update and select operations to be performed frequently and to store at least 6000 records in three table each under one database. In this i am facing a issue in insert and update because it take too much time(around 60 sec) for one record after 1000 records. so any idea how to reduce this time or any alternative for this?

[siara-cc]

@ankitmittal1402 I can't think of any reason why it is slow only on littlefs. If it is not an issue with the schema or insert logic then the only thing that comes to my mind is whether the wear levelling logic is the cause.
You may also try to see if the same problem exists with SPIFFS.

[ankitmittal1402]

@siara-cc Thank you for the suggestion! However, I also tried using SPIFFS, and I encountered the same issue. I even tried FFAT on the ESP32, but it is also very slow.