slow insertion - how to improve speed if need to insert large amount of rows ?

[our30K]

Hi There

thanks for creating this neat sqlite for ESP. quick question, I realise the insertion is kind of slow, how do I ensure fast insertion yet does not run out memory ?

I am using this to log sensor data, so it can be easily retrieve and manage later. the sensor is pumping out 4-5 lines(100 bytes a line) of data every second. and since I have other things to do when dealing with this output, I will prefer the logging to be as fast as possible.

Justin

[tobozo]

Maybe use a 1K buffer to store the output, then only write when the buffer is nearly full, or once every 30 seconds if the buffer isn't full ?

SPIFFS blocks are 4K and minimal efficient SD write is 512 bytes, getting closer to those values when writing may improve the performances,

[siara-cc]

@our30K Please try my other library https://github.com/siara-cc/sqlite_micro_logger_arduino, which exactly fits your need. It was built for the purpose of logging sensor data faster than the official library.

@tobozo What you have suggested is just what the Sqlite Micro Logger does. You could choose the block size depending on the optimal size for the FS.

Also you could use any IO Library - SPIFFS, SD, SdFat etc. I am planning to do the same for the library for ESP8266, instead of having to depend on SdFat (well, when I get time :-)).

[our30K]

thanks, I will give it a try in the next few days. meanwhile, I assume this also solve the problem of slow down when table gets big issue ?

[rjjrbatarao]

@our30K I used sqlite transactions to isert faster. You can use INSERT query instead of the UPDATE Im using and it should also be fine since sqlite transactions is compatible with this queries.