[odb-users] Slow select in SQLite

[Boris Kolpackov]

Lloyd <lloydkl.tech at gmail.com> writes:

> 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.


So the database size in case (4) is 250,000 * 10KB = ~2.5GB. It shouldn't
cause any issues on moderns hardware (with SSDs, etc), but it's not exactly
nothing. But still, 1h sounds abnormal.


> 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

I am 99% certain this is not about ODB but about SQLite and/or Windows.
One thing that I would try is make the column just TEXT, not JSON, to
eliminate any (remote) possibility of some JSON validation overhead.

Another thing worth trying would be to switch to an in-memory SQLite
database and see what kind of numbers you get. If it's fast, then it's
definitely not ODB and I would suggest asking on the SQLite mailing
lists/forums.