Improve SQL performance #299
Assignees
Labels
Comments
|
Perhaps an option would be to use "better-sqlite3" in combination with TypeORM? better-sqlite3 seems to perform much faster than sqlite3 and in combination with TypeORM it seems to be simply a matter of switching out the npm package and TypeORM connection option from sqlite3 to better-sqlite3. |
|
I have just tested the idea and it is simply a matter of switching out the existing sqlite3 dependency for better-sqlite3 (tested version 7.4.4). Since TypeORM is already compatible with better-sqlite3, you can then simply change the driver from The positive performance impact is massive, especially on my database with ~150.000 images. better-sqlite3 is compatible to any existing sqlite database files, so it should really be as simple as that. On another note, I'd also update TypeORM itself to a more recent version, but I haven't tested whether a TypeORM update breaks anything yet. |
|
This sounds great! I already have some benchmark (that code is only best-effort maintained): https://github.com/bpatrik/pigallery2/tree/master/benchmark I would need to run that to compare the benefit. |
This comment was marked as outdated.
This comment was marked as outdated.
bpatrik
added a commit
that referenced
this issue
Feb 22, 2022
bpatrik
added a commit
that referenced
this issue
Feb 22, 2022
|
The previous benchmark result was corrupted so I had to rerun it. There is a 10-12% increase with PiGallery2 v1.9.1, 23.02.2022Version: v1.9.1, built at: Tue Feb 22 2022 23:16:30 GMT+0000 (Coordinated Universal Time), git commit:b4c64d12472bb618787168009eae103d59487ded Gallery: directories: 31, photos: 2036, videos: 35, diskUsage : 22.08GB, persons : 1381, unique persons (faces): 25
run for : 2696203.0ms |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Listing and searching is relatively slow currently, see: https://github.com/bpatrik/pigallery2/blob/master/benchmark/README.md.
Did some minimal local benchmarking and found that Issuing a
SELECT * FROM media_entrytakes <30ms from DB Browser from SQLite, while from code (even if I list raw results, not parsed JSON) it could take 300ms.This is without getting all the relations (directory, faces, etc) and doing any
whereor sorting.A more realistic query:
In the DB app: 300ms, from nodejs: 7300ms (first run), 1000ms (following runs)
(The long
facesconcatenation select is there as I figured that returning with less rows has 30-40% better performance.)I did the check on a DB with 21k photos, win 10, i7-6700HQ, with SSHD.
There is clearly some significant performance loss around typerom.
There is already some harsh thread about it on reddit: TypeORM Sucks!! Something I wanted to talk about since long!
Currently I see two alternatives:
Blocking:
The text was updated successfully, but these errors were encountered: