Improve database relation & rollup field loading performance

[cracra-droid]

Database with relation fields & rollups very slow to interact with

Is there an existing issue for this?

• I have searched the existing issues

Can the issue be reproduced with the default theme (daylight/midnight)?

• I was able to reproduce the issue with the default theme

Could the issue be due to extensions?

• I've ruled out the possibility that the extension is causing the problem.

Describe the problem

When adding a relation column, interacting with the database becomes very slow. Everything is affected, any column input, setting filters/sorts, etc.

• hiding the relation columns in the current view doesn't increase speed
• modifying properties inside a document, not in the table view, is affected as well
• more relation fields there are, the slower the database becomes, to the point of becoming unusable.

Removing relation columns increases speed greatly, so the problem doesn't come from other columns or the number of rows in the database.

I've provided a screen recording from a sample database, but the problem gets even worse is some databases with more rows/more relation columns.

EDIT: All of the above seems to apply to rollup columns as well.

Expected result

• existence of relation columns doesn't affect speed of interacting with other columns
• hiding columns in the current view increases speed, if their content is not used in any way in this view (ex. in a filter/sort)
• even if the table view slows down, editing properties on a single document is faster

Screenshot or screen recording presentation

Enregistrement.de.l.ecran.2024-10-19.105637.mp4

Version environment

- Version: 3.1.9
- Operating System: Windows 11
- Browser (if used):

Log file

I 2024/10/19 10:57:04 repository.go:1285: syncing data repo [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d, kernel=vqh16i5, provider=0, mode=a/false]
I 2024/10/19 10:57:04 ref.go:50: got local latest [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=6b9cc9d8f1f873bb67643b0789f39b42f2f65649, files=2445, size=231.2 MB, created=2024-10-19 10:54:30]
I 2024/10/19 10:57:04 repo.go:591: walk data [files=2445] cost [72.2593ms]
I 2024/10/19 10:57:04 ref.go:50: got local latest [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=6b9cc9d8f1f873bb67643b0789f39b42f2f65649, files=2445, size=231.2 MB, created=2024-10-19 10:54:30]
I 2024/10/19 10:57:05 ref.go:64: updated local latest to [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=8041b29ea488d1a6efb6d0bb2225d6df44c212c1, files=2445, size=231.16 MB, created=2024-10-19 10:57:05]
I 2024/10/19 10:57:06 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/lock-sync]
I 2024/10/19 10:57:06 ref.go:50: got local latest [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=8041b29ea488d1a6efb6d0bb2225d6df44c212c1, files=2445, size=231.16 MB, created=2024-10-19 10:57:05]
I 2024/10/19 10:57:06 siyuan.go:138: downloaded object [siyuan/1728685071796/repo/main/refs/latest]
I 2024/10/19 10:57:07 siyuan.go:138: downloaded object [siyuan/1728685071796/repo/main/indexes/6b9cc9d8f1f873bb67643b0789f39b42f2f65649]
I 2024/10/19 10:57:07 sync.go:1581: got cloud latest [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=6b9cc9d8f1f873bb67643b0789f39b42f2f65649, files=2445, size=231.2 MB, created=2024-10-19 10:54:30]
I 2024/10/19 10:57:07 sync.go:1325: upsert file [12722cb5fa9ef70f7b5f9516e13da198abef371e, /storage/av/20241019085650-y83d8g9.json, 2024-10-19 10:56:30] chunk [fc0325cad789ac51964815d50b06d866f7793d34]
I 2024/10/19 10:57:07 sync.go:1325: upsert file [2dcab19d1189c3382241b635bf9062648fb83722, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019084811-17p4dzx.sy, 2024-10-19 10:56:30] chunk [86595b938e5a9d08761335a1c067b6f972e521fd]
I 2024/10/19 10:57:08 sync.go:1325: upsert file [eda2cc450d84cc5f5288294e246d4a2a30b6697e, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019103405-1wiuk13.sy, 2024-10-19 10:55:15] chunk [748dffb4e46bd6b69af683a746c8d28d63e24c5f]
I 2024/10/19 10:57:08 sync.go:1341: upsert chunk [fc0325cad789ac51964815d50b06d866f7793d34]
I 2024/10/19 10:57:08 sync.go:1341: upsert chunk [86595b938e5a9d08761335a1c067b6f972e521fd]
I 2024/10/19 10:57:08 sync.go:1341: upsert chunk [748dffb4e46bd6b69af683a746c8d28d63e24c5f]
I 2024/10/19 10:57:08 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/74/8dffb4e46bd6b69af683a746c8d28d63e24c5f]
I 2024/10/19 10:57:09 sync.go:1261: uploaded chunk [objects/74/8dffb4e46bd6b69af683a746c8d28d63e24c5f, 1/3]
I 2024/10/19 10:57:09 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/86/595b938e5a9d08761335a1c067b6f972e521fd]
I 2024/10/19 10:57:09 sync.go:1261: uploaded chunk [objects/86/595b938e5a9d08761335a1c067b6f972e521fd, 2/3]
I 2024/10/19 10:57:09 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/fc/0325cad789ac51964815d50b06d866f7793d34]
I 2024/10/19 10:57:09 sync.go:1261: uploaded chunk [objects/fc/0325cad789ac51964815d50b06d866f7793d34, 3/3]
I 2024/10/19 10:57:10 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/2d/cab19d1189c3382241b635bf9062648fb83722]
I 2024/10/19 10:57:10 sync.go:1207: uploaded file [objects/2d/cab19d1189c3382241b635bf9062648fb83722, 1/3]
I 2024/10/19 10:57:10 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/12/722cb5fa9ef70f7b5f9516e13da198abef371e]
I 2024/10/19 10:57:10 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/objects/ed/a2cc450d84cc5f5288294e246d4a2a30b6697e]
I 2024/10/19 10:57:10 sync.go:1207: uploaded file [objects/ed/a2cc450d84cc5f5288294e246d4a2a30b6697e, 3/3]
I 2024/10/19 10:57:10 sync.go:1207: uploaded file [objects/12/722cb5fa9ef70f7b5f9516e13da198abef371e, 2/3]
I 2024/10/19 10:57:10 sync.go:221: got local latest [8041b29ea488d1a6efb6d0bb2225d6df44c212c1] files [2445]
I 2024/10/19 10:57:11 sync.go:1454: got latest sync [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=6b9cc9d8f1f873bb67643b0789f39b42f2f65649, files=2445, size=231.2 MB, created=2024-10-19 10:54:30]
I 2024/10/19 10:57:11 sync.go:228: got latest sync [6b9cc9d8f1f873bb67643b0789f39b42f2f65649] files [2445]
I 2024/10/19 10:57:11 diff.go:49: upsert [lID=df49321ec9bb259c8a651ffd604460f89b85a4ce, lPath=/storage/av/20241019085650-y83d8g9.json, lUpdated=2024-10-19 10:51:03, rID=12722cb5fa9ef70f7b5f9516e13da198abef371e, rPath=/storage/av/20241019085650-y83d8g9.json, rUpdated=2024-10-19 10:56:30]
I 2024/10/19 10:57:11 diff.go:49: upsert [lID=6f96bf9926dc37becb0fdd575bc877da871692e9, lPath=/20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019103405-1wiuk13.sy, lUpdated=2024-10-19 10:34:06, rID=eda2cc450d84cc5f5288294e246d4a2a30b6697e, rPath=/20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019103405-1wiuk13.sy, rUpdated=2024-10-19 10:55:15]
I 2024/10/19 10:57:11 diff.go:49: upsert [lID=2b7a005b388e592604a4b512458cb7854fb9ccdf, lPath=/20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019084811-17p4dzx.sy, lUpdated=2024-10-19 10:53:54, rID=2dcab19d1189c3382241b635bf9062648fb83722, rPath=/20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019084811-17p4dzx.sy, rUpdated=2024-10-19 10:56:30]
I 2024/10/19 10:57:11 sync.go:239: cloud upsert [df49321ec9bb259c8a651ffd604460f89b85a4ce, /storage/av/20241019085650-y83d8g9.json, 2024-10-19 10:51:03]
I 2024/10/19 10:57:11 sync.go:239: cloud upsert [6f96bf9926dc37becb0fdd575bc877da871692e9, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019103405-1wiuk13.sy, 2024-10-19 10:34:06]
I 2024/10/19 10:57:11 sync.go:239: cloud upsert [2b7a005b388e592604a4b512458cb7854fb9ccdf, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019084811-17p4dzx.sy, 2024-10-19 10:53:54]
I 2024/10/19 10:57:11 sync.go:245: local upsert [eda2cc450d84cc5f5288294e246d4a2a30b6697e, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019103405-1wiuk13.sy, 2024-10-19 10:55:15]
I 2024/10/19 10:57:11 sync.go:245: local upsert [12722cb5fa9ef70f7b5f9516e13da198abef371e, /storage/av/20241019085650-y83d8g9.json, 2024-10-19 10:56:30]
I 2024/10/19 10:57:11 sync.go:245: local upsert [2dcab19d1189c3382241b635bf9062648fb83722, /20241008164550-mahlmyv/20241010163144-if3nrd2/20241010163413-w95utbg/20241019084811-13hho0k/20241019084811-17p4dzx.sy, 2024-10-19 10:56:30]
I 2024/10/19 10:57:11 siyuan.go:138: downloaded object [siyuan/1728685071796/repo/main/refs/latest]
I 2024/10/19 10:57:12 siyuan.go:138: downloaded object [siyuan/1728685071796/repo/main/indexes-v2.json]
I 2024/10/19 10:57:12 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/indexes/8041b29ea488d1a6efb6d0bb2225d6df44c212c1]
I 2024/10/19 10:57:12 sync.go:1172: uploaded index [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=8041b29ea488d1a6efb6d0bb2225d6df44c212c1, files=2445, size=231.16 MB, created=2024-10-19 10:57:05]
I 2024/10/19 10:57:12 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/check/indexes/09ac7d3271815f12a449ce07b56095cc2fe09f64]
I 2024/10/19 10:57:12 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/indexes-v2.json]
I 2024/10/19 10:57:13 siyuan.go:112: uploaded object [siyuan/1728685071796/repo/main/refs/latest]
I 2024/10/19 10:57:13 siyuan.go:138: downloaded object [siyuan/1728685071796/repo/main/refs/latest]
I 2024/10/19 10:57:13 sync.go:1382: updated latest sync [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d/windows, id=8041b29ea488d1a6efb6d0bb2225d6df44c212c1, files=2445, size=231.16 MB, created=2024-10-19 10:57:05]
I 2024/10/19 10:57:14 siyuan.go:173: removed object [siyuan/1728685071796/repo/main/lock-sync]
I 2024/10/19 10:57:14 repository.go:1353: synced data repo [device=0d85a7df-dec6-45b6-83e2-2786bb9f637d, kernel=vqh16i5, provider=0, mode=a/false, ufc=6, dfc=3, ucc=3, dcc=0, ub=113.07 kB, db=146.88 kB] in [10.17s], merge result [conflicts=0, upserts=0, removes=0]

More information

No response

[88250]

If it is convenient, please use the latest dev version to export the document as .sy.zip so that we can debug it. Thank you.

[cracra-droid]

Thank you for you quick response!

I've got the app from the windows app store, so I'm not sure if it's possible/how to install the dev version?
Here's the .sy.zip document from v3.1.9.

DB speed tests.sy.zip

[88250]

Thank you, we will check the bottleneck later.

[88250]

Hello, we have made a performance optimization. Please install v3.1.10-dev5 for testing later (it is still under construction and needs to wait for the construction to be completed https://github.com/siyuan-note/siyuan/actions/runs/11416975432).

Thank you!

[5kyfkr]

Hello, we have made a performance optimization. Please install v3.1.10-dev5 for testing later (it is still under construction and needs to wait for the construction to be completed https://github.com/siyuan-note/siyuan/actions/runs/11416975432).

Thank you!

Wow, this improvement is so fast. It's just like the AE86 in Initial D.

[cracra-droid]

Hello, we have made a performance optimization. Please install v3.1.10-dev5 for testing later (it is still under construction and needs to wait for the construction to be completed https://github.com/siyuan-note/siyuan/actions/runs/11416975432).

Thank you!

Wow indeed! It works perfectly now, thank you 🙏