Grocy users, update to SQLite 3.50

Numerous bug reports (closed reflexively by the author, Bernd Bestel — with pretexts of either "slow hardware" or "bullshit data entry") exist of Grocy becoming unusably slow.

The bug

Bernd is wrong (he's also rude but that's for another topic).  I've tracked down the issue to a substantial regression present in older versions of SQLite (specifically 3.47, present in Fedora 42 and many other last-generation Linux distributions).   Quoted from the issue:

complex CTE query goes from 1.5s in 3.46.1 to 17s in 3.47.2
(1) By gotson on 2025-01-14 04:35:51 [source]
Hi,

i recently had some reports of poor performance in my application, after some investigation i narrowed it down to the version of SQLite used.

I have a database.sqlite file provided by a client that is ~7GB.

The impacted query is using 2 CTE expressions.

In 3.46.1, the query takes ~1.5s.

In 3.47.2, the same query takes ~17s.

I have ran explain query plan on both versions, and i get a different plan, but i don't know what i can do from there.

I can always remain on 3.46.1 for now, but that won't work in the long term.

What steps could I take in order to try to resolve this ?

I could provide the database file (i would need to ask my user if that is OK to share), as well as the query, if that is something that could help to understand why the 3.47 version doesn't perform as well as 3.46.

This regression impacts complex CTE queries, which Grocy uses in at least one of its database views (recipes_pos_resolved, in turn used by recipes_resolved).  It made the recipes and the meal plan Web views my Grocy setup take in excess of six seconds to load.  Here is the query with Fedora 42's SQLite 3.47:

bash-5.2# sqlite3 new.db
SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 5.989 user 5.737005 sys 0.009360
sqlite> 

The fix

Here is the same query on the same hardware with a Toolbox container of Fedora 43 sporting SQLite 3.50:

bash-5.2# toolbox enter fedora-toolbox-43
⬢ [root@toolbx tmp]# sqlite3 new.db
SQLite version 3.50.2 2025-06-28 14:00:48
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 0.115 user 0.100834 sys 0.007156
sqlite> 

If you have performance issues, then you are strongly recommended to upgrade to SQLite 3.50 or later.

Linuxserver container users of Grocy — currently no fix

Most users are relying on the linuxserver Docker image container for Grocy.  At the time of this writing, the version the container ships of SQLite3 is 3.48, and it is affected by the bug:

bash-5.2# podman exec -it upbeat_bell /bin/sh
root@e2da071c4f01:/# apk list | grep sqlit
WARNING: opening from cache http://dl-cdn.alpinelinux.org/alpine/v3.21/main: No such file or directory
WARNING: opening from cache http://dl-cdn.alpinelinux.org/alpine/v3.21/community: No such file or directory
php83-pdo_sqlite-8.3.19-r0 x86_64 {php83} (PHP-3.01 BSD-3-Clause LGPL-2.0-or-later MIT Zend-2.0) [installed]
sqlite-libs-3.48.0-r4 x86_64 {sqlite} (blessing) [installed]
root@e2da071c4f01:/# exit
bash-5.2# podman exec -it upbeat_bell /bin/sh
root@e2da071c4f01:/# apk add sqlite
s(1/1) Installing sqlite (3.48.0-r4)
Executing busybox-1.37.0-r14.trigger
OK: 88 MiB in 118 packages
liroot@969acf43fec6:/# sqlite3 new.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select count(*) from recipes_resolved;
101
Run Time: real 4.496 user 4.322394 sys 0.049214
sqlite>