* Speed up the jobset index page. Especially the query to get the

[?]

Feb 9, 2010, 1:47 PM

6QRHXIM3XHCDLSIIBEGETDV67V6LTV55QMHC64ZPBMLTAECM5N3QC

Dependencies

[2] ID277UG3
[3] AKAXJDMW * Disable the statistics on the project, jobset and job pages for now
[4] TJPIS3UP hydra: minor improvements in hydra ui
[5] S6OISBQ3 * Mark the "current" builds in a jobset, i.e. those corresponding to
[6] 3HZY24CX * Make jobsets viewable under
[7] RAKTHYAI * In the job status and error pages, show when the status of a job
[8] A6FXZPKE * Another essential index.
[9] RH37XKJ2
[10] Z4KRJX4Q * In the last succeeded / job status queries, use the Builds.isCurrent
[11] RBNQKATL * Adding persistant releases. A release is a named set of builds.
[12] ODNCGFQ5 * Improved the navigation bar: don't include all projects (since that
[13] KN3VYE5P * Cleaned up the foreign key constraints.
[14] MI4IMCD3
[15] SJN2QPWH * Big speed-up of the job status page and the channel generation (such
[16] ZI535LI6 * hydra: 'new' UI for project/jobset/job/build
[17] SLWFKMU5 extra indexes
[18] T2232OBS * Add some DB indices to make the /releases page much faster.
[19] SFUSIKUY * Use isCurrent to determine the inactive jobs.
[*] N22GPKYT * Put info about logs / build products in the DB.

Change contents

edit in src/lib/Hydra/Controller/Jobset.pm at line 29

[3.118]

[5.335]

my $projectName = $c->stash->{project}->name;
my $jobsetName = $c->stash->{jobset}->name;

# Get the active / inactive jobs in this jobset.
my @jobs = $c->stash->{jobset}->jobs->search(
{ },
{ select => [
"name",
\("exists (select 1 from builds where project = '$projectName' and jobset = '$jobsetName' and job = me.name and isCurrent = 1) as active")
]
, as => ["name", "active"]
, order_by => ["name"] });
replacement in src/lib/Hydra/Controller/Jobset.pm at line 43

[5.336]→[5.0:405](∅→∅)

$c->stash->{activeJobs} = [
$c->stash->{jobset}->builds->search(
{isCurrent => 1},
{select => ["job"], order_by => ["job"], distinct => 1}
)];
$c->stash->{inactiveJobs} = [
$c->stash->{jobset}->builds->search(
{},
{select => ["job"], order_by => ["job"], group_by => ["job"], having => { 'sum(isCurrent)' => 0 }}
)];

[5.336]

[5.0]

$c->stash->{activeJobs} = [];
$c->stash->{inactiveJobs} = [];
foreach my $job (@jobs) {
print STDERR $job->get_column('active'), "\n";
if ($job->get_column('active')) {
push @{$c->stash->{activeJobs}}, $job->name;
} else {
push @{$c->stash->{inactiveJobs}}, $job->name;
}
}
replacement in src/lib/Hydra/Controller/Jobset.pm at line 63

[4.158]→[5.329:1313](∅→∅),[5.329]→[5.329:1313](∅→∅)

my @select = ();
my @as = ();
push(@select, "job"); push(@as, "job");
foreach my $system (@systems) {
push(@select, "(SELECT buildstatus FROM BuildResultInfo bri NATURAL JOIN Builds b WHERE b.id = (SELECT MAX(id) FROM Builds t WHERE t.project = me.project AND t.jobset = me.jobset AND t.job = me.job AND t.system = '$system'))");
push(@as, $system);
push(@select, "(SELECT b.id FROM BuildResultInfo bri NATURAL JOIN Builds b WHERE b.id = (SELECT MAX(id) FROM Builds t WHERE t.project = me.project AND t.jobset = me.jobset AND t.job = me.job AND t.system = '$system'))");
push(@as, $system."-build");
}
$c->stash->{activeJobsStatus} = [$c->model('DB')->resultset('ActiveJobsForJobset')
->search( {}
, { bind => [$c->stash->{project}->name, $c->stash->{jobset}->name]
, select => \@select
, as => \@as
, order_by => ["job"]
})];
}

[4.158]

[5.1313]

my @select = ();
my @as = ();
push(@select, "job"); push(@as, "job");
foreach my $system (@systems) {
push(@select, "(SELECT buildstatus FROM BuildResultInfo bri NATURAL JOIN Builds b WHERE b.id = (SELECT MAX(id) FROM Builds t WHERE t.project = me.project AND t.jobset = me.jobset AND t.job = me.job AND t.system = '$system'))");
push(@as, $system);
push(@select, "(SELECT b.id FROM BuildResultInfo bri NATURAL JOIN Builds b WHERE b.id = (SELECT MAX(id) FROM Builds t WHERE t.project = me.project AND t.jobset = me.jobset AND t.job = me.job AND t.system = '$system'))");
push(@as, $system."-build");
}
$c->stash->{activeJobsStatus} =
[ $c->model('DB')->resultset('ActiveJobsForJobset')->search(
{},
{ bind => [$c->stash->{project}->name, $c->stash->{jobset}->name]
, select => \@select
, as => \@as
, order_by => ["job"]
})];
}
replacement in src/root/jobset.tt at line 241

[5.24835]→[5.24835:24974](∅→∅)

[% FOREACH j IN activeJobs %] [% INCLUDE renderJobName project=project.name jobset=jobset.name job=j.get_column('job') %] [% END %]

[5.24835]

[5.24974]

[% FOREACH j IN activeJobs %] [% INCLUDE renderJobName project=project.name jobset=jobset.name job=j %] [% END %]
replacement in src/root/jobset.tt at line 244

[5.25001]→[5.25001:25005](∅→∅)

[5.25001]

[5.25005]
replacement in src/root/jobset.tt at line 249

[5.25168]→[5.25168:25309](∅→∅)

[% FOREACH j IN inactiveJobs %] [% INCLUDE renderJobName project=project.name jobset=jobset.name job=j.get_column('job') %] [% END %]

[5.25168]

[5.25309]

[% FOREACH j IN inactiveJobs %] [% INCLUDE renderJobName project=project.name jobset=jobset.name job=j %] [% END %]
replacement in src/sql/hydra.sql at line 425

[5.2228]→[5.2228:2358](∅→∅),[5.2358]→[5.3337:3395](∅→∅),[5.2984]→[5.1204:1262](∅→∅),[5.1262]→[5.0:56](∅→∅),[5.56]→[2.0:258](∅→∅)

create index IndexBuildInputsByBuild on BuildInputs(build);
create index IndexBuildInputsByDependency on BuildInputs(dependency);
create index IndexBuildsByTimestamp on Builds(timestamp);
create index IndexBuildsByIsCurrent on Builds(isCurrent);
create index IndexBuildsByFinished on Builds(finished);
create index IndexBuildsByProject on Builds(project);
create index IndexBuildsByJobset on Builds(project, jobset);
create index IndexBuildsByJob on Builds(project, jobset, job);
create index IndexBuildsByJobAndSystem on Builds(project, jobset, job, system);

[5.2228]

[5.2984]

create index IndexBuildInputsOnBuild on BuildInputs(build);
create index IndexBuildInputsOnDependency on BuildInputs(dependency);
create index IndexBuildProducstOnBuildAndType on BuildProducts(build, type);
create index IndexBuildProductsOnBuild on BuildProducts(build);
replacement in src/sql/hydra.sql at line 430

[5.3104]→[5.0:81](∅→∅),[5.81]→[5.57:198](∅→∅),[5.198]→[5.0:58](∅→∅),[5.6014]→[5.0:58](∅→∅)

create index IndexBuildSchedulingInfoByBuild on BuildSchedulingInfo(id); -- idem
create index IndexBuildProductsByBuild on BuildProducts(build);
create index IndexBuildProducstByBuildAndType on BuildProducts(build, type);
create index IndexBuildStepsByBuild on BuildSteps(build);

[5.3104]

[5.8636]

create index IndexBuildSchedulingInfoOnBuild on BuildSchedulingInfo(id); -- idem
create index IndexBuildStepsOnBuild on BuildSteps(build);
create index IndexBuildsOnFinished on Builds(finished);
create index IndexBuildsOnIsCurrent on Builds(isCurrent);
create index IndexBuildsOnJob on Builds(project, jobset, job);
create index IndexBuildsOnJobAndIsCurrent on Builds(project, jobset, job, isCurrent);
create index IndexBuildsOnJobAndSystem on Builds(project, jobset, job, system);
create index IndexBuildsOnJobset on Builds(project, jobset);
create index IndexBuildsOnProject on Builds(project);
create index IndexBuildsOnTimestamp on Builds(timestamp);
create index IndexJobsetAltsOnJobset on JobsetInputAlts(project, jobset);