Discussion :

[Sergejack]

Salut,

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Farçous]

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

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE DATABASE VLDB;
GO
 
USE VLDB;
GO

création des tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

DELETE FROM V_CLIENT WHERE CLI_ID = 1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[SQLpro]

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 +

[SQLpro]

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 +

[Sergejack]

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