diff options
author | Christopher Baines <mail@cbaines.net> | 2023-05-17 16:57:09 +0100 |
---|---|---|
committer | Christopher Baines <mail@cbaines.net> | 2023-05-17 16:57:09 +0100 |
commit | acd62a5c08d6bace08339b5250367f715aea9b53 (patch) | |
tree | 22d16fd74c8f453a4b83b306eb016793a1d51eb0 /guix-build-coordinator/datastore/sqlite.scm | |
parent | e89e97ccc91cdcf7fe0195cd34c0231404f10abd (diff) | |
download | build-coordinator-acd62a5c08d6bace08339b5250367f715aea9b53.tar build-coordinator-acd62a5c08d6bace08339b5250367f715aea9b53.tar.gz |
Speed up listing builds
Remove the complicated JOIN on tags and just use subqueries, also remove a
redundant join on derivations and don't order the results.
Diffstat (limited to 'guix-build-coordinator/datastore/sqlite.scm')
-rw-r--r-- | guix-build-coordinator/datastore/sqlite.scm | 39 |
1 files changed, 15 insertions, 24 deletions
diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm index fc9b9df..db9508b 100644 --- a/guix-build-coordinator/datastore/sqlite.scm +++ b/guix-build-coordinator/datastore/sqlite.scm @@ -2371,12 +2371,11 @@ SELECT id FROM tags WHERE key = :key" (match (sqlite-step-and-reset statement) (#(id) - (simple-format - #f "tag_string ~A '%,~A,%'" - (if not? - "NOT LIKE" - "LIKE") - id)) + (string-append + (if not? "NOT " "") + "EXISTS (SELECT 1 FROM build_tags WHERE build_id = builds.id AND tag_id = " + (number->string id) + ")")) (#f #f))) (key (sqlite-bind-arguments key-statement @@ -2390,14 +2389,15 @@ SELECT id FROM tags WHERE key = :key" (string-append "(" (string-join - (map (lambda (id) - (simple-format - #f "tag_string ~A '%,~A,%'" - (if not? - "NOT LIKE" - "LIKE") - id)) - tag-ids) + (map + (lambda (id) + (string-append + (if not? "NOT " "") + "EXISTS (SELECT 1 FROM build_tags " + "WHERE build_id = builds.id AND tag_id = " + (number->string id) + ")")) + tag-ids) (if not? " AND " " OR ")) ")"))) (sqlite-reset key-statement) @@ -2435,12 +2435,6 @@ SELECT uuid, derivations.name, priority, processed, canceled, created_at, end_ti FROM builds INNER JOIN derivations ON builds.derivation_id = derivations.id -LEFT JOIN ( - SELECT build_id, (',' || group_concat(tag_id) || ',') AS tag_string - FROM build_tags - GROUP BY build_id -) AS all_build_tags - ON builds.id = all_build_tags.build_id " (if where-needed? (string-append @@ -2505,10 +2499,8 @@ INNER JOIN derivation_outputs AS all_derivation_outputs ON outputs.id = all_derivation_outputs.output_id INNER JOIN derivation_inputs ON derivation_inputs.derivation_output_id = all_derivation_outputs.id -INNER JOIN derivations AS dependent_derivations - ON dependent_derivations.id = derivation_inputs.derivation_id INNER JOIN builds AS dependent_builds - ON dependent_builds.derivation_id = dependent_derivations.id + ON dependent_builds.derivation_id = derivation_inputs.derivation_id AND dependent_builds.processed = 0 AND dependent_builds.canceled = 0 WHERE derivation_outputs.derivation_id = builds.derivation_id)")) @@ -2516,7 +2508,6 @@ WHERE derivation_outputs.derivation_id = builds.derivation_id)")) " AND ") "\n") "") - "ORDER BY uuid ASC\n" (if limit (string-append "LIMIT " (number->string limit) "\n") ""))) |