SQL Updates

[BackInFiveMinutes]

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]

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]

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

[BackInFiveMinutes]

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]

@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;)

[trianglesis]

page_size

Hello,

Can you please point me in the right direction?
I'm having issues with setting page_size:

I'm using two approaches, neither of which is working.
As soon as the database is created, and I'm checking it in DB Browser (downloaded from ESP32 via file server to my PC), it shows page_size 4096

1. Set #define SQLITE_DEFAULT_PAGE_SIZE 512 at the header of my SQL driver file, where I include #include "sqlite3.h" and #include "sqllib.h", later I can see #define SQLITE_DEFAULT_PAGE_SIZE 512 in managed_components\nopnop2002__sqlite3\config_ext.h file, which is fine.

2. Set the query: PRAGMA page_size=512; VACUUM; as soon as the newly created DB is opened, before any table creation.
   Literally: rc = db_query(xMessageBufferQuery, db, "PRAGMA page_size=512; VACUUM;");

I was trying actually delete the file via file browser and programmatically with unlink(db_name); - right before init.

And no effect.

PRAGMA main.page_size = 1024;
PRAGMA main.page_size;

[siara-cc]

@trianglesis Have you tried setting the PRAGMA page_size=512 just before you do CREATE TABLE? There have been issues reported about doing VACUUM because of limited memory.

[trianglesis]

@trianglesis Have you tried setting the PRAGMA page_size=512 just before you do CREATE TABLE? There have been issues reported about doing VACUUM because of limited memory.

    char db_name[32];
    snprintf(db_name, sizeof(db_name)-1, "%s/stats.db", DB_ROOT);
    // Open database
    sqlite3 *db;
    int rc = db_open(db_name, &db); // will print "Opened database successfully"
    if (rc != SQLITE_OK) {
        ESP_LOGE(TAG, "Cannot open database: %s resp: %d", db_name, rc);
        vTaskDelete(NULL);
    } else {
        ESP_LOGI(TAG, "Opened database: %s resp: %d", db_name, rc);
    }

    // Set page size, read page size: "PRAGMA page_size;"
    // Inquiry
    rc = db_query(xMessageBufferQuery, db, "PRAGMA page_size=512; VACUUM;");
    if (rc != SQLITE_OK) {
        ESP_LOGE(TAG, "Set PRAGMA page_size FAILED!");
    }

Yes, I ran this query from the very beginning.
I also deleted the DB file from the SD Card before this routine by unlink(db_name);

Later, I downloaded a freshly created DB and checked its pragmas in SQL Browser.

[siara-cc]

@trianglesis Can you try with Arduino IDE? I have not used other IDEs. I do remember changing the default to 512 and saw that it took effect. So there should not be a need to use PRAGMA and VACUUM.

[trianglesis]

@trianglesis Can you try with Arduino IDE? I have not used other IDEs. I do remember changing the default to 512 and saw that it took effect. So there should not be a need to use PRAGMA and VACUUM.

Thanks,
I'll stick with IDF anyway.

Strangely, this fork I'm using, as the author told me, only modified compilation and build methods without major changes in the code itself.

sqlite3 for esp-idf.
The original is [here](https://github.com/siara-cc/esp32-idf-sqlite3).
Although it is a very nice library, it cannot be built with ESP-IDF Ver5.
Therefore, We changed it so that it can also be used with ESP-IDF Ver5.
At the same time, we also changed the sample code so that it can be used with ESP-IDF Ver5.

I will ask the author. Thanks.

[siara-cc]

@trianglesis

esp32_arduino_sqlite3_lib/src/config_ext.h

Line 27 in 10fb9cf

#define SQLITE_DEFAULT_PAGE_SIZE 512

The default page size is 512 as set in the line shown above. If a new table is created with this setting the PRAGMA and VACUUM are not needed.