Make listjobs faster by getting all info in one querybaserock/liw/lc-list-all-jobs-optimisation

2 files changed, 22 insertions, 3 deletions

diff --git a/lorrycontroller/listjobs.py b/lorrycontroller/listjobs.py
index eaffeef..f9747c9 100644
--- a/lorrycontroller/listjobs.py
+++ b/lorrycontroller/listjobs.py
@@ -52,12 +52,11 @@ class ListAllJobsHTML(lorrycontroller.LorryControllerRoute):
 
     def get_jobs(self, statedb):
         jobs = []
-        for job_id in statedb.get_job_ids():
-            exit = statedb.get_job_exit(job_id)
+        for job_id, path, exit in statedb.get_all_jobs_id_path_exit():
             job = {
                 'job_id': job_id,
                 'exit': 'no' if exit is None else str(exit),
-                'path': statedb.get_job_path(job_id),
+                'path': path,
                 }
             jobs.append(job)
         return jobs
diff --git a/lorrycontroller/statedb.py b/lorrycontroller/statedb.py
index 1f18189..8316c9a 100644
--- a/lorrycontroller/statedb.py
+++ b/lorrycontroller/statedb.py
@@ -539,6 +539,26 @@ class StateDB(object):
             'UPDATE jobs SET output=? WHERE job_id=?',
             (output + more_output, job_id))
 
+    def get_all_jobs_id_path_exit(self):
+        '''Return id, path, and exit for all jobs.
+
+        This is an ugly method, but it's much faster than first
+        getting a list of job ids and then querying path and exit for
+        each. Much, much faster. FTL versus the pitch drop experiment
+        faster.
+
+        This is a generator.
+
+        '''
+        
+        c = self.get_cursor()
+        c.execute('SELECT job_id, path, exit FROM jobs')
+        while True:
+            row = c.fetchone()
+            if row is None:
+                break
+            yield row[0], row[1], row[2]
+
     def remove_job(self, job_id):
         logging.debug('StateDB.append_to_job_output(%r,..) called', job_id)
         assert self.in_transaction