Snížení počtu dotazů v ORM

Školení, která pořádám

Představte si kód využívající ORM:

<?php
$authors = new Table("author", "name LIKE 'a%'");
foreach ($authors->data as $author) {
    echo "$author->name:\n";
    foreach ($author->getAll("book") as $book) {
        echo "- $book->title (" . $book->getOne("publisher")->name . ")\n";
    }
}
?>

Hodně dotazů

Ve většině ORM povede tento kód celkem k 1 + N + M dotazům, kde N je počet autorů a M je počet knih. Většina ORM tento problém zná a řeší ho pomocí cachování, to má ale svá úskalí – při prázdné cachi je dotazů stejně potřeba hodně, cache je potřeba aktualizovat, …

Zde je ukázka nehospodárného ORM, který klade dotazy opakovaně:

<?php
// název tabulky v jednotném čísle, primární klíč `id`, odkazy na tabulky podle jejich názvu

class Table {
    var $name;
    var $data = array();
    
    function __construct($name, $where = "") {
        $this->name = $name;
        $result = mysql_query("SELECT * FROM $name" . ($where ? " WHERE $where" : ""));
        while ($row = mysql_fetch_assoc($result)) {
            $this->data[$row["id"]] = new Row($this, $row);
        }
        mysql_free_result($result);
    }
}

class Row {
    private $table;
    
    function __construct($table, $row) {
        $this->table = $table;
        foreach ($row as $key => $val) {
            $this->$key = $val;
        }
    }
    
    function getAll($name) {
        $table = new Table($name, $this->table->name . " = $this->id");
        return $table->data;
    }
    
    function getOne($name) {
        if (!$this->$name) {
            return null;
        }
        $table = new Table($name, "id = " . $this->$name);
        return reset($table->data);
    }
}

/* Položené dotazy:
SELECT * FROM author WHERE name LIKE 'a%';
SELECT * FROM book WHERE author = 1;
SELECT * FROM publisher WHERE id = 21;
SELECT * FROM publisher WHERE id = 21;
SELECT * FROM book WHERE author = 2;
SELECT * FROM publisher WHERE id = 22;
*/
?>

Jeden dotaz

Výpis dat v požadované struktuře se dá zajistit i jedním dotazem, to má ale také svá úskalí: dotaz je složitý, procházení je neintuitivní, dat se přenáší příliš mnoho (informace o autorovi se přenáší i s každou jeho knihou), … Pro sestavení tohoto dotazu v ORM bychom navíc potřebovali dopředu vědět, k jakým objektům se bude přistupovat.

Ideální počet dotazů

Kompromisní řešení vidím v použití tří dotazů – jeden získá autory, druhý všechny jejich knihy a třetí všechny jejich vydavatele. Tento postup se navíc dá využít i v ORM. Jak na to?

<?php
class Table {
    var $name;
    var $data = array();
    var $group = array();
    var $tables = array();
    
    function __construct($name, $where = "", $group = "") {
        $this->name = $name;
        $result = mysql_query("SELECT * FROM $name" . ($where ? " WHERE $where" : ""));
        while ($row = mysql_fetch_assoc($result)) {
            $this->data[$row["id"]] = new Row($this, $row);
            if ($group) {
                $this->group[$row[$group]][$row["id"]] = $this->data[$row["id"]];
            }
        }
        mysql_free_result($result);
    }
}

class Row {
    private $table;
    
    function __construct($table, $row) {
        $this->table = $table;
        foreach ($row as $key => $val) {
            $this->$key = $val;
        }
    }
    
    function getAll($name) {
        if (!isset($this->table->tables[$name])) {
            $column = $this->table->name;
            $where = "$column IN (" . implode(", ", array_keys($this->table->data)) . ")";
            $this->table->tables[$name] = new Table($name, $where, $column);
        }
        return $this->table->tables[$name]->group[$this->id];
    }
    
    function getOne($name) {
        if (!isset($this->table->tables[$name])) {
            $in = array();
            foreach ($this->table->data as $val) {
                if ($val->$name) {
                    $in[$val->$name] = true;
                }
            }
            $this->table->tables[$name] = new Table($name, "id IN (" . implode(", ", array_keys($in)) . ")");
        }
        return $this->table->tables[$name]->data[$this->$name];
    }
}

/* Položené dotazy:
SELECT * FROM author WHERE name LIKE 'a%';
SELECT * FROM book WHERE author IN (1, 2);
SELECT * FROM publisher WHERE id IN (21, 22);
*/
?>

Metody getAll a getOne získají pomocí operátoru IN jedním dotazem rovnou všechna potřebná data. Z těchto dat následně vrací požadovanou část. To vede k tomu, že z každé tabulky se získají jedním dotazem všechna požadovaná data a žádná navíc. Objem přenesených dat je tedy ve většině případů minimální možný.

Závěr

Představené řešení samozřejmě není kompletním ORM, chybí např. dotazy do tabulek M:N. Osobně ORM v PHP ani nepoužívám, ale pokud vy ano, zkuste v něm představenou myšlenku využít.

Jakub Vrána, Řešení problému, 30.1.2009, diskuse: 22 (nové: 0)

Diskuse

Jan Menšík:

Zalezi na konkretni implementaci, ale osobne preferuji druhou moznost, tedy jednim dotazem nacist co nejvice udaju, i za cenu zvyseneho objemu prenasenych dat mezi aplikaci a SQL serverem. IMHO proste k udaji o knize patri i udaj o vydavateli a autorovi, nestaci jen jeho ID.

ikona Jakub Vrána OpenID:

A pokud toto pravidlo aplikujeme rekurzivně, tak skončíme u načtení všech tabulek v databázi při libovolném dotazu…

Navíc příklad prochází autory a zobrazuje všechny jejich knihy. Pokud by nás knihy nezajímaly (chtěli bychom zobrazit třeba jen seznam autorů), tak bys je stejně načítal? To je přece nesmysl.

Jan Menšík:

Samozrejme je nutne pouzivat hlavu. Pokud nacitam autory, vedle jejich osobnich udaju si nactu i agregovanou informaci o poctu odpovidajich knih + jednu pro ukazku (group_concat nemam rad), stejne tak vydavatele.

V konkretnim prikladu bych tedy nenacital autori a nasledne jejich knihy, ale nacetl bych knihy a s tim bych si vzdy nacetl i zakladni informace o autorovi a vydavateli.

Kazdy zpusob ma sva pro i proti. Je vzdy nutne posoudit potreby a podle toho zvolit variantu.

ikona Jakub Vrána OpenID:

Obávám se, že každý mluvíme o něčem jiném. Já jsem tady dal ukázku jednoduchého a přehledného kódu, se kterým se ORM musí umět vypořádat. A ty doporučuješ kód přepsat.

Kód navíc vybírá jenom autory od 'A' a následně jejich knihy. Takže kdybys chtěl nejprve vybrat knihy, musel bys je omezit jenom na tyto autory nebo to povede ke zcela zásadnímu plýtvání.

A dovolím si tvrdit, že představená myšlenka žádné "proti" nemá: kód je přirozený a jednoduchý, objem dat přenášených z databáze je minimální a dotazů se pokládá konstantní množství.

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL: Reakce na: Jakub Vrána

Rattus:

Nebude neumerne narustat pocet hodnot v IN casti ?
pokud dostanu na prvni dotaz tisic(e) autoru a ti budou mit desetitisic(e) knih ?

ikona Jakub Vrána OpenID:

Pokud chci získat takovéto množství dat, tak mi nic jiného nezbývá. Řešení samozřejmě předpokládá, že programátor je slušný a využije všechna data, o která databázi požádá (tedy že např. po prvním průchodu cyklem nezavolá break).

LLook:

Napadlo mě to samé co Rattuse. Pokud budu mít tisíce autorů s desetitisíci knih, tak bych asi zavedl stránkování. Takže by do ORM přibyly další dva parametry - limit a offset. Další zbytečné API k něčemu, co všichni dokážou snadno udělat v SQL (musí to umět už proto, aby rozuměli tomu, jak správně používat toto ORM).

ORM jsou snad jedny z nejvíc leaky abstractions vůbec. Proto ani já nejsem velkým příznivcem ORM (býval jsem, dokud jsem to nezačal pořádně používat).

ORM nepomůže ve složitějších případech. Pouze v těch jednoduchých, kde jejich pomoc není potřeba.

ikona Jakub Vrána OpenID:

Já taky nejsem příznivcem ORM a jak v článku píšu, tak žádné v PHP ani nepoužívám.

Ale vadí mi, že je většina ORM napsaná špatně – buď automaticky načítají zbytečně hodně dat, která nejsou potřeba, nebo naopak pokládají nekonstantní množství dotazů do databáze. Oboje aplikaci zdržuje. Článek představuje myšlenku, jak by se ORM daly opravit.

ikona karf:

Tak já např. jsem příznivcem ORM a zmíněnou techniku dokonce používám :) Nicméně (a to spíš reaguju na llooka) tohle už není věc, kterou by dokázala ORM vrstva vyřešit obecně a automaticky. Rozhodnutí, kdy je lepší lazy loading a kdy naopak natáhnout vše jedním dotazem, kdy a jak seskupovat dotazy do DB atd., záleží na konkrétní situaci a je lepší, když je řeší člověk podle potřeby. ORM nepoužívám primárně kvůli odstínění od SQL, ale kvůli automatické správě a persistenci objektů. (P.S.: nepoužívám frameworky typu Propel, ezpdo či Doctrine, to už je moc vlezlé)

LLook:

Článek představuje myšlenku, která vychází z předpokladu, že pokud na jednom řádku volám getAll(), tak ji budu chtít volat i na všech ostatních. Jenže to vždy platit nemusí a v takovém případě načítáme zbytečně hodně dat, která nejsou potřeba. Zavání to předčasnou optimalizací, která také bývá nazývána kořenem zla programování.

Ta ORM, která při každém getAll() vyvolávají nový dotaz na databázi, se podle mě chovají lépe. Jen to jejich uživatel musí vědět a zařídit se podle toho. Opravit podle mě nepotřebuje ORM, ale ten kód, který ho využívá.

Špatně se podle mě chovají naopak ta ORM, která načítají předem věci, o které nebyla požádána.

ikona Jakub Vrána OpenID:

Podle vlastní zkušenosti můžu říct, že pokud jsou závislá data potřeba u jednoho řádku, jsou v naprosté většině případů potřeba i u všech ostatních. Pokud by hrozilo, že tomu tak nebude, mohlo by to ORM samozřejmě řešit (buď přidáním parametru do getAll() nebo explicitním osamostatněním řákdu).

O předčasnou optimalizaci se nejedná. Django (Python framework) získává data pro jednotlivé záznamy a u běžných webových aplikací to je hlavní příčina jeho pomalosti - na jednu stránku se totiž položí stovky až tisíce dotazů (bez kešování se v podstatě nedá přežít). ORM se stejným konceptem mají stejný problém.

Podle mě je největší výhoda ORM v tom, že můžu psát kód přirozeně: vrať mi všechny autory od A a k nim všechny jejich knihy s vydavatelem. Pokud mi ORM kód nedovolí psát přirozeně a musím mu ho uzpůsobovat, tak ztrácí kouzlo.

avatar:

Človeče čo to tu melieš. Na efektívny prevod mi postačujú dve veci. Deklaratívna deklarácia vazieb medzi Entitami a Engine, ktorý na základe toho vygeneruje objektový strom. Vygenerované SQL je potom zmes OUTER / INNER JOINOV. Na strane klienta sa potom prechádza výsledný záznam a podľa hodnôt identifikátorov sa zostavuje objektový strom. Nejako som vôbec ale vôbec nepochopil zmysel tohoto článku.

ikona Jakub Vrána OpenID:

Když něčemu nerozumíš, tak se vyjadřuj pokorně.

Tebou popisovaný postup je druhý extrém - pokládá málo dotazů, ale získává extrémní množství dat, které se třeba na nic nepoužijí.

Představ si, že bys chtěl získat samotný seznam autorů - tebou popisovaný postup by i v tom případě získal i všechny jejich knihy a jejich vydavatele (což by se pak na nic nepoužilo).

ffrr:

mala poznamocka: vsimol som si, ze vo funkcii getAll() pouzivas 'IN ()' na mieste, kde ti pri vacsom pocte vysledkov sposobi kriticku performance penalty, v naozaj extremnom pripade prekrocenie maximalnej dlzky query. IN () ma svoje miesto, ale pausalne pouzivanie tejto funkcie je imho cesta do pekiel - vo vacsine situacii je riesenim prave join, ktory sice v pripade outer joinu vracia redundantne data, ale je neporovnatelne rychlejsi ako IN ().
dalsia poznamocka: pokial viem, orm engine v djangu bol medzi verziami 0.96 a 1.0 masivne prekopany k lepsiemu (ale neskumal som ;)

ikona Jakub Vrána OpenID:

Podpoř prosím svůj příspěvek nějakým odkazem na smysluplné výkonnostní srovnání.

Bez odkazu na nějaké konkrétní srovnání je takovýto příspěvek jen FUD. Skutečnost je totiž taková, že alespoň v MySQL je IN velmi výkonné. Dokonce se třeba místo id >= 1 AND id <= 5 doporučuje používat id (1,2,3,4,5). Maximální délka dotazu se dá nastavit, defaultní délka by se s číselnými primárními klíči navíc překročila až někde u 150000 záznamů.

ffrr:

nuz na viac velmi nemam casu, ale tu je kratucky test.

tabulka Employee - 13000 riadkov.
tabulka Calendar_Entry - 95000 riadkov.

Calendar_Entry ma relaciu / foreign key na Employee skrz EmployeeID, obe tabulky pouzivaju engine InnoDB.

Porovnam nasledovne dva selekty. Jeden z nich je vystup, aky by vyprodukoval tvoj priklad pri volani getAll, druhy je obycajny join.

1. SELECT EntryID  FROM Calendar_Entry WHERE EmployeeID IN (1,2,3 ... 13000); (podotykam, ze uz len samotna query ma 75 kB)

Exec time: 1203ms

2. SELECT EntryID FROM Calendar_Entry INNER JOIN Employee USING (EmployeeID);

Exec time: 77ms.

ikona Jakub Vrána OpenID:

Napadá mě jediné využití dotazu s tak velkým množstvím záznamů a to jsou exporty. Na normální stránce samozřejmě nebudu získávat všech 13000 záznamů a k nim všechny související.

Ale když si tuhle nereálnost odmyslím, tak se může jednat o zajímavé řešení, které by ale bohužel bylo na naprogramování ještě složitější. Pokud jsem to dobře pochopil, mají být výsledkem tyto dotazy:

SELECT * FROM author WHERE name LIKE 'a%';
SELECT book.* FROM book INNER JOIN author ON book.author = author.id WHERE author.name LIKE 'a%' GROUP BY book.id;
SELECT publisher.* FROM publisher INNER JOIN book ON publisher.id = book.publisher INNER JOIN author ON book.author = author.id WHERE author.name LIKE 'a%' GROUP BY publisher.id;

Z dotazů je vidět slabina tohoto přístupu – podmínka z prvního dotazu se pořád dokola vyhodnocuje i u dalších dotazů. Záleží samozřejmě na konkrétním využití, ale troufl bych si tipnout, že v 80 % případů bude lepší varianta s IN.

ffrr:

beriem, je to extremny priklad, len som chcel poukazat na slabinu IN() - je pravda, ze vo vacsine volani budes napr. strankovat - nebude ta zaujimat kompletny set zaznamov a do IN() pojde omnoho mensi pocet id.

Zav:

Strankovanim se tomuto nevyhnete, beztak potrebujete znat konecny pocet vyhovujicich zaznamu(SELECT COUNT(*) ... id IN(1, 2, 3, .. 13000))... Osobne pouzivam IN jenom v pripadech jako je filtrovani stavu bud z ciselnikove tabulky, nebo preddefinovanych poli.

Lukáš Kubánek:

Dovolím si znovu otevřít diskuzi k tomuto článku.

Především bych chtěl podotknout, že volba ORM nástroje závisí vždy na konkrétním případě. Mnohdy opravdu nemá cenu mapovat záznamy z relační databáze na doménové objekty. Přiznám se ale, že se k vývoji aplikací v PHP i přesto snažím přistupovat správně objektově za každou cenu. Využívám pro to tedy robustnější ORM nástroj Doctrine (http://doctrine-project.org), který podobné problémy (lazy loading/natažení všeho) řeší následujícím způsobem:

Nejprve je v aplikaci nadefinována závislost doménových objektů na tabulky a sloupce v databázi, přičemž jsou popsány i vzájemné relace mezi nimi.

Pokud vím, která data chci pro mé doménové objekty z databáze natáhnout, docílím toho takovým počtem dotazů, jaký bych použil v normálním případě. Pomocí objektového přístupu k SQL dotazům (dp Object Query), který se v Doctrine nazývá DQL (Doctrine Query Language), sestavím kompletní dotaz, ve kterém si najoinuji tabulky, ze kterých vyberu jen potřebné data.

<?php

$authors
= Doctrine_Query::create()
     ->select('a.id, a.name, b.id, b.title, p.id, p.name')
     ->from('author a')
     ->where('a.name LIKE ?', 'A%')
     ->leftJoin('a.book b')
     ->leftJoin('b.publisher p')
     ->execute();

/*
vygeneruje následující dotaz:
SELECT a.id AS a__id, a.name AS a__name, b.id AS b__id, b.title AS b__title, p.id AS p__id, p.name AS p__name
FROM authors a
WHERE a.name LIKE 'A%'
LEFT JOIN books b ON a.id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.id
*/

?>

Doctrine sama vrátí namapované definované doménové objekty. Podle vzájemných relací (a názvů rolí) pak můžu procházet doménové objekty následovně:

<?php

foreach ($authors as $author) {
     echo $author->name . '\n';
     foreach($author->books as $book) {
          echo $book->title . ' - ';
          echo $book->publisher->name . '\n';
     }
     echo '\n';
}

/*
vypíše:
Aleš
Kniha 1 - Nakladatelství 21
Kniha 2 - Nakladatelství 22

*/

?>

Výhodou je, že můžu okamžitě používat business logiku těchto objektů, protože objekty nereprezentují pouhá data z databáze!

ikona Jakub Vrána OpenID:

Doctrine je široce používané řešení, tento přístup se mi ale nelíbí ze dvou důvodů:

1. Opakovaně se přenáší už jednou přenesená data. Viz obrázek na http://php.vrana.cz/srovnani-dotazu-do-zavislych-tabulek.php, z toho je tento problém pěkně vidět.

2. Když chci přidat data z další tabulky, musím to napsat na dvě místa – do dotazu a na místo, kde s tím pracuji. Systém je tedy dost primitivní a místo automatického mapování mu musím radit, co má mapovat a co ne.

Elegance konstantního počtu dotazů spočívá v tom, že se přenáší minimální objem dat (i když ve více round-tripech) a že dopředu nemusím říkat, o co budu mít zájem.

Lukáš Kubánek:

Nejprve, než se k odpovědi vyjádřím, chtěl bych opravit první joinovou část dotazu, kde je logičtější místo a.book použít a.books, protože se jedná o relaci 1:N.

1. Nezbývá mi, než souhlasit.

2. Pokud chci natáhnout data, musím vědět která. Pokud to nevím, je možné je dotáhnout lazy loadem. Pokud chci natáhnout data do definové business logiky, která je představována doménovými objekty, stačí mi přidat výběr potřebných dat do dotazu. Pod místem, kde s daty pracuji, si představuji doménové objekty, kterým již ale znovu nemusím vysvětlovat, jak s daty pracují.

Jsem rád, že práce s databází je pro mě odstíněna.
avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.