03/03: Chunk the data for some queries in insert-missing-data-and-return-all-ids

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 37e8af60fb33a84a92328f3a812baceafca4e40d
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Thu Sep 15 09:18:19 2022 +0100

    Chunk the data for some queries in insert-missing-data-and-return-all-ids
    
    This helps to avoid queries getting logged as slow just because of the 
amount
    of data.
---
 guix-data-service/model/utils.scm | 42 +++++++++++++++++++++++++--------------
 1 file changed, 27 insertions(+), 15 deletions(-)

diff --git a/guix-data-service/model/utils.scm 
b/guix-data-service/model/utils.scm
index 4a0e5d8..093b618 100644
--- a/guix-data-service/model/utils.scm
+++ b/guix-data-service/model/utils.scm
@@ -53,12 +53,13 @@
           s)
       #f))
 
-(define (exec-query->vhash conn query field-function value-function)
+(define* (exec-query->vhash conn query field-function value-function
+                            #:key (vhash vlist-null))
   (fold (lambda (row result)
           (vhash-cons (field-function row)
                       (value-function row)
                       result))
-        vlist-null
+        vhash
         (exec-query-with-null-handling conn query)))
 
 (define (two-lists->vhash l1 l2)
@@ -194,7 +195,7 @@ WHERE table_name = $1"
         schema-details)
        (error "error: field-can-be-null?"))))
 
-  (define select-query
+  (define (select-query data)
     (string-append
      "SELECT id,\n"
      (string-join (map (lambda (field)
@@ -210,10 +211,7 @@ WHERE table_name = $1"
           "("
           (string-join (map value->sql field-values) ",")
           ")"))
-       (if sets-of-data?
-           (delete-duplicates
-            (concatenate data))
-           data))
+       data)
       ", ")
      ")\n  AS vals (" (string-join field-strings ", ") ") "
      "ON "
@@ -355,11 +353,20 @@ WHERE table_name = $1"
               ;; If not using a temporary table, just do a single SELECT query
               (if (null? data)
                   '()
-                  (exec-query->vhash conn
-                                     select-query
-                                     cdr
-                                     (lambda (result)
-                                       (string->number (first result)))))))
+                  (fold
+                   (lambda (data-chunk result)
+                     (exec-query->vhash conn
+                                        (select-query data-chunk)
+                                        cdr
+                                        (lambda (result)
+                                          (string->number (first result)))
+                                        #:vhash result))
+                   vlist-null
+                   (chunk (if sets-of-data?
+                              (delete-duplicates
+                               (concatenate data))
+                              data)
+                          5000)))))
          (missing-entries
           (filter (lambda (field-values)
                     (not (vhash-assoc
@@ -375,9 +382,14 @@ WHERE table_name = $1"
          (new-entries
           (if (null? missing-entries)
               '()
-              (map (lambda (result)
-                     (string->number (first result)))
-                   (exec-query conn (insert-sql missing-entries)))))
+              (append-map!
+               (lambda (missing-entries-chunk)
+                 (map (lambda (result)
+                        (string->number (first result)))
+                      (exec-query conn
+                                  (insert-sql missing-entries-chunk))))
+               (chunk missing-entries 5000))))
+
          (new-entries-lookup-vhash
           (two-lists->vhash missing-entries
                             new-entries))