Dans l’article Découpage de gros traitements, j’évoquais le fait de traiter par paquets une insertion massive de données.
Mais sans aller chercher si compliqué, il est, de manière beaucoup plus courante, très utile de découper par lots des traitements tels que la purge de données.
Prenons par exemple une table contenant un million d’enregistrements, concernant les données depuis un certain nombre d’années.
create table MesDonneesHistoriques (Id bigint identity PRIMARY KEY,
DateEvenement datetime,
timestamp,
GUID uniqueidentifier DEFAULT NEWID(),
Chaine varchar(MAX) DEFAULT REPLICATE('TEST',100),
Longueur AS LEN(Chaine) PERSISTED)
Supposons ici que nous cherchons à faire du ménage, et à supprimer par exemple toutes les données antérieures à l’année 2005.
Une première approche consisterait à écrire une simple requête d’effacement :
delete from MesDonneesHistoriques where DateEvenement<'20050101'
Syntaxe simple, mais pas forcément très efficace, puisque la durée d’exécution est constatée à près de 4 minutes sur la machine sur lequel le test a été réalisé et qu’il s’agit surtout de 4 minutes pendants lesquelles la table est complètement bloquée.
SQL Server Execution Times: CPU time = 17720 ms, elapsed time = 230140 ms.
Un tout petit peu plus compliqué, mais nettement plus efficace, surtout dans le cas de bases de données en mode de récupération simple, le traitement par lot permet de faire le même travail.
set nocount on declare @Depart int select @Depart=1 -- Pour fixer un @@ROWCOUNT>0 WHILE @@ROWCOUNT>0 delete TOP (1000) from MesDonneesHistoriques where DateEvenement<'20050101'
Cette fois-ci, la durée d’exécution totale constatée est d’environ 8 minutes.
C’est effectivement plus long, mais ce qu’il faut surtout retenir, c’est qu’entre chaque lot, les éventuels autres traitements tournant en parallèle peuvent passer, et il y a donc un impact bien moindre sur les autres processus applicatifs. Dans notre cas (377000 lignes effacées environ), la suppression s’est faite en 377 lots ce qui en faisant la division, nous donne une série de petit blocages d’environ 1.25 seconde.
Les valeurs de performances citées ci-dessus ont été relevées sur une machine virtuelle plutôt sous-dimensionnée, mais la règle est tout de même à retenir et surtout à appliquer sur des serveurs de production : lorsque l’on souhaite faire une purge massive, il est nettement préférable d’y aller petit bout par petit bout, plutôt que comme une brute …
en spécifiant les deux paramétres de mesures « set statics time on » et set statics io on » cette requépte présente plus d’IO disque et le temps d’exécution est plus élevé
donc c’est quoi le gain d’utiliser le variable globale @@ROWCOUNT
Dans tout les cas c’est un article très important qui pointe sur un élément très interéssant dans le domaine du sql server sur la partie supression
Bonjour,
La variable @@ROWCOUNT permet d’arrêter la boucle de traitement lorsqu’il n’y a plus d’information à traiter. Néanmoins, on peut tout à fait s’appuyer sur un autre compteur, suivant le cas métier.
@@ROWCOUNT permet, au prix d’un passage à vide superflu, de ne pas avoir à chaque étape à compter combien d’enregistrements restant à traiter.
En ce qui concerne le nombre d’IO et la durée d’exécution supérieurs à ceux d’un traitement massif en une fois, cela est tout à fait normal, puisque SQL Server « préfère » en général les traitements massifs.
Néanmoins, ce qu’il faut bien comprendre, c’est qu’un traitement massif peut bloquer la table traitée pendant de nombreuses secondes voire pendants plusieurs minutes en cas de très grosses purge. Et sur un environnement de production utilisé 24h/24, ce genre de blocage n’est tout simplement pas acceptable. D’où le traitement pas petits lots, qui ne perturbent individuellement que pendant des temps très courts, et laissent passer les requêtes métier entre deux lots.
JN.