Double_Pass
-
class ITRocks\Framework\Dao\Option\Double_Pass
Cette option du Dao peut être appliquée à Dao::select() pour activer l’optimisation “lecture en double-passe” sur la requête générée.
Cette optimisation peut multiplier les performances en lecture lorsque vous utilisez select pour rechercher ou lire des données provenant de plusieurs tables, notamment impliquant des jointures complexes.
Elle est notamment utilisée en interne par le widget Data_List par défaut, où seules les jointures nécessitées par les recherches, les regroupements, et les relations 1-à-n sont conservées pour la lecture des enregistrements qui vont être affichées, les objets liés n’étant nécessaires que pour les 20 premières lignes (ou 100, ou 1000, mais jamais pour l’ensemble des données en base). On observe sur des tables de 100.000 à 1.000.000 d’enregistrements des performances multipliées par 3 ou 4 selon les cas en appliquant cette optimisation.
Cas d’utilisation
Cette optimisation n’est utile que :- Lorsque vous n’avez pas la main sur la liste des propriétés demandées en retour, et que celles-ci peuvent générer potentiellement une multitude de jointures. C’est le cas dans le widget Data_List.
- Lorsque vous avez explicitement demandé des propriétés en réponse qui vont se traduire en jointures qui pourraient être évitées en première passe.
Le mieux en cas de doute est de tester votre appel sur des données à volumétrie type de votre environnement de production, pour constater si l’application de cette option se traduit bien en un gain notable de performances.
Cas d’école par l’exemple
L’exemple ci-dessous explique les dessous techniques de l’optimisation, afin d’en comprendre le mécanisme.
Sans optimisation
Si l’on souhaite afficher les commandes stockées en base de données 20 par 20 mais en comptant le nombre total de lignes, en détaillant entre autres ses lignes, dont le code article, et le nom du client, on peut exécuter la requête suivante :
$data = Dao::select( Order::class, ['number', 'date', 'client.name', 'lines.caption', 'lines.item.code'], ['date' => Func::range('2016-01-01 00:00:00', '2016-12-31 23:59:59')], [Dao::count(), Dao::limit(20), Dao::sort()] );
La traduction de cette requête en SQL va générer quelques de jointures, coûteuse en performance de base de données :
# exécutée en .091 secondes SELECT SQL_CALC_FOUND_ROWS t0.`number`, t0.`date`, t1.`name` AS `client.name`, t2.`caption` AS `lines.caption`, t3.`code` AS `lines.item.code`, t0.`id` FROM `sales_orders` t0 INNER JOIN `clients` t1 ON t1.id = t0.id_client LEFT JOIN `sales_orders_lines` t2 ON t2.id_sales_order = t0.id LEFT JOIN `items` t3 ON t3.id = t2.id_item WHERE (t0.`date` BETWEEN "2016-01-01 00:00:00" AND "2016-12-31 23:59:59") ORDER BY t0.`number` LIMIT 0, 20; SELECT FOUND_ROWS();
Avec optimisation
Comme on travaille sur l’ensemble des commandes, notamment pour compter le nombre de lignes, on pourrait gagner largement en performances en limitant dans un premier temps cette requête sur l’ensemble des données aux propriétés dont on a besoin pour le compte des résultats et récupérer les identifiants d’origine. Une deuxième requête pourra ensuite compléter les données dont on a besoin, mais uniquement pour les enregistrements nécessaires, ce qui ira donc beaucoup plus vite.
$data = Dao::select( Order::class, ['number', 'date', 'client.name', 'lines.caption', 'lines.item.code'], ['date' => Func::range('2016-01-01 00:00:00', '2016-12-31 23:59:59')], [Dao::count(), Dao::doublePass(), Dao::limit(20), Dao::sort()] );
On a alors deux requêtes SQL :
# exécutée en .053 secondes SELECT SQL_CALC_FOUND_ROWS t1.`caption` AS `lines.caption`, t0.`id` FROM `sales_orders` t0 LEFT JOIN `sales_orders_lines` t1 ON t1.id_sales_order = t0.id WHERE (t0.`date` BETWEEN "2016-01-01 00:00:00" AND "2016-12-31 23:59:59") ORDER BY t0.`number` LIMIT 0, 20; SELECT FOUND_ROWS(); # deuxième requête : résultat immédiat (moins de 1 millième de seconde) : SELECT t0.`number`, t0.`date`, t1.`name` AS `client.name`, t2.`caption` AS `lines.caption`, t3.`code` AS `lines.item.code`, t0.`id` FROM `sales_orders` t0 INNER JOIN `clients` t1 ON t1.id = t0.id_client LEFT JOIN `sales_orders_lines` t2 ON t2.id_sales_order = t0.id LEFT JOIN `items` t3 ON t3.id = t2.id_item WHERE t0.`id` IN (184549, 184519, 184449, 184419, 184409, 184379, 184369, 184359, 184539, 184529, 184509, 184499, 184489, 184479, 184479, 184469, 184459, 184459, 184439, 184429) ORDER BY t0.`number` LIMIT 0, 20;
On a multiplié ici presque par 2 les performances sur une table de 35000 lignes de commandes prise pour l’exemple. Sur de plus grosses tables le gain de performances augmente de façon exponentielle.
Notes concernant cet exemple d’optimisation
- Les jointures avec les clients(clients) et les articles (items) sont retirées en première passe, car ne servent que pour la récupération des données. C’est là qu’on gagne en performances.
- La jointure avec les lignes (sales_orders_lines) a été conservée car on va avoir plusieurs lignes par commande, dont on aura besoin pour compter correctement le nombre total de lignes. Sans l’option Count, on cette jointure devient inutile en première passe et est retirée.
- On a gardé le
LIMIT
en deuxième passe car la première passe va avoir lu le nombre suffisant de commandes pour lire 20 lignes, mais les commandes pouvant avoir plusieurs lignes la seconde passe risque de lire un petit peu plus de 20 lignes, puisse qu’elle lit des commandes complètes.