From f106bbe5c5aee8109521fa60387d54ac88e5f46d Mon Sep 17 00:00:00 2001 From: Lars Wirzenius Date: Thu, 22 May 2014 10:58:31 +0000 Subject: Make listjobs faster by getting all info in one query --- lorrycontroller/listjobs.py | 5 ++--- lorrycontroller/statedb.py | 20 ++++++++++++++++++++ 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 -- cgit v1.2.1