SET disponible = TRUE
WHERE id = OLD.animal_id;
UPDATE VM_Revenus_annee_espece
SET somme = somme - OLD.prix, nb = nb - 1
WHERE annee = YEAR(OLD.date_reservation)
AND espece_id = (SELECT espece_id FROM Animal WHERE id =
OLD.animal_id);
DELETE FROM VM_Revenus_annee_espece
WHERE nb = 0;
END |
DELIMITER ;
Gain de performance
Tables vs vue vs vue matérialisée
On va tester les performances des vues et des vues matérialisées en créant trois procédures stockées différentes, répondant à la
même question : quelle est l'année ayant rapporté le plus en termes d'adoption de chats ?
Les trois procédures utiliseront des objets différents :
l'une fera la requête directement sur les tables ;
l'autre fera la requête sur la vue ;
la dernière utilisera la vue matérialisée.
Nos tables contenant très peu de données, la requête sera répétée un millier de fois, afin que les temps d'exécution soient
utilisables.
Empêcher MySQL d'utiliser le cache
Lorsqu'on exécute la même requête plusieurs fois de suite, MySQL ne l'exécute en réalité qu'une seule fois, et stocke le résultat
en cache. Toutes les fois suivantes, MySQL ressort simplement ce qu'il a en cache, ce qui ne prend quasiment rien comme temps.
Pour faire des tests de performance, cela pose évidemment problème, puisque cela fausse le temps d'exécution. Heureusement, il
existe une clause spéciale, qui permet d'empêcher MySQL de stocker le résultat de la requête en cache : SQL_NO_CACHE.
Les trois procédures
Sur les tables
On utilise SELECT... INTO afin de stocker les résultats dans des variables, plutôt que de les afficher mille fois.
Code : SQL
DELIMITER |
CREATE PROCEDURE test_perf_table()
BEGIN
DECLARE v_max INT DEFAULT 1000;
DECLARE v_i INT DEFAULT 0;
DECLARE v_nb INT;
DECLARE v_somme DECIMAL(15,2);
DECLARE v_annee CHAR(4);
boucle: LOOP
IF v_i = v_max THEN LEAVE boucle; END IF;
-Condition d'arrêt de la boucle
SELECT SQL_NO_CACHE YEAR(date_reservation) AS annee,
SUM(Adoption.prix) AS somme,
COUNT(Adoption.animal_id) AS nb
INTO v_annee, v_somme, v_nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
370/414
www.openclassrooms.com
WHERE id = OLD.animal_id;
UPDATE VM_Revenus_annee_espece
SET somme = somme - OLD.prix, nb = nb - 1
WHERE annee = YEAR(OLD.date_reservation)
AND espece_id = (SELECT espece_id FROM Animal WHERE id =
OLD.animal_id);
DELETE FROM VM_Revenus_annee_espece
WHERE nb = 0;
END |
DELIMITER ;
Gain de performance
Tables vs vue vs vue matérialisée
On va tester les performances des vues et des vues matérialisées en créant trois procédures stockées différentes, répondant à la
même question : quelle est l'année ayant rapporté le plus en termes d'adoption de chats ?
Les trois procédures utiliseront des objets différents :
l'une fera la requête directement sur les tables ;
l'autre fera la requête sur la vue ;
la dernière utilisera la vue matérialisée.
Nos tables contenant très peu de données, la requête sera répétée un millier de fois, afin que les temps d'exécution soient
utilisables.
Empêcher MySQL d'utiliser le cache
Lorsqu'on exécute la même requête plusieurs fois de suite, MySQL ne l'exécute en réalité qu'une seule fois, et stocke le résultat
en cache. Toutes les fois suivantes, MySQL ressort simplement ce qu'il a en cache, ce qui ne prend quasiment rien comme temps.
Pour faire des tests de performance, cela pose évidemment problème, puisque cela fausse le temps d'exécution. Heureusement, il
existe une clause spéciale, qui permet d'empêcher MySQL de stocker le résultat de la requête en cache : SQL_NO_CACHE.
Les trois procédures
Sur les tables
On utilise SELECT... INTO afin de stocker les résultats dans des variables, plutôt que de les afficher mille fois.
Code : SQL
DELIMITER |
CREATE PROCEDURE test_perf_table()
BEGIN
DECLARE v_max INT DEFAULT 1000;
DECLARE v_i INT DEFAULT 0;
DECLARE v_nb INT;
DECLARE v_somme DECIMAL(15,2);
DECLARE v_annee CHAR(4);
boucle: LOOP
IF v_i = v_max THEN LEAVE boucle; END IF;
-Condition d'arrêt de la boucle
SELECT SQL_NO_CACHE YEAR(date_reservation) AS annee,
SUM(Adoption.prix) AS somme,
COUNT(Adoption.animal_id) AS nb
INTO v_annee, v_somme, v_nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
370/414
www.openclassrooms.com