search

siara-cc / esp32_arduino_sqlite3_lib

Sqlite3 Arduino library for ESP32
Apache License 2.0
376 stars 68 forks source link

SQL Updates #92

Open BackInFiveMinutes opened 4 months ago

BackInFiveMinutes commented 4 months ago

Brilliant library and really appriciate it. I have an issue with my code which hopefully someone can help

I have a small databast circa 170K in size. There is a config table of around 40 rows, no indexing. It takes me arounf 8 seconds to perform a SQL update which is pretty slow.

If I use SQLite Expert to perform the same update, the time is around 20ms.

I used this example https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/master/examples/sqlite3_littlefs/sqlite3_littlefs.ino and pointed to my database and added a SQL update

This reproduces the same (slow) SQL update.

So do I have to live woth the slow SQL updates or is there something I can do that will speed the SQL updates up.

As a side note I am definatly an amateur with coding!

Thanks for reading

Garry

siara-cc commented 4 months ago

8 seconds sounds quite high for a 40 row table on LITTLEFS, unless the row length is huge and all rows are updated.

siara-cc commented 4 months ago

I think the issue you are facing is unusual so need more info or a reproducible sample to suggest further opinion.

BackInFiveMinutes commented 4 months ago

Hi Arun

Thanks for taking the trouble to look at this issue

I have created a sample code snippet which is largly based on your littlefs example

In my sample I test two databases

The first, small database has only one table, tblConfig in it and 2 columns. The second is larger, it still has tblConfig in it plus many other tables, which are not used in this sample. Both databases were Vacuumed

I found that wrapping the UPDATE query within a transaction significaly improved the update speed.

What you will see when running the sample code is

  1. The smaller the database the faster the UPDATES are, irrespective of wrapping in a transaction
  2. Wrapping the UPDATE in a transaction is always faster.

So is it best practice to use transactions when updating? (My gut feeling is that is shouldn't matter in this case)

Also do you expect the size of the database to impact the UPDATE, even though the UPDATE is not affecting any of those extra tables?

main.zip platformio.zip data.zip

I've zipped up the code, the two sample databases and the platformm.ini. If there's anything else you need please let me know Again, thanks for you help

Garry

siara-cc commented 4 months ago

@BackInFiveMinutes I am having trouble loading the data onto the board, so unable to test what you have provided.

It may help to try using Prepared Statements instead of plain SQL statements and using a WITHOUT ROWID table. Also the page size used is 4096. Please try using 512 page size (PRAGMA page_size 512; VACUUM;)