From a81cea34b0d8aa483e697b45dacb032a4a26cd5d Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Wed, 10 Oct 2012 13:37:23 -0700 Subject: [PATCH] Improve the last_build queries - they had got noticeably slow --- cgi-bin/show_log.pl | 24 ++++++++++++++---------- 1 file changed, 14 insertions(+), 10 deletions(-) diff --git a/cgi-bin/show_log.pl b/cgi-bin/show_log.pl index 064337d..22df731 100755 --- a/cgi-bin/show_log.pl +++ b/cgi-bin/show_log.pl @@ -58,16 +58,20 @@ if ($system && $logdate) }; my $last_build_statement = q{ - select distinct on (sysname) sysname, snapshot, stage, git_head_ref - from build_status - where sysname = ? and branch = ? and snapshot < ? - order by sysname, snapshot desc limit 1 + select git_head_ref + from build_status + where sysname = ? and branch = ? and snapshot = + (select max(snapshot) + from build_status + where sysname = ? and branch = ? and snapshot < ?) }; my $last_success_statement = q{ - select distinct on (sysname) sysname, snapshot, git_head_ref - from build_status - where sysname = ? and branch = ? and snapshot < ? and stage = 'OK' - order by sysname, snapshot desc limit 1 + select git_head_ref + from build_status + where sysname = ? and branch = ? and snapshot = + (select max(snapshot) + from build_status + where sysname = ? and branch = ? and snapshot < ? and stage = 'OK') }; my $sth=$db->prepare($statement); $sth->execute($system,$logdate); @@ -80,7 +84,7 @@ if ($system && $logdate) { $last_build_row = $db->selectrow_hashref($last_build_statement,undef, - $system,$branch,$logdate); + $system, $branch,$system,$branch,$logdate); $last_build_git_ref = $last_build_row->{git_head_ref} if $last_build_row; @@ -90,7 +94,7 @@ if ($system && $logdate) { $last_success_row = $db->selectrow_hashref($last_success_statement,undef, - $system,$branch,$logdate); + $system,$branch,$system,$branch,$logdate); $last_success_git_ref = $last_success_row->{git_head_ref} if $last_success_row; } -- 2.30.2