Accélérer une opération DELETE complexe.

Salut,

Citation:

---

Tout d'abord je tiens à préciser que ce que je vais expliquer n'est pas (ou dangereusement) appliquable à des supression d'enregistrements auxquels fonrt référence (par une clé étrangère) d'autres enregistrements.
Je précise aussi qu'une (trop) grande complexité peut apparaître lorsque l'on manipule des tables avec des colonnes autoincrémentées (identity).

Ce que je vais donc raconter ne concerne que pour les cas (pas forcément moins fréquents) de tables d'autres natures.

---

Ceci ne sont que mes propres conclusions, suite à quelques recherches sur le net (sans réel succès) de pratiques d'optimisation.

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.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

// raw_result sont des éléments présents dans la table que je ne souhaite pas supprimer DECLARE @raw_result AS TABLE [...] INSERT @raw_result [...]   /* Je souhaite supprimer tous les enregistrement de GroupID 5 qui ne sont pas dans @raw_result Mais je procède plutôt à une suppression grossière suivie d'une réinsertion plutôt que de faire un supression ciblée */ DELETE FROM MaTable WHERE GroupID = 5   INSERT MaTable ( GroupID [...] ) SELECT 5 , [...] FROM @raw_result AS RAW WHERE RAW.GroupID = 5

---

Ce procédé est soumis à la lourde restriction qu'il soit possible de copier/retrouver les enregistrements à ne pas perdre lors de la supression grossière.

Ma conclusion est qu'une série de supressions et insertions aux conditions simples peuvent être bien moins coûteuses en terme de temps d'excécution qu'une seule opération dont la conditions est un tant soit peu complexe.
Et j'ai pu observer des diminution de temps d'excécution de l'ordre de 3-10 fois.

Intéressant.
Serait-il intéressant de déactiver des contraintes temporairement pour accélérer des suppressions?

La problématique de suppression que tu soulève est biaisé par le fait que tu pense physique alors que dans les grands systèmes et les VLDB on fait de la suppression logique en temps réel, puis de la physique en batch en temps différé. Même dans tes propos tu mélange les aspect logiques (tables, lignes...) et les aspects physiques (enregistrements....).

C'est pourquoi il existe les modes de gestion de L'IR ON DELETE SET NULL et ON DELETE SET DEFAULT.

Dans les grosses applications de ce style on monte des vues et on travaille exclusivement sur les vues. Dans ces vues on ne montrent jamais que les lignes actives et toutes les opérations d'UPDATE ou DELETE sont faites à travers les vues. Ensuite on va supprimer physiquement par batch la nuit.

Exemple :
Tables T_CLIENT et T_FACTURE liées par IR sur PRS_ID avec CLI_ID obligatoire et ON DELETE SET DEFAULT.
Insertion d'un client "bidon" dont la clef est 0 (CLI_ID)
Dans la table des factures, la colonne CLI_ID à pour valeur par défaut le client 0.

Détail du code :

création de la base :

Code:

---

1 2 3 4 5

CREATE DATABASE VLDB; GO   USE VLDB; GO

---

création des tables :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

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

---

Création des vues :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

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

---

Jeu de test :

Code:

---

1 2 3 4 5 6

INSERT INTO T_CLIENT VALUES (0, '<client bidon>');   INSERT INTO T_CLIENT VALUES (1, 'DUPONT'), (2, 'MARTIN'), (3, 'JOHNSON');

---

Code:

---

1 2 3 4 5 6

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');

---

Test de suppression avec cascade de l'IR :

Code:

---

DELETE FROM V_CLIENT WHERE CLI_ID = 1;

---

Code:

---

1 2 3 4 5 6 7

SELECT * FROM T_CLIENT   CLI_ID CLI_NOM ----------- ---------------- 0 <client bidon> 2 MARTIN 3 JOHNSON

---

le client 1 est réellement supprimé

Code:

---

1 2 3 4 5

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

Code:

---

1 2 3 4 5 6 7 8 9

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.


A +

Citation:

---

Envoyé par Farçous

Intéressant.
Serait-il intéressant de déactiver des contraintes temporairement pour accélérer des suppressions?

---

Ce serait amusant, comme cela 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. Voir mon post ci avant !

A +

Enfin, la vitesse de suppression dépend grandement des index qui sont posés. S'il existe un index couvrant pour la suppression, alors celle-ci sera rapide. Sinon, ce sera du scan !

A +

Intéressant, je vais y réfléchir.