d12frosted / vulpea

A collection of functions for note taking based on `org` and `org-roam`.
GNU General Public License v3.0
236 stars 12 forks source link

Use single sql query in db-query-*-some functions #129

Open d12frosted opened 2 years ago

d12frosted commented 2 years ago

See explanation in this thread.

Basically, the following implementation works 1.55 times faster than the current implementation:

(defun vulpea-db-query-by-links-some (destinations)
  "Query a list of `vulpea-note' from database.

Only notes that link to at least one destination from the list of
DESTINATIONS are returned."
  (emacsql-with-transaction (org-roam-db)
    (vulpea-db-query-by-ids
     (seq-map
      #'car
      (org-roam-db-query
       (format "select distinct source from links where dest in %s"
               (emacsql-escape-vector
                (apply #'vector (seq-map #'cdr destinations)))))))))

(defun vulpea-db-query-by-links-some-single (destinations)
  "Query a list of `vulpea-note' from database.

Only notes that link to at least one destination from the list of
DESTINATIONS are returned."
  (seq-mapcat
   #'vulpea-db--notes-from-row
   (org-roam-db-query
    (format
     "SELECT * FROM notes WHERE id IN (SELECT source FROM links WHERE links.type = '\"id\"' AND links.dest IN %s);"
     (emacsql-escape-vector
      (apply #'vector (seq-map #'cdr destinations)))))))

Benchmark result:

[00:20.629]  [vulpea-db-query-by-links-some-single] begin benchmark with 10 invocations
[00:22.870]  [vulpea-db-query-by-links-some-single] benchmark result is (2.2408159999999997 34 1.3603679999999994) after 10 invocations => 1767 notes
[00:22.870]  [vulpea-db-query-by-links-some] begin benchmark with 10 invocations
[00:26.362]  [vulpea-db-query-by-links-some] benchmark result is (3.492386 49 2.1438930000000003) after 10 invocations => 1767 notes
[00:26.362]  [vulpea-db-query] begin benchmark with 10 invocations
[00:36.941]  [vulpea-db-query] benchmark result is (10.578342000000001 65 5.856387000000002) after 10 invocations => 1767 notes