optimizing a query 2007-01-18 - By Randy Burke
Back
I know this is not really a Perl question, Except that I use perl yo
generate the page.
I have a report that is displayed on a webpage in an 10 x 9 table
Originally we did it as one query per cell, then we combined it into one
monolithic query that returns one row with 72 columns
I am sure that there is some way to optimize this, maybe using sub-query,
which I have not been able to grasp yet.
so here is the beast:
my ($newdata, $noanswerdata, $timeddata, $misseddata, $highdata,
$normaldata, $lowdata, $monitordata);
# Open DB
my $dbh = DBI->connect( "dbi:$datasrc", "$sqluser", "$sqlpassword") ||
error("Unable to open user database: $DBI::errstr");
# Setup the Query
my $query = qq{SELECT
# New
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
# No answer
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND noanswer='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
noanswer='1'),
# Timed Callback
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND NOT
date_format(timedcallback, "%Y")='0000'),
# Missed Timed Callbacks
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
timedcallback<date_format(now(), '%Y-%m-%d') AND NOT
date_format(timedcallback, "%Y")='0000'),
# High Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='1'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='1'),
# Normal Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='2'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='2'),
# Low Priority
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='O' AND priority='3'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND
priority='3'),
# Monitor
sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND
status='M'),
sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='M')
FROM ticketsys
WHERE project=?
AND assignedto='POOL'
GROUP BY project};
my $sth = $dbh->prepare($query);
$sth->execute($project) || error("Cannot execute: $DBI::errstr");
my ($newolder, $newtodayminus6, $newtodayminus5, $newtodayminus4,
$newtodayminus3, $newtodayminus2, $newtodayminus1, $newtoday, $naolder,
$natodayminus6, $natodayminus5, $natodayminus4, $natodayminus3,
$natodayminus2, $natodayminus1, $natoday, $tcolder, $tctodayminus6,
$tctodayminus5, $tctodayminus4, $tctodayminus3, $tctodayminus2,
$tctodayminus1, $tctoday, $mcolder, $mctodayminus6, $mctodayminus5,
$mctodayminus4, $mctodayminus3, $mctodayminus2, $mctodayminus1, $mctoday,
$hpolder, $hptodayminus6, $hptodayminus5, $hptodayminus4, $hptodayminus3,
$hptodayminus2, $hptodayminus1, $hptoday, $npolder, $nptodayminus6,
$nptodayminus5, $nptodayminus4, $nptodayminus3, $nptodayminus2,
$nptodayminus1, $nptoday, $lpolder, $lptodayminus6, $lptodayminus5,
$lptodayminus4, $lptodayminus3, $lptodayminus2, $lptodayminus1, $lptoday,
$monolder, $montodayminus6, $montodayminus5, $montodayminus4,
$montodayminus3, $montodayminus2, $montodayminus1, $montoday);
$sth->bind_columns( undef, \$newolder, \$newtodayminus6,
\$newtodayminus5, \$newtodayminus4, \$newtodayminus3, \$newtodayminus2,
\$newtodayminus1, \$newtoday, \$naolder, \$natodayminus6, \$natodayminus5,
\$natodayminus4, \$natodayminus3, \$natodayminus2, \$natodayminus1,
\$natoday, \$tcolder, \$tctodayminus6, \$tctodayminus5, \$tctodayminus4,
\$tctodayminus3, \$tctodayminus2, \$tctodayminus1, \$tctoday, \$mcolder,
\$mctodayminus6, \$mctodayminus5, \$mctodayminus4, \$mctodayminus3,
\$mctodayminus2, \$mctodayminus1, \$mctoday, \$hpolder, \$hptodayminus6,
\$hptodayminus5, \$hptodayminus4, \$hptodayminus3, \$hptodayminus2,
\$hptodayminus1, \$hptoday, \$npolder, \$nptodayminus6, \$nptodayminus5,
\$nptodayminus4, \$nptodayminus3, \$nptodayminus2, \$nptodayminus1,
\$nptoday, \$lpolder, \$lptodayminus6, \$lptodayminus5, \$lptodayminus4,
\$lptodayminus3, \$lptodayminus2, \$lptodayminus1, \$lptoday, \$monolder,
\$montodayminus6, \$montodayminus5, \$montodayminus4, \$montodayminus3,
\$montodayminus2, \$montodayminus1, \$montoday);
$sth->fetch();
$sth->finish();
Any help in taming the monster would be nice.
Randy B.
|
|
