In short - no news 
Right now I am focused on improving documentation of vulpea (you can see the progress in vulpea#127) and then I will have time to backport view table approach. But! I will not start without approval from @jethro. I have limited availability and don’t want to waste effort on PR that has little chances of being merged 
But if @jethro gives a green light I will be happy to prepare it And let me know if you need more context/explanations.
@d12frosted I have no idea what view tables you have in mind, will there be a performance improvement within Org-roam itself? I can see view tables coming in handy when we fetch node completions, but the view tables will need to constantly be updated on write?
@jethro Yes, there is performance improvement. It is described in vulpea#116. I mentioned you there And I believe it will improve performance of org-roam itself.
@d12frosted i’m looking at the db sync benchmarks and it looks like sync in general has some degradation? My concern is what it speeds up as a tradeoff: there seems to be benchmarks on tags-some, links-every etc. but I don’t know what these queries are and whether they are “specialized queries” that are not used within Org-roam.
I’m welcome to any PR that introduces perf improvements without significant overhead, i’m just still not sure what I’m looking at 
1 Like
Sync degradation happens because I duplicate buffer parsing. I tried to do it in a non-intrusive manner, eg without replacing org-roam sync routine. If done in place, overhead is negligible.
I will explain it in more details to you in the upcoming days. Hopefully will have some free time on weekends.
2 Likes
@d12frosted Have you thought about using views, which are virtual tables instead? I was about to try vulpea but stopped because building database of 7833 nodes would take hours on my machine.
Never used them. Will take a look if it improves anything. Not clear to me if they provide any performance improvement for my use-case.
I was about to try vulpea but stopped because building database of 7833 nodes would take hours on my machine.
Wow, that means that even with plain org-roam it takes hours/2 to build db, right? Current implementation of this view table just doubles the time. And I wonder why it takes so long. Even on my wooden laptop it takes around 10 minutes to build 8500 notes.
I’ve read a little bit about view table and seems like it’s not solving performance like a dedicated materialized view table does. Please correct me if I am wrong, but it seems like views simply allow to abstract away some query, but under the hood it always runs the abstracted query. Meaning that view over heavy query will still run the heavy part each time you query, meaning there is no performance improvement.
What I wanted to achieve with materialized view table is performance of query operation, so multiplication of N tables is not performed every time I query something, but rather on write.
Sync performance degradation comes from the fact that it’s done in a non-obtrusive way to avoid clashes with org-roam. If merged to org-roam performance degradation will go away
@d12frosted you are right. sorry for recommending something without looking deeply at it.
I will investigate why building the database is slow for me. I was under the impression that was also the case for others.
Assuming build time of 10 mins, the initial build can be sped up for people that have an up-to-date org-roam db by constructing the view table directly from the database. I haven’t verified but I imagine it is possible to update the db while utilizing entries as they are entered and in general using emacs in a separate emacs session.
Chiming in the discussion, org-roam-search now supports v2. It sorts and filters nodes using the current prompt through sql clauses order-by and where respectively within org-roam-search and limits results to around 200. sorting and filtering must be done on the whole database not partial results to preserve accuracy. I am usually looking for a few nodes therefore limiting results doesn’t impact my usage.
the latest org-roam-search supports
- titles
- level
- tags
- olp
filters are constructed in org-ql syntax. It integrates with delve using delve-show. one of these days I will write a doc.
If looking to build upon sexp-string as a query language library, Beside requiring a proper development process (like testing), I think its interface as a library should be improved. For example, org-roam-search calls function generating functions defined in sexp-string to create bespoke string to s-exp mapper and s-exp to sql clause transformer. Parsing depends on data stored in org-roam-search-predicate. org-roam-search-predicate dictates the behavior of the bespoke functions. Modifying it changes the behavior of the bespoke functions on the fly. There are currently no functions that modify the predicate variable. Do you guys have suggestions? Maybe a new thread is best lest this thread is derailed.
No worries. Since I never used virtual tables in SQL, didn’t really even know what this feature brings.
Assuming build time of 10 mins, the initial build can be sped up for people that have an up-to-date org-roam db by constructing the view table directly from the database. I haven’t verified but I imagine it is possible to update the db while utilizing entries as they are entered and in general using emacs in a separate emacs session.
Yes, it’s possible. But then it requires mimicking even more of the db sync routine from org-roam, e.g. how would you know that you need to sync something to view table? By having a separate table that stores hash of the file, for example. But that’s what org-roam already does 
Sent a proposal here - Materialized view proposal · Issue #1997 · org-roam/org-roam · GitHub
2 Likes