Hallo Zusammen,
beim Testen von VZ auf dem eher schwachbrüstigen Raspberry Pi ist mir
aufgefallen, dass Anfragen des Frontends nach einer bestimmten Anzahl
von Tupeln bei niedrigen Zoomstufen dadurch beantwortet werden, dass der
Interpreter durch alle Rows eines Resultsets läuft und diese gem. mit
Hilfe von packageSize auf die gewünschte Anzahl Tupel aggregiert. Dabei
müssen zum einen recht viele Daten verarbeitet werden, zum anderen diese
auch erstmal aus MySQL abgeholt werden.
Alternativ lassen sich die Daten jedoch bereits in MySQL aggregieren.
Die untenstehende Methode erledigt das und kann in MeterInterpreter
eingefügt werden (siehe unterer Teil nach EmptyIterator).
Ich freue mich über Feedback/ Testergebnisse:
/**
* Get raw data
*
* Optimized version for thinning meter data
*
* @param string|integer $groupBy
* @return Volkszaehler\DataIterator
*/
protected function getData() {
// get timestamps of preceding and following data points as a
graciousness
// for the frontend to be able to draw graphs to the left and
right borders
if (isset($this->from)) {
$sql = 'SELECT MIN(timestamp) FROM (SELECT timestamp FROM
data WHERE channel_id=? AND timestamp<? ORDER BY timestamp DESC LIMIT 2) t';
$from = $this->conn->fetchColumn($sql,
array($this->channel->getId(), $this->from), 0);
if ($from)
$this->from = $from;
}
if (isset($this->to)) {
$sql = 'SELECT MAX(timestamp) FROM (SELECT timestamp FROM
data WHERE channel_id=? AND timestamp>? ORDER BY timestamp ASC LIMIT 2) t';
$to = $this->conn->fetchColumn($sql,
array($this->channel->getId(), $this->to), 0);
if ($to)
$this->to = $to;
}
// common conditions for following SQL queries
$sqlParameters = array($this->channel->getId());
$sqlTimeFilter = self::buildDateTimeFilterSQL($this->from,
$this->to, $sqlParameters);
if ($this->groupBy) {
$sqlGroupFields = self::buildGroupBySQL($this->groupBy);
if (!$sqlGroupFields)
throw new \Exception('Unknown group');
$sqlRowCount = 'SELECT COUNT(DISTINCT ' . $sqlGroupFields .
') FROM data WHERE channel_id = ?' . $sqlTimeFilter;
$sql = 'SELECT MAX(timestamp) AS timestamp, SUM(value) AS
value, COUNT(timestamp) AS count'.
' FROM data'.
' WHERE channel_id = ?' . $sqlTimeFilter .
' GROUP BY ' . $sqlGroupFields;
}
else {
$sqlRowCount = 'SELECT COUNT(*) FROM data WHERE channel_id
= ?' . $sqlTimeFilter;
$sql = 'SELECT timestamp, value, 1 AS count FROM data WHERE
channel_id=?' . $sqlTimeFilter . ' ORDER BY timestamp ASC';
}
$this->rowCount = (int) $this->conn->fetchColumn($sqlRowCount,
$sqlParameters, 0);
if ($this->rowCount <= 0)
return new \EmptyIterator();
// potential to reduce result set?
if ($this->rowCount > $this->tupleCount && !$this->groupBy) {
$packageSize = floor($this->rowCount / $this->tupleCount);
// worth doing -> go
if ($packageSize > 1) {
$this->rowCount = floor($this->rowCount / $packageSize);
$this->conn->query('SET @row:=0;');
$sql = 'SELECT MAX(aggregate.timestamp) AS timestamp,
SUM(aggregate.value) AS value, '.$packageSize.' AS count '.
'FROM ('.
' SELECT timestamp, value, @row:=@row+1 AS row '.
' FROM data WHERE channel_id=?' .
$sqlTimeFilter .
') AS aggregate '.
'GROUP BY row DIV ' . $packageSize .' '.
'ORDER BY timestamp ASC;';
}
}
$stmt = $this->conn->executeQuery($sql, $sqlParameters); //
query for data
return new DataIterator($stmt, $this->rowCount, $this->tupleCount);
}
}
Viele Grüße,
Andreas
