Inventory Controller - Datenbankoptimierungen für mini_journal
Aus Datenbanksicht war das Inventory mini-journal eine Katastrophe.
Die trans_id Abfrage führte zu einem ersten Seq Scan auf der Tabelle inventory. my $query = 'SELECT trans_id FROM inventory GROUP BY trans_id ORDER BY max(itime) DESC LIMIT 10';
Die Rose Manager Abfrage führte dann zu einem zweiten Seq Scan auf der Tabelle inventory: $objs = SL::DB::Manager::Inventory->get_all(query => [ trans_id => \@ids ]) if @ids;
Das sind zwei Seq Scans auf eine Tabelle die in kivitendo recht groß werden kann. Außerdem könnte in der Zwischenzeit ein neuer inventory-Eintrag dazugekommen sein, der damit ignoriert würde.
Im Template wurde dann auf die Rose-Objekte von part, transfer_type, bin und warehouse zugegriffen, und da diese noch nicht geladen wurden sorgt das im Extremfall für 40 weitere Datenbankzugriffe.
Das ist alles schön kompakter perl Code, aber wie könnte man das aus Datenbanksicht optimieren, also die Anzahl der Zugriffe verringern und nach Möglichkeit Indexe benutzen?
Die Templatezugriffe können einfach durch ein with_objects verhindert werden: with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ],
Statt einer separaten Abfrage für die trans_ids könnte man diese als eine Unterabfrage in get_all einführen: query => [ trans_id => [ \"$query" ] ]
Die get-all-Abfrage kann man aber noch weiter verbessern, indem man nach id statt trans_id filtert, da es für id im Gegensatz zu trans_id schon einen Index gibt. Das Ziel für die Unterabfrage sollte also sein, eine Liste von ids zu bekommen, damit der Index benutzt wird und man sich den Seq Scan spart.
Das mini-Journal zeigt die letzten 10 Lagerbewegungen, die entweder Einlagerungen, Auslagerungen oder Umlagerungen sein können. Im Fall von Umlagerungen wären das 2 inventory-Einträge, ansonsten 1. Da wir nicht wissen, wieviele Umlagerungen dabei sind, holen wir die letzten 20 Einträge, filtern diese nach den letzten 10 trans_ids, und extrahieren daraus die inventory ids (zwischen 10 und 20 ids). Die ursprüngliche Abfrage mit dem GROUP BY konnte keinen index nutzen, da das ORDER BY auf max(itime) statt itime war. Durch das "limit 20" werden zwar potentiell ein paar Zeilen zu viel geholt, dafür kann man aber nun einen Index auf inventory(itime) setzen, der von der Abfrage auch verwendet werden kann, und damit spart man sich auch den letzten Seq Scan auf inventory.
create index if not exists inventory_itime_idx on inventory (itime);
Inventory Controller - Datenbankoptimierungen für mini_journal
Aus Datenbanksicht war das Inventory mini-journal eine Katastrophe.
Die trans_id Abfrage führte zu einem ersten Seq Scan auf der Tabelle inventory.
my $query = 'SELECT trans_id FROM inventory GROUP BY trans_id ORDER BY max(itime) DESC LIMIT 10';
Die Rose Manager Abfrage führte dann zu einem zweiten Seq Scan auf der Tabelle inventory:
$objs = SL::DB::Manager::Inventory->get_all(query => [ trans_id => \@ids ]) if @ids;
Das sind zwei Seq Scans auf eine Tabelle die in kivitendo recht groß werden
kann. Außerdem könnte in der Zwischenzeit ein neuer inventory-Eintrag
dazugekommen sein, der damit ignoriert würde.
Im Template wurde dann auf die Rose-Objekte von part, transfer_type, bin und
warehouse zugegriffen, und da diese noch nicht geladen wurden sorgt das im
Extremfall für 40 weitere Datenbankzugriffe.
Das ist alles schön kompakter perl Code, aber wie könnte man das aus
Datenbanksicht optimieren, also die Anzahl der Zugriffe verringern und nach
Möglichkeit Indexe benutzen?
Die Templatezugriffe können einfach durch ein with_objects verhindert werden:
with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ],
Statt einer separaten Abfrage für die trans_ids könnte man diese als eine
Unterabfrage in get_all einführen:
query => [ trans_id => [ \"$query" ] ]
Die get-all-Abfrage kann man aber noch weiter verbessern, indem man nach id
statt trans_id filtert, da es für id im Gegensatz zu trans_id schon einen Index
gibt. Das Ziel für die Unterabfrage sollte also sein, eine Liste von ids zu
bekommen, damit der Index benutzt wird und man sich den Seq Scan spart.
Das mini-Journal zeigt die letzten 10 Lagerbewegungen, die entweder
Einlagerungen, Auslagerungen oder Umlagerungen sein können. Im Fall von
Umlagerungen wären das 2 inventory-Einträge, ansonsten 1. Da wir nicht wissen,
wieviele Umlagerungen dabei sind, holen wir die letzten 20 Einträge, filtern
diese nach den letzten 10 trans_ids, und extrahieren daraus die inventory ids
(zwischen 10 und 20 ids). Die ursprüngliche Abfrage mit dem GROUP BY konnte
keinen index nutzen, da das ORDER BY auf max(itime) statt itime war. Durch das
"limit 20" werden zwar potentiell ein paar Zeilen zu viel geholt, dafür kann
man aber nun einen Index auf inventory(itime) setzen, der von der Abfrage auch
verwendet werden kann, und damit spart man sich auch den letzten Seq Scan auf
inventory.
create index if not exists inventory_itime_idx on inventory (itime);