SQL Server vu par Christian Robert

SQL Server 2005 : Améliorations de la clause TOP et DELETE de grandes tables

Rien de particulier me direz-vous concernant le TOP, mais SQL Server 2005 a apporté quelques améliorations à cette clause.

-- Seule possibilité sous SQL Server 7 & 2000
SELECT TOP 200 ChampA, ChampB
FROM MaTable
ORDER BY ChampC;

-- Supporté par SQL Server 2005
SELECT TOP (200) ChampA, ChampB
FROM MaTable
ORDER BY ChampC;

La clause TOP permet de passer une variable en argument au lieu comme c'était le cas dans les versions précédentes d'un nombre, voici un exemple de procédure stockée :

CREATE PROCEDURE MaProc @nb int
AS
SELECT TOP (@nb) ChampA, ChampB
FROM MaTable
ORDER BY ChampC
GO

Autre possibilité celle de passer cette même clause sur DELETE, INSERT et UPDATE :

UPDATE TOP (1) MaTable
SET Nom = 'ABCDEF'

Cette requête modifie un enregistrement de manière aléatoire dans la table, cependant cet aspect aléatoire est assez limité étant donné que si vous exécutez la même requête, c'est exactement le même enregistrement qui est impacté (si aucun enregistrement n'est inséré entre temps dans la table ou si aucune modification causant de la fragmentation n'est faite).

Beaucoup plus utile la clause TOP sur le DELETE permet d'écrire un Batch de suppression de table… Comme exemple j'ai une table (MaTable) comportant près de 6 millions d'enregistrements :

-- Suppression des enregistrements par bloc de 100000
-- Valeur à ajuster suivant vos besoins
WHILE EXISTS(SELECT * FROM XXX)
BEGIN

DELETE TOP(100000) FROM XXX

END

Cette opération peut se révéler lente (même beaucoup plus lente qu'un TRUNCATE TABLE), mais si vous avez un grand nombre d'enregistrements à supprimer sur une table en production et que celle-ci est lié via des clefs étrangères à d'autres c'est la seule solution viable. Je vous conseille d'ajouter la valeur du batch en fonction des besoins.

En cas d'exécution simple d'un DELETE, ma table faisant 161 Mo, ce qui fait que l'opération va au minimum consommer cette quantité de mémoire, ce qui est très important… le fait de découper l'opération va limiter la consommation de mémoire ainsi que les écritures effectuées sur le disque. Si je divise en bloc de 100 000 enregistrements je consomme 67 fois moins de mémoire que dans l'autre cas !

En termes de temps le DELETE TOP en boucle met 58 secondes à s'exécuter, tandis que le DELETE FROM MaTable simple effectue l'opération 39 secondes, la différence n'est donc trop importante.

Pour comparer le TRUNCATE TABLE est quasi instantané tout comme le DROP TABLE (les 2 opérations étant quasiment identiques pour le moteur) et consomme une quantité minimale de mémoire… mais… il faut des droits importants pour l'exécuter, il ne doit pas y avoir de clefs étrangère dans la table, et la trace laissée dans le journal de transaction est limitée.

Bonnes suppressions…