[OSSTEST,v2,25/41] sg-report-host-history: Do the main query per host

Commit Message

Ian Jackson July 31, 2020, 11:38 a.m. UTC

In f6001d628c3b3fd42b10cd15351981a04bc02572 we combined these
queries into one:
  sg-report-host-history: Aggregate runvars query for all hosts

Now that we have an index, there is a faster way for the db to do this
query: via that index.  But it doesn't like to do that if be aggregate
the queries.  Experimentally, doing this query separately once per
host is significantly faster.

Also, later, it will allow us to parallelise this work.

So, we undo that.  (Not by reverting, though.)

Signed-off-by: Ian Jackson <[e-mail]>
---
v2: Use proper \ escaping for underscores in LIKE
---
 schema/runvars-host-index.sql |  2 +-
 sg-report-host-history        | 27 +++++++++------------------
 2 files changed, 10 insertions(+), 19 deletions(-)

Patch

diff --git a/schema/runvars-host-index.sql b/schema/runvars-host-index.sql
index 222a0a30..6a3ef377 100644
--- a/schema/runvars-host-index.sql
+++ b/schema/runvars-host-index.sql
@@ -1,4 +1,4 @@ 
--- ##OSSTEST## 009 Preparatory
+-- ##OSSTEST## 009 Needed
 --
 -- This index helps sg-report-host-history find relevant flights.
 
diff --git a/sg-report-host-history b/sg-report-host-history
index 1c2d19ae..15866ab6 100755
--- a/sg-report-host-history
+++ b/sg-report-host-history
@@ -165,34 +165,25 @@  sub jobquery ($$$) {
 our %hosts;
 
 sub mainquery () {
-    our $valcond = join " OR ", map { "val = ?" } keys %hosts;
-    our @params = keys %hosts;
-
     our $runvarq //= db_prepare(<<END);
-	SELECT flight, job, name, val, status
+	SELECT flight, job, name, status
 	  FROM runvars
           JOIN jobs USING (flight, job)
-	 WHERE $namecond
-	   AND ($valcond)
+	 WHERE (name = 'host' OR name LIKE '%\_host')
+	   AND val = ?
 	   AND $flightcond
            AND $restrictflight_cond
            AND flight > ?
 	 ORDER BY flight DESC
-	 LIMIT ($limit * 3 + 100) * ?
+         LIMIT $limit * 2
 END
+    foreach my $host (sort keys %hosts) {
+	print DEBUG "MAINQUERY $host...\n";
+	$runvarq->execute($host, $minflight);
 
-    push @params, $minflight;
-    push @params, scalar keys %hosts;
-
-    print DEBUG "MAINQUERY...\n";
-    $runvarq->execute(@params);
-
-    print DEBUG "FIRST PASS\n";
-    while (my $jr= $runvarq->fetchrow_hashref()) {
-	print DEBUG " $jr->{flight}.$jr->{job} ";
-	push @{ $hosts{$jr->{val}} }, $jr;
+	$hosts{$host} = $runvarq->fetchall_arrayref({});
+	print DEBUG "MAINQUERY $host got ".(scalar @{ $hosts{$host} })."\n";
     }
-    print DEBUG "\n";
 }
 
 sub reporthost ($) {