Slow execution (sqlite3_step(res) == SQLITE_ROW)

[pyrotechnik]

Hello, I'm using an ESP32S3 and an SD card connected to it via MMC. I am having a problem with the execution time of certain database dumps. Some requests where I read one row from a database that has 20 rows takes about 12ms, but reading the last row from a database that has 10000 rows takes 1128ms. Which is a problem.

     sprintf (s, "SELECT * FROM dataTable WHERE sn=\'%s\' ORDER BY date DESC LIMIT 1", pomSN);

     if (openDb("/sd/data.db", &db1))
        return;

      rc = sqlite3_prepare_v2(db1, s, -1, &res, &tail);
      if (rc != SQLITE_OK) {
        Serial.println(sqlite3_errmsg(db1));
        return;
      }

      time_start = millis();
      while (sqlite3_step(res) == SQLITE_ROW) {
        jsonTxt.printf(",\"date\":\"%s\"", (const char *) sqlite3_column_text(res, 1));
        if (strcmp(pomCubeID, "85") == 0){
          if (strcmp(sensorPosition, "name_1") == 0){
            jsonTxt.printf(",\"value\":\"%s\"", (const char *) sqlite3_column_text(res, 2));
          } else if (strcmp(sensorPosition, "name_2") == 0){
            jsonTxt.printf(",\"value\":\"%s\"", (const char *) sqlite3_column_text(res, 3));
          } else if (strcmp(sensorPosition, "name_out_1") == 0){
            jsonTxt.printf(",\"value\":\"%s\"", (const char *) sqlite3_column_text(res, 5));
          } else if (strcmp(sensorPosition, "name_out_2") == 0){
            jsonTxt.printf(",\"value\":\"%s\"", (const char *) sqlite3_column_text(res, 6));
          }
        }
      }

In the code I attached, all the statements that are inside the while only take 4ms. But the entire while loop, which is only executed once since I'm reading a single row, takes over 1s. Even if I put if instead of while. I know I could have used switch instead of if else, but this is still a skeleton program that I am constantly tweaking.

I would be very grateful for any advice as this is quite a hurdle.

[siara-cc]

Hi, In general creating a composite index on SN and DATE should make this faster. However, it will have to be tested overall to make sure that it does not cause out of memory issues either during insertion or retrieval on a continuous basis.

You could try this on a desktop with EXPLAIN PLAN for the same SQL to make sure SQLite uses the index that was created if it does not work.

It takes time on sqlite3_step because that is where the real processing of SQL happens.

[pyrotechnik]

Thank you, it really helped. The time was reduced from 1000+ ms to 6ms.