Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

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…

 

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié mardi 19 décembre 2006 23:26 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01