i working on application generate buildup , burndown chart in mysql/php use generate using nvd3.js
my code below loops every day min max date of dataset can filtered.
so far, run query 1 time every day, realize can add tremendous amount of delay, 10 seconds wait load.
how can generate data more quickly?
invoking code
public function burnupaction() { $actionitemtable = $this->getactionitemtable(); $burnup = array('totalactionitems' => $actionitemtable->getburnup('assigneddate'), 'ecditems' => $actionitemtable->getburnup('ecd'), 'originaldueitems' => $actionitemtable->getburnup('duedate'), 'actualopenitems' => $actionitemtable->getburnup('closeddate')); $this->response->setcontent(json_encode($burnup)); return $this->response; } build chart code
for ($y = $minyear; $y <= $maxyear; $y++) { if ($y == $minyear) $startmonth = $minmonth; else $startmonth = 1; if ($y == $maxyear) $finishmonth = $maxmonth; else $finishmonth = 12; ($m = $startmonth; $m <= $finishmonth; $m++) { if ($m < 10) { $month = "0$m"; } else { $month = "$m"; } $monthstr = $this->getmonth($m); ($d = 1; $d <= 31; $d++) { if ($d< 10) { $day = "0$d"; } else { $day = "$d"; } $dt = "$monthstr $day $y"; $start = "$y-$month-$day"; $end = "$y-$month-$day"; $where = $this->filterstring(); $filtered = "select * actionitems " . $where; if ($field == 'assigneddate') { array_push($subsel, "(select '$dt' assigneddate, sum(case when assigneddate between '$start' , '$end' 1 else 0 end) 'numactionitems' ($filtered) s)"); } if ($field == 'ecd') { array_push($subsel, "(select '$dt' ecd, sum(case when ecd between '$start' , '$end' 1 else 0 end) 'numactionitems' ($filtered) s)"); } if ($field == 'duedate') { array_push($subsel, "(select '$dt' duedate, sum(case when duedate between '$start' , '$end' 1 else 0 end) 'numactionitems' ($filtered) s)"); } if ($field == 'closeddate') { array_push($subsel, "(select '$dt' closeddate, sum(case when closeddate between '$start' , '$end' 1 else 0 end) 'numactionitems' ($filtered) s)"); } } } } if (count($subsel) == 0) return array(); $sub = join(" union ", $subsel); if ($field == 'assigneddate') { $sql = "select assigneddate, (@csum:= @csum + numactionitems) totalactionitems ($sub) t"; } if ($field == 'ecd') { $sql = "select ecd, numactionitems ecditems, (@csum:= @csum + numactionitems) totalactionitems ($sub) t"; } if ($field == 'duedate') { $sql = "select duedate, numactionitems originaldueitems, (@csum:= @csum + numactionitems) totalactionitems ($sub) t"; } if ($field == 'closeddate') { $sql = "select closeddate, numactionitems acutalcloseditems, (@csum:= @csum + numactionitems) totalactionitems ($sub) t"; } generated carts (buildup on top / burndown on bottom)
the main part of query this:
select 'mar 01 2015' assigneddate, sum(case when assigneddate between '2015-03-01' , '2015-03-01' 1 else 0 end) 'numactionitems' (select * actionitems) s; this query has 1 drawback: per date table actionitems scanned.
to improve query can written as:
select 'mar 01 2015' assigneddate, count(*) 'numactionitems' actionitems assigneddate between '2015-03-01' , '2015-03-01'; this still not pretty, next step required: alternative dates be:
select assigneddate, count(*) 'numactionitems' actionitems assigneddate between '2015-03-01' , '2015-03-30' group assigndate; this give 'numactionitems' per date.
adding @csum outer query:
select assigneddate, ( @csum := @csum + numactionitems ) totalactionitems ( select assigneddate, count(*) 'numactionitems' actionitems assigneddate between '2015-03-01' , '2015-03-30' group assigndate) should give same result.
also add index:
create index idx_ai_nn_1 on actionitems(assigneddate); this index work if date range scanning table << smaller number of rows selecting.
Comments
Post a Comment