281
8.3 Optimisation du code SQL
Mais il faut aussi dire qu’avec la fonction de partitionnement il est facile de
recommencer la numérotation à chaque groupe, en modifiant la fonction
ROW_NUMBER() dans la CTE ainsi :
WITH cteTestLoop AS (
SELECT
id,
ROW_NUMBER() OVER(PARTITION BY groupe ORDER BY id) as rownumber
FROM dbo.testloop
) …
Ce qui serait difficile à imiter dans notre « bricolage », surtout pour garantir un
tri correct.
La fonction ROW_NUMBER() demande obligatoirement un ORDER BY. Vous pouvez
simplement utiliser n’importe quelle colonne, ou, si vous n’avez pas de colonne
pour générer cet ordre, vous pouvez utiliser l’astuce suivante : ROW_NUMBER()
OVER(ORDER BY (SELECT 0)).
8.3.4 Optimisation des déclencheurs
Les déclencheurs (triggers) sont des blocs de code SQL qui sont exécutés au déclenchement d’une opération DML sur une table : INSERT, UPDATE ou DELETE. Depuis
SQL Server 2005, existent aussi des déclencheurs DDL, qui s’exécutent lors de toute
modification de structures. Ils n’entrent pas dans le cadre de l’optimisation du
système, nous ne les aborderons donc pas ici.
Du point de vue de la compilation, les déclencheurs se comportent comme des
procédures stockées : leur plan d’exécution est mis en cache dans le cache de procédure à leur premier rappel, et réutilisé ensuite. Il n’y a donc pas recompilation systématique, sauf si le code lui-même provoque des recompilations en modifiant le
contexte. Il est important de garder le code à l’intérieur du déclencheur aussi court
et simple que possible : en effet, il s’exécute dans la transaction de l’instruction qui
l’a déclenché, et la prolonge donc d’autant, ce qui augmente la durée des verrous.
En SQL Server, les déclencheurs sont uniquement ensemblistes (par opposition à
certains SGBDR qui implémentent des déclencheurs « PER ROW »), cela veut dire
qu’ils se déclenchent une seule fois par instruction, quel que soit le nombre de lignes
affectées. Il est donc essentiel de penser en terme de requêtes ensemblistes dans un
déclencheur. Il est très commun de voir du code tel que celui-ci, dans un trigger :
CREATE TRIGGER atr_d$sales_currency$archive
ON sales.currency
AFTER DELETE
AS BEGIN
DECLARE @CurrencyCode NCHAR(3),
@Name NVARCHAR(50),
@DeletedDate smalldatetime
8.3 Optimisation du code SQL
Mais il faut aussi dire qu’avec la fonction de partitionnement il est facile de
recommencer la numérotation à chaque groupe, en modifiant la fonction
ROW_NUMBER() dans la CTE ainsi :
WITH cteTestLoop AS (
SELECT
id,
ROW_NUMBER() OVER(PARTITION BY groupe ORDER BY id) as rownumber
FROM dbo.testloop
) …
Ce qui serait difficile à imiter dans notre « bricolage », surtout pour garantir un
tri correct.
La fonction ROW_NUMBER() demande obligatoirement un ORDER BY. Vous pouvez
simplement utiliser n’importe quelle colonne, ou, si vous n’avez pas de colonne
pour générer cet ordre, vous pouvez utiliser l’astuce suivante : ROW_NUMBER()
OVER(ORDER BY (SELECT 0)).
8.3.4 Optimisation des déclencheurs
Les déclencheurs (triggers) sont des blocs de code SQL qui sont exécutés au déclenchement d’une opération DML sur une table : INSERT, UPDATE ou DELETE. Depuis
SQL Server 2005, existent aussi des déclencheurs DDL, qui s’exécutent lors de toute
modification de structures. Ils n’entrent pas dans le cadre de l’optimisation du
système, nous ne les aborderons donc pas ici.
Du point de vue de la compilation, les déclencheurs se comportent comme des
procédures stockées : leur plan d’exécution est mis en cache dans le cache de procédure à leur premier rappel, et réutilisé ensuite. Il n’y a donc pas recompilation systématique, sauf si le code lui-même provoque des recompilations en modifiant le
contexte. Il est important de garder le code à l’intérieur du déclencheur aussi court
et simple que possible : en effet, il s’exécute dans la transaction de l’instruction qui
l’a déclenché, et la prolonge donc d’autant, ce qui augmente la durée des verrous.
En SQL Server, les déclencheurs sont uniquement ensemblistes (par opposition à
certains SGBDR qui implémentent des déclencheurs « PER ROW »), cela veut dire
qu’ils se déclenchent une seule fois par instruction, quel que soit le nombre de lignes
affectées. Il est donc essentiel de penser en terme de requêtes ensemblistes dans un
déclencheur. Il est très commun de voir du code tel que celui-ci, dans un trigger :
CREATE TRIGGER atr_d$sales_currency$archive
ON sales.currency
AFTER DELETE
AS BEGIN
DECLARE @CurrencyCode NCHAR(3),
@Name NVARCHAR(50),
@DeletedDate smalldatetime