Comment supprimer efficacement et rapidement un grand nombre de lignes dans de grandes tables avec des critères complexes. Cet article fait le point sur une méthode fiable et classique, connue des spécialistes des VLDB…
A partir d’un post (http://www.developpez.net/forums/d793894/bases-donnees/ms-sql-server/contribuez/accelerer-operation-delete-complexe/) d’un internaute qui s’étonnait de la lenteur de suppressions des lignes dans une table :
Suprimer des enregistrements selon des critères particuliers (par exemple : est-ce que ma clé primaire apparient à telle sous liste) peut s’avérer hélas étonnament lent.
Et j’ai finit par me rendre compte qu’il pouvait être préférable de réduire les conditions de suppression un maximum et de réintroduire (insert) les enregistrements qui n’aurait pas du être supprimés.
Bien entendu une telle méthode est très critiquable, car en dehors des problèmes soulevés par une éventuelle intégrité référentielle on peut imaginer que des calculs comptables ou statistiques se basent sur l’état intermédiaire des données (avant la réintroduction des lignes et après la suppression massive) ou bien que cela suppose une transaction hautement bloquante par un niveau d’isolation maximale qui rendrait encore plus contre performant le traitement pour tous les utilisateurs concurrents.
Autre tentative, suggérée par un internaute : « Serait-il intéressant de déactiver des contraintes temporairement pour accélérer des suppressions ? »
De la même manière, pendant le temps de la suppression, la base pourrait allégrement être pourrie par des mises à jour en parallèle….
Non, il faut jouer AVEC les contraintes et non pas CONTRE les contraintes.
C’est dans cet esprit qu’il existe en revanche une méthode efficace et sérieuse adoptée par une grande parties des éditeurs travaillant dans avec des VLDB (Very large DataBases, c’est à dire des bases dans lequel le volume des données est souvent de plus de 1 Tera octet).
Cette méthode consiste à utiliser pleinement les capacités du modèle relationnel dans ses moindre détails et repose sur une combinaison de vues (en principe les applications ne doivent jamais attaquer les bases avec des requêtes sur tables, mais uniquement sur des vues) et de batch.
Le principe est le suivant :
* toute entité est représenté par une vue dans laquelle sont effectuées des suppressions logiques.
* aux heures creuses, les suppressions logiques sont transformées en suppression physiques
Le tout est possible si le SGBDR accepte les options ON DELETE SET DEFAULT et ON DELETE SET NULL que la norme SQL propose dans le cadre de la gestion de l’intégrité référentielle.
Démonstration…
Cette démo est codée sous MS SQL Server, mais peut être facilement adaptée à tout bon SGBDR.
1 Рcr̩ation de la base :
CREATE DATABASE VLDB;
GO
USE VLDB;
GO
2 Рcr̩ation des tables :
CREATE TABLE T_CLIENT
(CLI_ID INT NOT NULL PRIMARY KEY,
CLI_NOM VARCHAR(16));
GO
CREATE TABLE T_FACTURE
(FAC_ID INT NOT NULL PRIMARY KEY,
CLI_ID INT NOT NULL DEFAULT 0
REFERENCES T_CLIENT (CLI_ID)
ON DELETE SET DEFAULT,
FAC_DATE DATE NOT NULL DEFAULT GETDATE());
GO
Notez que dans la table des factures, la colonne CLI_ID à pour valeur par défaut le client 0.
3 РCr̩ation des vues :
CREATE VIEW V_CLIENT
AS
SELECT *
FROM T_CLIENT
WHERE CLI_ID > 0;
GO
CREATE VIEW V_FACTURE
AS
SELECT *
FROM T_FACTURE
WHERE CLI_ID > 0
GO
Ces vues ne filtrent que les « vrais » client et doivent être systématiquement utilisée pour tout le développement de l’application qui ne doit JAMAIS accéder directement aux tables.
4 – Jeu de test :
INSERT INTO T_CLIENT VALUES (0, '<client bidon>');
INSERT INTO T_CLIENT
VALUES (1, 'DUPONT'),
(2, 'MARTIN'),
(3, 'JOHNSON');
INSERT INTO T_FACTURE
VALUES (1, 1, '2000-01-01'),
(2, 1, '2002-02-02'),
(3, 1, '2003-03-03'),
(4, 1, '2004-04-04'),
(5, 2, '2005-05-05');
5 – Test de suppression avec cascade de l’IR :
DELETE FROM V_CLIENT WHERE CLI_ID = 1;
Vérifications
SELECT * FROM T_CLIENT
CLI_ID CLI_NOM
----------- ----------------
0 <client bidon>
2 MARTIN
3 JOHNSON
le client 1 est réellement supprimé
SELECT * FROM V_FACTURE
FAC_ID CLI_ID FAC_DATE
----------- ----------- ----------
5 2 2005-05-05
Les factures du client 1 apparaissent comme réellement supprimées
SELECT * FROM T_FACTURE
FAC_ID CLI_ID FAC_DATE
----------- ----------- ----------
1 0 2000-01-01
2 0 2002-02-02
3 0 2003-03-03
4 0 2004-04-04
5 2 2005-05-05
Les factures du client 1 ont été reportées au client 0 (bidon).
Le batch de nuit fera le nettoyage physiques des lignes et reconstruira les index.
Enfin, n’oubliez jamais que la vitesse de suppression dépend grandement des index qui sont posés. S’il existe un index couvrant et « sargeable » pour la suppression, alors celle-ci sera rapide. Sinon, il faut balayer toute la table !
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
