JeuWeb Créez votre jeu par navigateur !

**Xenos** · 14-08-2019, 05:52 PM

Salutations,

je ne sais plus qui l'avait demandé, mais je suis tombé sur un script de test de performances des triggers que j'avais fait, à l'époque où quelqu'un (moi-même? non, je crois que c'était quelqu'un de normal!) m'avait posé la question: "mais les triggers alors, c'est lent ou pas?"

Voici donc le script:



# noinspection SqlUserVariableUsageForFile



DROP TRIGGER IF EXISTS perf_trig_before_update;

DROP TRIGGER IF EXISTS perf_trig_after_update;

DROP TABLE IF EXISTS perf_trig;

DROP TABLE IF EXISTS perf_trig_other;

DROP TABLE IF EXISTS perf_trig_result;

DROP TRIGGER IF EXISTS perf_trig_before_insert;

DROP TRIGGER IF EXISTS perf_trig_after_insert;

DROP TRIGGER IF EXISTS perf_trig_before_delete;

DROP TRIGGER IF EXISTS perf_trig_after_delete;



CREATE TABLE `perf_trig_result` (

	`act` VARCHAR(80) NOT NULL,

	`time` DOUBLE NOT NULL

)

ENGINE = InnoDB

;



CREATE TABLE `perf_trig` (

	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

	`edits` INT(10) UNSIGNED NOT NULL,

	`value` VARCHAR(80) NULL DEFAULT NULL,

	PRIMARY KEY (`id`)

)

ENGINE = InnoDB

;



CREATE TABLE `perf_trig_other` (

	`id` INT(10) UNSIGNED NOT NULL,

	PRIMARY KEY (`id`)

)

ENGINE = InnoDB

;



INSERT INTO perf_trig (id, edits) VALUES (1, 0), (2, 0), (3, 0), (4, 0);

INSERT INTO

	perf_trig (id, edits)

	(

		SELECT p1.id + p2.id * 4, 0

		FROM perf_trig AS p1

			INNER JOIN perf_trig AS p2 ON TRUE);

INSERT INTO

	perf_trig (id, edits)

	(

		SELECT p1.id + p2.id * 20, 0

		FROM perf_trig AS p1

			INNER JOIN perf_trig AS p2 ON TRUE);

INSERT INTO

	perf_trig (id, edits)

	(

		SELECT p1.id + p2.id * 420, 0

		FROM perf_trig AS p1

			INNER JOIN perf_trig AS p2 ON TRUE);

INSERT INTO perf_trig_other (id) (SELECT id FROM perf_trig);





SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITHOUT TRIGGER' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);





SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITHOUT TRIGGER (BIS)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

CREATE TRIGGER `perf_trig_before_update`

	BEFORE UPDATE

	ON `perf_trig`

	FOR EACH ROW

BEGIN

	SET new.edits = old.edits + 1;

END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (SET)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

DROP TRIGGER perf_trig_before_update;

CREATE TRIGGER `perf_trig_before_update`

	BEFORE UPDATE

	ON `perf_trig`

	FOR EACH ROW

BEGIN

	IF (new.value IS NULL) THEN

		SET new.edits = old.edits + 1;

	END IF;

END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (IF...SET)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

DROP TRIGGER perf_trig_before_update;

CREATE TRIGGER `perf_trig_before_update`

	BEFORE UPDATE

	ON `perf_trig`

	FOR EACH ROW

BEGIN

	IF (EXISTS(SELECT 1 FROM perf_trig_other WHERE id = new.id)) THEN

		SET new.edits = old.edits + 1;

	END IF;

END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 1 (BEFORE)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

CREATE TRIGGER `perf_trig_after_update`

	AFTER UPDATE

	ON `perf_trig`

	FOR EACH ROW

BEGIN

	IF (EXISTS(SELECT 1 FROM perf_trig_other WHERE id = new.id)) THEN

		SET @x = 1;

	END IF;

END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 2 (BEFORE + AFTER)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);





DROP TRIGGER `perf_trig_before_update`;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (IF EXISTS ... SET) x 1 (AFTER)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

DROP TRIGGER perf_trig_after_update$$

CREATE TRIGGER `perf_trig_after_update`

	AFTER UPDATE

	ON `perf_trig`

	FOR EACH ROW

BEGIN

END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGER (EMPTY)' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



DELIMITER $$

DROP TRIGGER perf_trig_after_update$$

CREATE TRIGGER `perf_trig_before_insert`

	BEFORE INSERT

	ON `perf_trig`

	FOR EACH ROW

BEGIN



END$$

CREATE TRIGGER `perf_trig_after_insert`

	AFTER INSERT

	ON `perf_trig`

	FOR EACH ROW

BEGIN



END$$

CREATE TRIGGER `perf_trig_before_delete`

	BEFORE DELETE

	ON `perf_trig`

	FOR EACH ROW

BEGIN



END$$

CREATE TRIGGER `perf_trig_after_delete`

	AFTER DELETE

	ON `perf_trig`

	FOR EACH ROW

BEGIN



END$$

DELIMITER ;



SET @microtime := UNIX_TIMESTAMP(NOW(6));

UPDATE perf_trig SET value = 'WITH TRIGGERS (EMPTY) x 4' WHERE TRUE;

INSERT INTO perf_trig_result (act, time)

VALUES ((SELECT VALUE FROM perf_trig LIMIT 1), UNIX_TIMESTAMP(NOW(6)) - @microtime);



SELECT * FROM perf_trig_result;

Et en voici les résultats:

Code :
act    time

WITHOUT TRIGGER    2.162516

WITHOUT TRIGGER (BIS)    1.412786

WITH TRIGGER (SET)    2.449268

WITH TRIGGER (IF...SET)    2.615878

WITH TRIGGER (IF EXISTS ... SET) x 1 (BEFORE)    7.850129

WITH TRIGGER (IF EXISTS ... SET) x 2 (BEFORE + AFTER)    12.180796

WITH TRIGGER (IF EXISTS ... SET) x 1 (AFTER)    9.335754

WITH TRIGGER (EMPTY)    2.583497

WITH TRIGGERS (EMPTY) x 4    1.65202

Que peut-on en dire?
- La présence d'un TRIGGER en lui-même (vide) n'influe pas les performances des requêtes
- Un trigger qui n'exécute pas de query SQL accédant aux tables (ie: un trigger type SET new.val = GREATEST(minval, old.val) A un léger impact, mais peu significatif (en gros, + ~20% de charge apparemment)
- Un trigger nécessitant un accès à une autre table va fatalement faire grimper le temps d'exécution (x3) mais c'est à mettre en comparaison avec le temps requis pour lire ces lignes (ie: faire le SELECT en dehors du TRIGGER revient grosso modo au même)

Si vous pouvez faire vous-même le test, je serai ravi d'avoir d'autres environnements pour comparer, mais si ce test (simplisite) est représentatif, alors les TRIGGER n'ont pas un surcoût collossal en eux-mêmes. Le surcout viendra surtout de l'action que vous voulez faire (IF/SET/SELECT) et non de la façon de le faire (TRIGGER, query à part, etc).

En revanche, c'est clair que cette remarque ne sera valide *que* ligne par ligne: entre faire un TRIGGER et faire un traitement de lot (SELECT de toutes les lignes, calcul sur toutes ces lignes, UPDATE de toutes les lignes résultats), le traitement de lot sera clairement plus rapide (d'où ma préférence pour les PROCEDURE mais c'est un autre sujet).

[PS: je voudrai surtout me concentrer sur l'impact de performance des triggers dans ce topic, si vous avez des avis sur l'impact en terme de maintenabilité, de réplication, de préférences/opinion personnelle, je vous prierai d'en faire un autre topic. En revanche, si vous avec des remarques, résultats, suggestions sur les perf' temps/mémoire/lock/whatever, elles sont les bienvenues Smile

]

Sujets apparemment similaires…
Sujet		Auteur	Réponses	Affichages	Dernier message
	Performances MySql	joshua	21	7 125	15-11-2006, 09:34 AM Dernier message: joshua