IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez SQL Server Discussion :

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


Sujet :

Contribuez SQL Server

  1. #1
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 198
    Points
    1 198
    Par défaut Accélérer une opération DELETE complexe.
    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.
    Most Valued Pas mvp

  2. #2
    Membre à l'essai
    Profil pro
    Inscrit en
    septembre 2009
    Messages
    19
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : septembre 2009
    Messages : 19
    Points : 23
    Points
    23
    Par défaut
    Intéressant.
    Serait-il intéressant de déactiver des contraintes temporairement pour accélérer des suppressions?

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 675
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 675
    Points : 48 998
    Points
    48 998
    Billets dans le blog
    1
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 675
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 675
    Points : 48 998
    Points
    48 998
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Farçous Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 675
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 675
    Points : 48 998
    Points
    48 998
    Billets dans le blog
    1
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 198
    Points
    1 198
    Par défaut
    Intéressant, je vais y réfléchir.
    Most Valued Pas mvp

Discussions similaires

  1. Trace d'une opération delete record
    Par azouzmenai24 dans le forum Oracle
    Réponses: 4
    Dernier message: 15/02/2015, 09h19
  2. Réponses: 2
    Dernier message: 27/10/2005, 11h33
  3. Réponses: 2
    Dernier message: 12/05/2005, 10h10
  4. [Builder] Effectuer une opération quand une resource a changé
    Par Satch dans le forum Eclipse Platform
    Réponses: 2
    Dernier message: 08/04/2004, 13h22
  5. Accélérer une requête
    Par bencot dans le forum SQL
    Réponses: 15
    Dernier message: 27/01/2004, 14h51

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo