[odb-users] Slow select in SQLite

[Lloyd]

Hi,


We have the following two tables created through ODB in an SQLite database


CREATE TABLE "InfoTable" ("ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"Name" TEXT NOT NULL,
"ImageID" INTEGER NOT NULL,
CONSTRAINT "ImageID_fk" FOREIGN KEY("ImageID") REFERENCES
"AddInfoTable"("ImageFileId") ON DELETE CASCADE DEFERRABLE INITIALLY
DEFERRED
);


CREATE TABLE "AddInfoTable" ("ImageFileId" INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,
"ImageFormat" TEXT NOT NULL,
"ImageSize" INTEGER NOT NULL,
"DataTree" TEXT NOT NULL );//JSON - represents a tree


DataTree column of AddInfoTable stores a JSON (As plain text) representing
a hierarchical data structure.  The table contains 0.25 million records.


The following ODB query is used to retrieve the data. It takes almost an
hour to fetch all the data from the table.

typedef odb::query<InfoTable> query;
result r = db.query<InfoTable>();


We have tried the following scenarios to identify the problem


1) Using a DB having only the InfoTable containing 0.25 million records. It
takes less than 1 sec to fetch all the data from the db.

2) The DB consists of 2 tables, InfoTable and AddInfoTable. InfoTable
consists of 3 fields and one of them is the foreign key of AddInfoTable and
the AddInfoTable has 5 fields but no fields holding JSON type value. This
too retrieves the data from the table very fast.

3)Used a JSON of size only 400 bytes (DataTree) and tried to retrieve, it
takes less than 1 sec. only.

4) The query is very slow when the AddInfoTable has the JSON (Data Tree
Column) data of size 10kb/raw on average.

May I know the reason for this slow query? (The same tables and query works
fast on ODB PostgreSQL)

We are using ODB 2.4 on Windows 10 with SQLite version 3.27.2

Thanks a lot in advance,
  Lloyd