Reduce the time needed for a database to be read

Question

I'm writing a program in which I have to read a huge amount of data from a database (Sqlite) and then display the obtained values in a window. I need to refresh the windows each second, so the operation of reading the all data need to last less then a second.

The database is composed by two columns a primary key and a value I want to display (a number), the primary key is simply a progressive number that goes from 1 to the number of rows. The primary key is not important for me as far the retrived data are sorted in a vector/struct.

The database has about 8*10^7 rows and it's size on the disk is about 250MB.

I start with the idea that with transaction I can reduce the time needed to read the data, I try to implement a small program and I measure the time, it was about 20 sec. Then I understand that transaction are not very effective with select statement (Here)

So I try with:

using Record = std::vector<std::string>;
using Records = std::vector<Record>;

int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names)
{
  Records* records = static_cast<Records*>(p_data);
  try {
    records->emplace_back(p_fields, p_fields + num_fields);
  }
  catch (...) {
    // abort select on failure, don't let exception propogate thru sqlite3 call-stack
    return 1;
  }
  return 0;
}

Records select_stmt(const char* stmt)
{
  Records records;  
  char *errmsg;
  int ret = sqlite3_exec(db, stmt, select_callback, &records, &errmsg);
  if (ret != SQLITE_OK) {
    std::cerr << "Error in select statement " << stmt << "[" << errmsg << "]\n";
  }
  else {
    std::cerr << records.size() << " records returned.\n";
  }

  return records;
}

Records records = select_stmt("SELECT * FROM TABLE");

I have fastest results (about 14sec) but It was not enough.

Is it possible to reach such reading time (less than 1 sec)?

OS: Ubuntu 20.10 Language: C++

The code I used to get the data with prepare and transaction statement

std::string msg = "SELECT ZPOS FROM TABLE WHERE ID = ?";

const char* msg_char_ptr = msg.c_str();

memset(sSQL, '\0', BUFFER_SIZE);

sprintf(sSQL, msg_char_ptr);

int rc = sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

int count = 0;

if(rc == SQLITE_OK ) {

    sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

    for (int i = 0; i < number_of_rows; i++)
    {

        // indices vector with all the indices of the table
        sqlite3_bind_int(stmt, 1, indices[i]); 

        while ( sqlite3_step( stmt ) == SQLITE_ROW ) { 

            int result = sqlite3_column_int( stmt, 0 );
            
            // collectData vector with the result
            collectData[count] = result;

            int val = sqlite3_column_count( stmt );

            count ++;

        }

        sqlite3_step(stmt);
        sqlite3_clear_bindings(stmt);
        sqlite3_reset(stmt);
    }

} else {

    printf("SQLite prepare error.\n");

}       

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_finalize(stmt);

Start by using the normal [prepared statement](https://www.sqlite.org/c3ref/stmt.html) api instead of `sqlite3_exec()`. — Shawn, Jan 30 '21 at 18:56
I tried it with, but I've not better results ( I used sqlite3_exec("SELECT ZPOS FROM PLANT_DATA WHERE ID = ?") ) moreover https://stackoverflow.com/questions/1703203/in-sqlite-do-prepared-statements-really-improve-performance — Fra, Jan 30 '21 at 20:36
Can you provide the exact query? My best guess is that you should add an index on the second column. If you're applying a `WHERE` clause or `ORDER BY` clause using the second column, indexing that column would make it [millions of times faster](https://sqlite.com/queryplanner.html#_lookup_by_index) to read from. — Max Shenfield, Jan 30 '21 at 21:38
I tried to apply an index on the first column but the performances were the same... — Fra, Jan 30 '21 at 22:10
Do you actually need to read all the data every second? Your window probably doesn't display 10 million numbers all at once. — Igor Tandetnik, Jan 30 '21 at 22:56
Hey Fra - thanks for providing the query. I can see why an index wouldn't help - it's using the primary key (`ID`) to look up the second column value, which already has an index on it. A significant optimization would be to fetch all the `ZPOS` values in a single query (`SELECT ZPOS FROM TABLE`), rather than executing a fetch for each row (`SELECT ZPOS FROM TABLE WHERE ID = ?`). That should lower the query time to about a second. On the side, what are you doing with the values once you get them? — Max Shenfield, Jan 30 '21 at 23:33

score 1 · Accepted Answer · answered Jan 31 '21 at 16:43

If you must SELECT many rows with SQL, your best bet is to do it with one SQL SELECT statement to get all the rows you want, in a multi-row result set. Instead of issuing one SELECT for each ID value you want, issue SELECT ID, ZPOS FROM TABLE` and set up your code to read multiple rows.

That being said, I guess you're trying to read 80 megarows (250MiB) from your table into RAM in your app once a second. That's too much data to read every second. Even if you wrote your C++ code to simply open a flat file and read 250MiB, doing it once a second would be too often.

Some design points to keep in mind:

There's no magic in SQL that makes it super fast, faster than file-reading, to access mass quantities of data.
The point of SQL is to allow programs like yours to handle very large sets of data a few rows at a time. You certainly can do stuff like
```
SELECT ID, ZPOS FROM TABLE WHERE ID > ?
```
where the value you put in ? with binding is the largest ID value you received last time you read the database. That gets you the new records.
With respect, you need to design your program to cache the data from your table in RAM and use the cache effectively. Reloading all the data once a second is too often.

Thank you, after some tests I reach the same conclusions. – Fra Feb 01 '21 at 21:07 — Fra, Feb 01 '21 at 21:07

Reduce the time needed for a database to be read

1 Answers1