Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
d12frosted opened this issue Dec 5, 2021 · 0 comments
Open

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

d12frosted opened this issue Dec 5, 2021 · 0 comments
Labels
enhancement New feature or request good first issue Good for newcomers

Comments

@d12frosted
Copy link
Owner

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
@d12frosted d12frosted added enhancement New feature or request good first issue Good for newcomers labels Dec 5, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request good first issue Good for newcomers
Projects
None yet
Development

No branches or pull requests

1 participant