SQLite problem

Databases
1

When I run this query in SQLiteManager or my Xojo app,it works.

SELECT A.*, B.UnitNo, B.ReadingID, B.DateTime FROM ExtReadings A LEFT JOIN Readings B ON A.ReadingKey = B.ReadingID WHERE B.DateTime >= ‘2022-08-01 00:00:00’ AND B.DateTime <= ‘2022-08-06 23:59:59’

When I add this to the query, it crashes both SQLiteManager and my Xojo app running in the debugger.

AND B.UnitNo = 16

The database file is 120 MB. When I run the app on a 400 KB database, it works as expected with or without the UnitNo. I am suspecting that the problem relates to memory. I am running Windows 11 with 16 GB of Ram. Xojo is 2019r1.1 and SQLabs’ SQLite Manager is the latest version. Is there a way to either make this work or trap the error so it doesn’t crash the app?

2

Any crash message? Could you zip it and share such crashing DB for inspection?

3

I prefer to be very explicit when I set a query and use ( ). One thing you may try is:

SELECT A.*, B.UnitNo, B.ReadingID, B.DateTime FROM ExtReadings A LEFT JOIN Readings B ON A.ReadingKey = B.ReadingID
WHERE (B.DateTime >= ‘2022-08-01 00:00:00’
AND B.DateTime <= ‘2022-08-06 23:59:59’)
AND B.UnitNo = 16

The single quote may be wrong here, but you get the idea. Sometimes code does not read the same way human do !

4

That’s not a cause for a crash. Maybe DB corruption? I would like to see that DB crashing in a local test.

5

could be some weird character(s) in the 120MB database that are not in the 400K database ?
try to read all datas from the big file to see this ?

6

For a sanity check, try

select * from readings where UnitNo = 16
7

Have you tried this using the sqlite3 command-line-shell app available from the sqlite website for your platform:

https://www.sqlite.org/download.html

8

TimStreaterTesters

Have you tried this using the sqlite3 command-line-shell app available from the sqlite website for your platform:

The command line shell actually works. When I entered the first part of the query without the UnitNo, each record appeared on the screen so fast I couldn’t read them. When I entered the complete query, about 2 records a second appeared on the screen.

Trying the query again in SQLiteManager with LIMIT 33 added at the end, the query runs, but takes 6.030 seconds to run. With about 1000 records, it appears the app may not be crashing; I just have not been waiting long enough.

The database was created in 2014 and the app is written in Xojo 2019r1.1 while the shell is current. The customer has not complained about this, probably because it took so long to fill the listbox for all the logs of meter readings. I discovered this problem when implementing the Lazy Loading Listbox example in my app. Works great now except for this one issue.

9

The reason I mentioned the shell is that the devs’ and others’ advice on the SQLite Users Forum is that, if someone has a problem using SQLite through another language than C (in our case, Xojo), that they try that database/SQL with the shell. If it doesn’t work there, then the devs become interested. Otherwise, they say go see those who wrote the language shim (SQLiteManager or Xojo in this case). The same team wrote and support both the library and the shell, and I find the latter invaluable for testing out (usually small, in my case) bits of SQL.

10

If the issue is related just to speed, maybe it is just a question of proper/better design of the indexes.

Once the design was checked, you could optimize the performance doing 3 commands:

REINDEX;
ANALYZE;
VACUUM;

Be sure to understand the implications of such “rebuild”. The most important is that it may affect the logic of systems when some people decided not creating rowids for some tables and deleted records afterwards. After a VACUUM the physical rowids may change because those rows marked as “deleted” will be really removed.

12

I rewrote the query to optimize it for the index (on UnitNo and DateTime)

SELECT B.UnitNo, B.DateTime, B.ReadingID, A.* FROM Readings B LEFT JOIN ExtReadings A ON B.ReadingID = A.ReadingKey WHERE B.UnitNo = 16 AND B.DateTime >= ‘2022-08-01 00:00:00’ AND B.DateTime <= ‘2022-08-06 23:59:59’
It still takes about 45 seconds to return 340 rows, but if I remove the UnitNo from the query, it returns about 3500 rows in 1.7 seconds. I can loop though the record set and just use the records desired.

I tried the query below and it took 18.6 seconds to return.

SELECT * FROM (SELECT B.UnitNo, B.DateTime, B.ReadingID, A.* FROM Readings B LEFT JOIN ExtReadings A ON B.ReadingID = A.ReadingKey WHERE B.DateTime >= ‘2022-08-01 00:00:00’ AND B.DateTime <= ‘2022-08-06 23:59:59’) WHERE UnitNo = 16

Thanks for all the suggestions.

13

Questions:

Do you really need A.* or just few columns? If fewer columns, try listing them and observe if you get some extra gains.

The second construct being faster seems like the SQLite planner (part of the engine) may need more improvements.

Based on your finding, does this small change makes some positive difference?

SELECT B.UnitNo, B.DateTime, B.ReadingID, A.* FROM Readings B LEFT JOIN ExtReadings A ON B.ReadingID = A.ReadingKey WHERE (B.DateTime >= ‘2022-08-01 00:00:00’ AND B.DateTime <= ‘2022-08-06 23:59:59’) AND B.UnitNo = 16

The huge increase of time just adding UnitNo still makes no sense, I guess that your query under normal conditions should run under 5 seconds. Something weird is going on here like the planner abandonning the indexes and making a sequential approach.

You may understand what SQLite does asking it to EXPLAIN your query for optimization: EXPLAIN QUERY PLAN