From 4d7864c0465fda9bb9f2d00de2f0ca0fd6a75ff0 Mon Sep 17 00:00:00 2001 From: Mathieu Othacehe Date: Tue, 29 Sep 2020 17:44:40 +0200 Subject: metrics: Optimize queries. Avoid full Builds table scans. * src/cuirass/metrics.scm (db-average-build-start-time-per-eval, db-average-build-complete-time-per-eval, db-evaluation-completion-speed): Rewrite query to avoid full Builds table scans. --- src/cuirass/metrics.scm | 9 +++------ 1 file changed, 3 insertions(+), 6 deletions(-) diff --git a/src/cuirass/metrics.scm b/src/cuirass/metrics.scm index 1fa94cd..2c61555 100644 --- a/src/cuirass/metrics.scm +++ b/src/cuirass/metrics.scm @@ -103,8 +103,7 @@ SELECT 100 * CAST(SUM(status > 0) as float) / COUNT(*) FROM SELECT AVG(B.starttime - E.evaltime) FROM (SELECT id, evaltime FROM Evaluations WHERE id = " eval ") E -LEFT JOIN -(SELECT id, evaluation, starttime FROM Builds) B +LEFT JOIN Builds as B ON E.id = B.evaluation and B.starttime > 0 GROUP BY E.id;"))) (and=> (expect-one-row rows) (cut vector-ref <> 0))))) @@ -116,8 +115,7 @@ GROUP BY E.id;"))) SELECT AVG(B.stoptime - E.evaltime) FROM (SELECT id, evaltime FROM Evaluations WHERE id = " eval ") E -LEFT JOIN -(SELECT id, evaluation, stoptime FROM Builds) B +LEFT JOIN Builds as B ON E.id = B.evaluation and B.stoptime > 0 GROUP BY E.id;"))) (and=> (expect-one-row rows) (cut vector-ref <> 0))))) @@ -142,8 +140,7 @@ SELECT END - E.evaltime) FROM (SELECT id, evaltime FROM Evaluations WHERE id = " eval ") E -LEFT JOIN -(SELECT id, evaluation, status, stoptime FROM Builds) B +LEFT JOIN Builds as B ON E.id = B.evaluation and B.stoptime > 0 GROUP BY E.id;"))) (and=> (expect-one-row rows) (cut vector-ref <> 0))))) -- cgit v1.2.3