delete multi tables

Version imprimable

delete multi tables

Bonjour tout le monde

Je désire effacer les lignes avec DELETE de différentes tables.
Voici mon code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select @numero :=26 delete etablissements,details, commandes ,corresp,agents,commerciaux,articles from etablissements,details,commandes, corresp,agents,commerciaux,articles where details.id_cmd=commandes.id_commande and commandes.id_societe=@numero and corresp.id_societe=@numero and agents.id_societe=@numero and commerciaux.id_societe=@numero and articles.id_societe=@numero and etablissements.id_societe=@numero
Cela fonctionne très bien si toutes les tables ont au moins une ligne correspondante, sinon cela ne fait rien.
Par exemple : si au moins une ligne dans TOUTES les tables correspond, alors la suppression se fait;
mais si par exemple dans la table commandes il n'y a pas de champ=@numero (la table details est liée à la table commandes), alors que les autres tables ont bien une correspondance, DELETE ne fonctionne pas.
Comme si la suppression se faisait sur le mode "tout ou rien".
Donc ma question est :
comment faire pour que la suppression soit ok dans toutes les tables, même celles qui n'ont pas de correspondance ?
(J'ai tenté EXISTS , sans résultat)

Merci !
Lepatantpato

Salut lepatantpato.

Citation:

Envoyé par lepatantpato

Comme si la suppression se faisait sur le mode "tout ou rien".

C'est le cas, puisque dans votre requête de suppression, vous mettez des "and" dans la clause "where".
Code:

1 2 3 4 5 6 and commandes.id_societe = @numero and corresp.id_societe = @numero and agents.id_societe = @numero and commerciaux.id_societe = @numero and articles.id_societe = @numero and etablissements.id_societe = @numero
Je constate que toutes vos tables ont la même colonnes "id_société".

Pourquoi ne pas utiliser une clef étrangère ?
Vous avez une table mère, dont la clef primaire est cette colonne "id_société".
Dans chacune de vos tables (commandes, corresp, agents, commerciaux, articles, etablissements), vous créez une clef étrangère de nom "id_societe_fk".
Et vous déclarez ceci dans chacune de vos tables :

Code:

CONSTRAINT `FK_societe` FOREIGN KEY (`id_societe_fk`) REFERENCES `societe` (`id_societe`) ON DELETE CASCADE ON UPDATE CASCADE

Vous avez remarqué que j'ai mis : "ON DELETE CASCADE ON UPDATE CASCADE".
Cela signifie que si dans la table mère "société", vous supprimez une seule ligne, par exemple la société "26", il va supprimer en cascade, toutes les lignes qui dépendent de cette société.
Autrement dit, il supprime toutes les lignes ayant comme clef étrangère, société = 26.

Cela simplifie grandement votre requête de suppression car elle va se résumer à :

Code:

delete from `societe` where id_societe = 26;

Avez-vous besoin d'un exemple ?

@+

06/08/2017, 12h02
lepatantpato

DELETE

Bonjour
Merci de votre aide. Je vais l'étudier.
Lepatantpato
08/08/2017, 10h54
escartefigue

Citation:

Envoyé par Artemus24

Pourquoi ne pas utiliser une clef étrangère ?
Vous avez une table mère, dont la clef primaire est cette colonne "id_société".
Dans chacune de vos tables (commandes, corresp, agents, commerciaux, articles, etablissements), vous créez une clef étrangère de nom "id_societe_fk".
Et vous déclarez ceci dans chacune de vos tables :

Code:

CONSTRAINT `FK_societe` FOREIGN KEY (`id_societe_fk`) REFERENCES `societe` (`id_societe`) ON DELETE CASCADE ON UPDATE CASCADE

Vous avez remarqué que j'ai mis : "ON DELETE CASCADE ON UPDATE CASCADE".
Cela signifie que si dans la table mère "société", vous supprimez une seule ligne, par exemple la société "26", il va supprimer en cascade, toutes les lignes qui dépendent de cette société.
Autrement dit, il supprime toutes les lignes ayant comme clef étrangère, société = 26.

Cela simplifie grandement votre requête de suppression car elle va se résumer à :

Code:

delete from `societe` where id_societe = 26;

ATTENTION : l'option ON DELETE CASCADE est très pratique en effet pour gérer simplement et avec 100% de succès l'intégrité de la BDD...
... mais à ne pas utiliser sur des arborescences importantes avec des tables volumineuses, car le temps de traitement peut être considérable et mettre carrément par terre la base de données !

Il est donc préférable pour les grosses tables avec une profondeur de FK importante d'utiliser ON DELETE SET NULL, puis, d'exécuter un traitement batch hors plage TP qui fait le ménage en supprimant physiquement les lignes dans les tables filles dont les FK sont marquées à NUL ;)
08/08/2017, 19h31
Artemus24

Salut Escartefigue.

Tout ça, je le sais. Je ne fais que répondre à une question posée, sans plus.

Citation:

Envoyé par Escartefigue

à ne pas utiliser sur des arborescences importantes avec des tables volumineuses, car le temps de traitement peut être considérable ...

Et donc, vous croyez que cela va considérablement diminuer le temps d'exécution ?
Non, car la volumétrie sera toujours la même.

Je préfère la simplicité d'un traitement, plutôt qu'une usine à gaz, qui au final, le temps d'exécution restera le même.

Citation:

Envoyé par Escartefigue

... mettre carrément par terre la base de données !

C'est pourquoi, ce traitement se fait en maintenance et non à la demande !
Car cela nécessite aussi une réorganisation des tables et des index dans la base de données.

@+
09/08/2017, 11h16
escartefigue

Citation:

Envoyé par Artemus24

Salut Escartefigue.

Tout ça, je le sais. Je ne fais que répondre à une question posée, sans plus.
Et donc, vous croyez que cela va considérablement diminuer le temps d'exécution ?
Non, car la volumétrie sera toujours la même.

NON, NON et NON !

Prenons le MCD très classique suivant :
[CLIENT] 1,n --- (commander )--- (1,1) [COMMANDE] 1,n --- (Contenir) --- (1,1) [LIGNE-CDE]
Le modèle physique résultant produira
- La table CLIENT avec un index pour la PK id client, et 2 index secondaires, pour recherche par nom et par secteur géographique
- La table COMMANDE avec un index primaire composé de id_client + id_commande, et par exemple deux index secondaires, un pour rechercher par date de commande, l'autre par id du commercial ayant enregistré la commande
- La table LIGNE-CDE ayant un index pour la PK id_client + id_commande + id ligne et un index secondaire sur l'identifiant article

Si vous codez un DELETE CASCADE, la suppression d'une occurrence de client va mettre à jour bien sur les tables CLIENT, COMMANDE et LIGNE-CDE mais aussi les 3 index primaires respectifs et les 5 index secondaires
Si vous codez un DELETE SET NUL, aucun des index secondaires des tables filles n'est impacté, or les index secondaires sont le plus souvent des index multiples, ils ont donc beaucoup plus d'entrée que ceux des PK, et nécessitent donc beaucoup plus d'I/O
Dans l'exemple très simplifié que j'ai donné, la différence ne porte que sur 3 index secondaires, mais si on enrichit le modèle avec plus de tables filles et/ou qu'on ajoute d'autres index secondaires sur chacune des tables, la différence en coût CPU et I/O est considérable.

Citation:

Envoyé par Artemus24

Je préfère la simplicité d'un traitement, plutôt qu'une usine à gaz, qui au final, le temps d'exécution restera le même.

Moi ausssi, sous réserve que la solution soit viable.
Mais comme expliqué plus haut, mais aussi et surtout vécu dans la vraie vie, pas dans la littérature dans de nombreux cas, DELETE CASCADE n'est pas viable. Certains clients interdisent purement et simplement le DELETE CASCADE pour cette raison !

Citation:

Envoyé par Artemus24

C'est pourquoi, ce traitement se fait en maintenance et non à la demande !
Car cela nécessite aussi une réorganisation des tables et des index dans la base de données.

Quel traitement ? on parle ici du choix entre CASCADE ou SET NUL, dans les deux cas, les actions sont réalisées en temps réel, certainement pas lors d'une maintenance !
Le besoin de réorganisation des tables et index n'est pas lié au choix entre DELETE CASCADE et DELETE SET NUL
09/08/2017, 21h15
Artemus24

Salut Escartefigue.

Citation:

Envoyé par Escartefigue

Si vous codez un DELETE SET NUL, aucun des index secondaires des tables filles n'est impacté, ...

Et donc par quel miracle votre colonne en clef étrangère passe d'une valeur renseignée au marqueur NULL ?
Votre raisonnement est faux car vous avez volontairement ignoré une étape.

Citation:

Envoyé par Escartefigue

Dans l'exemple très simplifié que j'ai donné, la différence ne porte que sur 3 index secondaires, mais si on enrichit le modèle avec plus de tables filles et/ou qu'on ajoute d'autres index secondaires sur chacune des tables, la différence en coût CPU et I/O est considérable.

Il n'y a aucune différence à la condition que vous tenez compte de toutes les étapes comme dit précédemment.

Entre autre la suppression dite logique qui consiste à marquer le NULL. Et c'est là que l'impact se fait sur les index !
Puis la suppression physique des lignes marquées à NULL. Dans cette étape, je suis d'accord avec vous, mais vous avez volontairement ignoré celle d'avant.

Citation:

Envoyé par Escartefigue

.. or les index secondaires sont le plus souvent des index multiples, ils ont donc beaucoup plus d'entrée que ceux des PK, et nécessitent donc beaucoup plus d'I/O

C'est plus rapide de désactiver les index, de faire la suppression en cascade, puis de réactiver les index.
Laisser en activité les index, fait qu'à chaque suppression d'une ligne, il y a intervention dans les index et cela coûte cher en I/O.

Citation:

Envoyé par Escartefigue

Certains clients interdisent purement et simplement le DELETE CASCADE pour cette raison !

Une fausse manipulation, et l'on détruit un gros volume de la base de données.
Pour récupérer cette mauvaise manipulation, Il faut alors arrêt le serveur, réinstaller le backup de la veille, redémarrer le serveur, refaire les saisies de la journée, ...
Et pendant ce temps là, les clients n'ont plus accès à la base de données.

La véritable raison est celle de l'indisponibilité de la base de données ainsi que de la perte de temps occasionné par ce retour à l'état normal.

Vous avez aussi oublié quelque chose.
Comment allez vous faire la distinction entre les clefs étrangères renseignées normalement et celle qui pour une raison quelconque sont à NULL ?
Avec votre méthode, vous allez tout détruire !
Je reconnais qu'une clef étrangère à NULL, c'est un problème d'intégrité, mais cela arrive parfois quand la saisie est partielle.

Dans ma méthode, je ne supprime que les lignes qui sont en relation avec la cascade, mais pas celle qui sont aussi à NULL.

Citation:

Envoyé par Escartefigue

Mais comme expliqué plus haut, mais aussi et surtout vécu dans la vraie vie, pas dans la littérature dans de nombreux cas, DELETE CASCADE n'est pas viable.

J'ai l'impression que vous me prenez pour un bouffon ! Et arrêtez de toujours généraliser.

Citation:

Envoyé par Escartefigue

Quel traitement ?

Je parlais d'un script qui va détuire dans chaque table, les lignes ayant le NULL, au lieu de faire un delete en cascade.

Citation:

Envoyé par Escartefigue

on parle ici du choix entre CASCADE ou SET NUL, dans les deux cas, les actions sont réalisées en temps réel, certainement pas lors d'une maintenance !

N'importe quoi ! Vous n'avez pas dû faire beaucoup de maintenance en exploitation dans votre vie.
On ne fait jamais ce genre de manipulation en temps réel !!! C'est de cette façon que l'on met une machine à genou.
Il faut mettre ce traitement avec une priorité faible afin de ne pas perdre la main.
Et faire en sorte de bloquer les autres traitements en attendant la fin de cette étape critique.

La suppression d'une grappe de donnée n'est pas quelque chose d'anodin et doit se faire dans de bonne condition.
C'est pourquoi, cela se programme à un moment de la journée (enfin plutôt la nuit), ou personne ne monopolise la base de données.
Comme cette suppression va créer des trous dans l'espace des tables, il faut faire une réorganisation.
Pourquoi ? Car l'espace disque est coûteuse en entreprise et il y a aussi des questions de performances sur une base désorganisée.

Citation:

Envoyé par Escartefigue

Le besoin de réorganisation des tables et index n'est pas lié au choix entre DELETE CASCADE et DELETE SET NUL

Quand j'était administrateur DB2 à l'exploitation, c'était tous les jours !
Il faut mieux faire un petit peu chaque jour, qu'une fois par mois, car le temps de cette réorganisation peut devenir très important.

Et donc si vous désirez faire ce genre de manipulation dans de bonne condition, il faut :
1) être le seul à intervenir sur la base de données. Par exemple programmer cette demande la nuit, lors des travaux d'exploitation.
2) et si vous avez peur que cela mette l'ordi à genou, attribuer une priorité faible sur le traitement afin de pouvoir garder la main.
3) et si c'est prioritaire, faire en sorte de bloquer les autres traitements de l'exploitation.
4) désactiver tous les index impactés par la suppression.
5) faire la suppression en cascade.
6) réactiver les index.
7) réorganiser les tables afin de gagner de l'espace et par voie de conséquence de la performance.
8) remettre la base de données en disponibilité.

C'est plutôt vous qui avez une connaissance livresque ! Seul l'expérience démontre ce qui est faisable ou pas.
Et on ne peut pas toujours généraliser comme vous le faite mais étudier chaque cas afin de trouver la meilleure solution.

@+
09/08/2017, 22h41
escartefigue

Citation:

Envoyé par Artemus24

Salut Escartefigue.
Et donc par quel miracle votre colonne en clef étrangère passe d'une valeur renseignée au marqueur NULL ?
Votre raisonnement est faux car vous avez volontairement ignoré une étape.

Tout simplement parceque les index secondaires n'ont rien à faire de l'id qui est supprimé, si je reprend l'un de mes exemples, l'index secondaire sur l'id article de la table ligne de commandes n'est nullement impacté par la mise à null de la colonne constitutive de l'index primaire :roll:

En d'autres termes, en quoi un index secondaire qui a pour seule colonne l'id article serait intéressé par l'id commande ...
10/08/2017, 00h45
Artemus24

Salut Escartefigue.

Citation:

Envoyé par Escartefigue

Tout simplement parce que les index secondaires n'ont rien à faire de l'id qui est supprimé ...

Bien sûr que oui, mais d'une manière indirecte.

Citation:

Envoyé par Escartefigue

... si je reprend l'un de mes exemples, l'index secondaire sur l'id article de la table ligne de commandes n'est nullement impacté par la mise à NULL de la colonne constitutive de l'index primaire

Dans ce cas de figure oui, car vous ne voyez que la suppression logique. Mais que faites-vous de la suppression physique ?

Vous vous focalisez que sur la valeur de la clef étrangère par le NULL. Donc sur ce point là, je vous rejoins.

Citation:

Envoyé par Escartefigue

En d'autres termes, en quoi un index secondaire qui a pour seule colonne l'id article serait intéressé par l'id commande ...

Mais je ne raisonne pas ainsi, car je regarde globalement ce qui se passe. Si vous ne tenez pas compte de la suppression physique, vous ne voyez que la moitié du travail.

Je rappelle que le but est de supprimer des lignes en relation par une clef étrangère dans une base de données.
Que vous le fassiez comme moi, par un "on delete cascade" en une phase (suppression logique et physique en même temps), ou que vous le fassiez en deux phases, d'abord le "on delete set null", puis plus tard la suppression physique, vous aurez exactement le même résultat.

Encore que vous ne faites pas exactement le même travail.
Dans le cas en deux phases, vous êtes obligé de faire une modification sur toutes les tables filles, puisque vous remplacez la valeur de la clef étrangère par le marqueur NULL.
Travail supplémentaire qui n'existe pas dans le cas en une phase, car cette modification ne sert à rien.

Globalement, votre approche est plus coûteuse en terme de performance.
Et comme je l'ai dit précédemment, je n'aime pas raisonner d'une manière générale car cela dépend de beaucoup de choses !

@+
10/08/2017, 15h52
escartefigue

Citation:

Envoyé par Artemus24

Globalement, votre approche est plus coûteuse en terme de performance

Encore une fois, c'est faux quand la profondeur de la cascade est importante et/ou qu'il y a beaucoup d'index et qu'il y a de nombreux DELETE dans la même transaction
Encore une fois, certains clients ne veulent pas en entendre parler sur les tables métier à forte volumétrie pour ces raisons, ces clients ne sont pas tous des imbéciles :roll:

Voici encore un exemple simple

Soit le modèle
[CLIENT 0,n] --- (passer) 1,1 [COMMANDE] 1,n --- (inclure) --- (1,1)[LIGNE_CDE] 1,1 --- (concerner) --- 0,n[ARTICLE]

Le DDL des tables est le suivant
T_CLIENT_CL(CL_id PK, CL_nom
, CONSTRAINT T_CLIENT_CL_PK PRIMARY KEY (CL_id))

T_COMMANDE_CO(CO_id, CO_num, CO_date, CL_id)
, CONSTRAINT T_COMMANDE_CO_PK PRIMARY KEY (CO_id)
, CONSTRAINT T_T_COMMANDE_CO_FK1 FOREIGN KEY (CL_id) references T_CLIENT_CL(CL_id ON DELETE option)

T_LIGNE_CDE_LG(CO_id, LG_id, LG_qte, AR_id
, CONSTRAINT T_LIGNE_CDE_PK PRIMARY KEY (CO_id, LG_id)
, CONSTRAINT T_LIGNE_CDE_FK1 FOREIGN KEY (CO_id) references T_COMMANDE_CO(CO_id) ON DELETE CASCADE
, CONSTRAINT T_LIGNE_CDE_FK2 FOREIGN KEY (AR_id) references T_ARTICLE_AR(AR_id))

Que se passe -t- il en cas de DELETE sur la table CLIENT

Dans tous les cas (quelque soit la clause ON DELETE de la table COMMANDE)
- suppression des lignes concernées dans la table CLIENT et dans tous ses index

Si la clause ON DELETE est paramétrée à CASCADE dans la table COMMANDE
- suppression de toutes les commandes du client et des lignes correspondantes dans tous les index de la table COMMANDE
- suppression de toutes les lignes de commande dont les commandes ont été supprimées, et supression de ces lignes dans tous les index correspondants
Soit DELETE sur 3 tables, et 6 index ==> 9 objets impactés par le DELETE de plus les index des lignes de commandes sont ceux qui comportent le plus de row !

Si la clause ON DELETE est paramétrée à SET NULL dans la table COMMANDE
- toutes les commandes des clients supprimés voient leur colonne CL_id positionnées à "NULL", elles ne sont pas supprimées
l'index primaire n'est pas affecté par la suppression puisqu'il ne contient pas la colonne CL_id, seul l'index multiple sur CL_id est impacté
- la table T_LIGNE_CDE_LG n'est aucunement impactée par l'opération, pas plus que ses index
Soit DELETE sur la table CLIENT seulement, et UPDATE sur la table COMMANDE et son index FK ==> 3 objets impactés seulement

Ici l'exemple est restreint à peu de tables et d'index, plus on augmente la pronfondeur de la cascade et le nombre d'index, plus le DELETE CASCADE est couteux

De plus : avec l'option SET NULL, on reporte le DELETE effectif dans un traitement batch, que l'on planifie sur une plage horaire où les concurrences d'accès sont moins nombreuses (la nuit, le WE...) alors qu'avec l'option cascade, le DELETE est immédiat, on n'a pas le choix !

Et, n'oubliez pas qu'une base de données et très rarement mono-utilisateur, toutes les MàJ en masse sont susceptibles de générer des contentions avec les autres threads, les limiter, c'est limiter les temps d'attente, voire pire, les deadlocks.

Donc je me répète pour la nième fois, DELETE CASCADE c'est très bien dans certains cas, à proscrire dans d'autres

CQFD
10/08/2017, 17h30
escartefigue

@Artemus 24 :
Voici un sujet qui devrait (enfin) vous convaincre ;) https://blog.developpez.com/sqlpro/p...mplexes_dans_l

EDIT : et en voici un autre, bonne lecture :) https://www.developpez.net/forums/d1...elete-cascade/
11/08/2017, 20h35
Artemus24

Salut Escartefigue.

Ce n'est pas la peine de me redonner un second exemple car j'ai bien compris vos explications précédentes.
Par contre, vous ne comprenez pas la problématique que je soulève !

Citation:

Envoyé par Escartefigue

De plus : avec l'option SET NULL, on reporte le DELETE effectif dans un traitement batch, ...

C'est ce que je m'efforce à vous faire comprendre depuis le début de nos échanges.
L'utilisateur désire faire une suppression physique et pas uniquement logique.
Et que cette suppression doit se faire dans de bonne condition, à savoir :
--> gérer le problème d'intégrité de la base de données
--> gérer les points de reprise en cas de plantage du traitement de suppression, sino il y aura un problème d'intégrité des données.
--> être le seul traitement à s'exécuter sur la base de données, ce qui implique d'être lancé durant la nuit lors d'une maintenance par le service d'exploitation.

La façon de faire la suppression est complètement secondaire si l'on respecte les conditions de passage de ce batch.

Je le répète, c'est un problème de maintenance dédié au service d'exploitation et non une façon de travailler des utilisateurs.
On ne devrait jamais autoriser un utilisateur à supprimer physiquement des lignes, pour des raisons de sécurités.

Citation:

Envoyé par Escartefigue

... que l'on planifie sur une plage horaire où les concurrences d'accès sont moins nombreuses (la nuit, le WE...) ...

C'est ce que j'ai dit avant vous. Cela se fait dans le cadre de la maintenance.
Et en quoi le "on delete cascade" pose problème si cela est fait durant la nuit, lorsque l'accès est limité à un seul utilisateur ?

Citation:

Envoyé par Escartefigue

... alors qu'avec l'option cascade, le DELETE est immédiat, on n'a pas le choix !

Mais avec la méthode "ON DELETE SET NULL" aussi, on n'a pas le choix.

Imaginons que l'utilisateur fasse plusieurs suppressions "on delete set null" sur ça table mère.
Comment voulez-vous réaffecter les clef étrangères à leur valeur d'origine ???
Réponse : impossible sans réinstaller la base de données de la veille.

Votre approche est valable que sur une seule suppression et dans cet exemple, je n'ai pas abordé la question de l'intégrité des données.

Encore que, j'ai dit précédemment que le marqueur NULL pour la clef étrangère ne veut pas nécessairement signifier ligne à supprimer.
Il est fort probable que la saisie se fasse en deux temps, et que cette clef étrangère soit affectée plus tard.
Prenons l'exemple d'une prise de commande qui sera traité par un employé.
Il n'est pas nécessaire d'affecter tout de suite cette prise de commande par tel employé.

La bonne pratique que vous avez surement rencontré en entreprise, est de programmer une demande de suppression qui sera effectué durant la nuit dans un batch dédié à cela.
L'utilisateur ne doit jamais faire de suppression logique ou physique, pas uniquement pour des raisons de performances, mais surtout pour des raisons de sécurités.

Citation:

Envoyé par Escartefigue

Et, n'oubliez pas qu'une base de données et très rarement mono-utilisateur, toutes les MàJ en masse sont susceptibles de générer des contentions avec les autres threads, les limiter, c'est limiter les temps d'attente, voire pire, les deadlocks.

Oui et alors ? Je n'ai jamais dit le contraire.

Citation:

Envoyé par Escartefigue

Donc je me répète pour la nième fois, DELETE CASCADE c'est très bien dans certains cas, à proscrire dans d'autres

Il n'est pas nécessaire de mettre "certains cas" au pluriel. Il s'agit d'une suppression de masse et donc cela se fait à la demande.

Et cette demande va se traduire par la programmation d'un batch durant la nuit quand le traitement sera le seul à s'exécuter.
Et ce travail se fait durant la maintenance, juste avant le passage à l'exploitation des travaux de nuit.

Je ne conseille pas du tout de faire des suppression de masse durant la journée pour les raisons que vous avez soulevé.
Donc pour moi, "on delete cascade" ou "on delete set null", c'est du pareil au même.
Car ce genre de traitement ce fait à la demande, durant la nuit et est définitif.
Et même si cela prend 30 minutes, on n'est pas à cela prêt durant un batch de nuit.

Par vos remarques, j'ai l'impression que vous négligez grandement la maintenance et l'exploitation.
Je l'ai déjà dit dans d'autres sujets que la façon de travailler dans le monde de la micro-informatique me choque.
Comme je viens du monde du gros système, on ne peut pas se permettre de faire du bricolage.
Il y a une rigueur qui est souvent lourde à mettre en place et qui permet à juste titre d'éviter des catastrophes.
En micro-informatique, on ne voit rien de tel. Par exemple, le versionnage des programmes, ou encore les sauvegardes quotidiennes.

@+
11/08/2017, 20h37
Artemus24

Re-Salut Escartefigue.

Voici mes remarques pour le premier lien.

Citation:

Envoyé par SQLPRO

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….

On évite ce genre de problème en interdisant les suppressions durant la journée.
C'est pourquoi, on programme la demande de suppression afin qu'elle soit traité durant la nuit quant il n'y a plus d'utilisateurs pour venir, comme dit SQLPRO, pourrir la base de données.

Citation:

Envoyé par SQLPRO

en principe les applications ne doivent jamais attaquer les bases avec des requêtes sur tables, mais uniquement sur des vues ...

Entièrement d'accord !!! C'est pourquoi les utilisateurs en micro-informatique ne savent même pas que les view existes.

Citation:

Envoyé par SQLPRO

... et de batch.

Ca, c'est la bonne façon de travailler !!!
Autrement dit, déléguer ce genre de travail à la maintenance qui va se faire dans la phase d'exploitation qui se passe durant la nuit.

Citation:

Envoyé par SQLPRO

toute entité est représenté par une vue dans laquelle sont effectuées des suppressions logiques.

Encore que, il n'est rien dit sur la façon de procéder une suppressions physiques.
J'ai déjà vu une colonne être délégué à cet usage. Genre 1 pour ligne active, et 0 ligne à supprimer.
C'est selon moi une bonne méthode car elle ne modifie en aucune façon les clefs étrangères.

Citation:

Envoyé par SQLPRO

aux heures creuses, les suppressions logiques sont transformées en suppression physiques

A midi, à l'heure du repas, on verrouille le serveur SGBDR ou plus fréquemment la nuit dans un batch.

Citation:

Envoyé par SQLPRO

Le batch de nuit fera le nettoyage physiques des lignes et reconstruira les index.

C'est ce que je préconise aussi.
Mais je ne voie pas trop l'intérêt de passer pour cela par un "on delete set null".
Un "on delete cascade" fonctionne tout aussi bien.

En fait, il faut se poser la question dès la conception de la base de données comment vont se faire les suppressions.
Et c'est à ce moment que l'on va devoir faire les bons choix, qui dépendent de paramètres que l'on ne peut pas généraliser.

Voici mes autres remarques pour le second lien.

Citation:

Envoyé par Tanebisse

On dit toujours qu'il faut gérer les règles métier sur la couche métier.

Tanebisse à bien compris que ce problème est un problème de métier.
Il ne faut pas confier ce genre de traitement aux études ou à un ou plusieurs utilisateurs, mais à le déléguer à l'exploitation.

Citation:

Envoyé par SQLPRO

Mais il existe d'autres techniques comme SET DEFAULT ou SET NULL qui permettent d'arriver au même résultat via des traitements déportés en batch à condition d'utiliser des vues.

Bonne remarque ! Encore qu'il aurait fallu préciser que cela se fasse par le métier de l'exploitation.
Le problème ne se trouve pas dans la technique utilisé ("on delete cascade", "on delete set null" ou encore des "delete from"), mais dans la façon de la mettre en oeuvre.
Il faut éviter d'avoir des problèmes d'intégrités dans la base de données. Pour cela, il faut être le seul traitement à avoir accès au serveur.

J'ai même mis un '+'1 sur le message #5 et #6 car j'ai trouvé la réponse très pertinente.

Citation:

Envoyé par SQPRO

La nuit, aux heures creuses, on lance les bach de traitement de purge effective qui vont supprimer les "fausses" lignes par lot de 500 par exemple.

C'est ce que j'ai préconisé dans mes réponses précédentes de ce sujet.

Citation:

Envoyé par StringBuilder

Sinon, en plus simple, vous pouvez apposer un flag sur vos tables, que vous pourrez propager par trigger au besoin : ces lignes seront écartées des traitements applicatifs, et vous aurez tout le loisir de les supprimer par lot, sans risque d'accès concurrent, à un moment plus calme.

Oui, je suis d'accord, mais c'est dommage car on exploite pas les fonctionnalités de suppression ("on delete cascade") que permet le SGBDR.
Encore que, cela dépend sur quelle table cela se fait. Si c'est juste sur la table mère, je suis d'accord.
Si c'est sur les tables filles, non, car il faudra balayer toutes les lignes, donc pas TOP comme approche.

Ne pas oublier si l'on veut faire cela correctement, de programmer une gestion des points de reprise, fréquemment oublié en micro-informatique.
Dans le cadre de la transaction, c'est ce que je nomme une grappe de donnée, qui doit nécessairement se terminer par un "commit" afin de conserver l'intégrité.

Ah oui, Escartefigue, arrêtez de me prendre pour un imbécile !

@+
14/08/2017, 09h10
escartefigue

Citation:

Envoyé par Artemus24

Ca, c'est la bonne façon de travailler !!!

Formidable, votre remarque se rapporte au traitement par batch également préconisé par SQL Pro, dont voici la citation complète puisque vous avez tronquée :

Citation:

Envoyé par SQLpro

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.

Soit exactement ce que je préconise depuis ma réponse du 08-08-2017 à 10h54 et que re-voici :

Citation:

Envoyé par escartefigue

ATTENTION : l'option ON DELETE CASCADE est très pratique en effet pour gérer simplement et avec 100% de succès l'intégrité de la BDD...
... mais à ne pas utiliser sur des arborescences importantes avec des tables volumineuses, car le temps de traitement peut être considérable et mettre carrément par terre la base de données !

Il est donc préférable pour les grosses tables avec une profondeur de FK importante d'utiliser ON DELETE SET NULL, puis, d'exécuter un traitement batch hors plage TP qui fait le ménage en supprimant physiquement les lignes dans les tables filles dont les FK sont marquées à NUL ;)

Citation:

Envoyé par Artemus24

Ah oui, Escartefigue, arrêtez de me prendre pour un imbécile !

Non, mais soit pour quelqu'un qui ne sait pas lire, soit pour quelqu'un de très mauvaise foi, il me semble que nous aurions pu nous économiser tous vos verbiages incessants.
14/08/2017, 11h23
Artemus24

Salut Escartefigue.

Citation:

Envoyé par Escartefigue

Soit exactement ce que je préconise depuis ma réponse du 08-08-2017 à 10h54 et que re-voici :

Avez-vous un problème de vocabulaire ? J'ai l'impression.
Nous n'allons pas refaire toute la discussion, mais je tiens à vous dire que vous ne cherchez pas à me comprendre !

Citation:

Envoyé par Escartefigue

... mettre carrément par terre la base de données !

C'est ce point de vue qui est faux, que vous n'avez pas compris !

J'ai rétorqué :

Citation:

Envoyé par Artemus24

C'est pourquoi, ce traitement se fait en maintenance et non à la demande !

Qu'est-ce que vous ne comprenez pas dans le mot "maintenance" ?
Une maintenance ne se fait pas dans la journée, quand les utilisateurs monopolisent la base de données.
Mais la nuit durant le début de la phase d'exploitation, juste avant le début du lancement des travaux (batch) de nuit.

Et vous me dites :

Citation:

Envoyé par Escartefigue

Quel traitement ?

Ce qui confirme bien que vous n'avez rien compris !

J'ajoute :

Citation:

Envoyé par Artemus24

Car cela nécessite aussi une réorganisation des tables et des index dans la base de données.

Et là, rien, vous passez sous silence la totale désorganisation des bases de données, suite à une suppression de masse.
Ce qui me confirme que vous ignorez tout de l'exploitation et de ces contraintes.

Oui, j'ai bien lu ceci :

Citation:

Envoyé par Escartefigue

puis, d'exécuter un traitement batch hors plage TP

Sauf qu'un traitement batch ne sous-entend pas que cela se fasse nécessairement durant la nuit.
L'important est d'être le seul à monopoliser la base de données durant ce traitement de suppression.
Le moment où cela se fait n'a pas grand importance, et peut très bien se faire durant les heures creuses, comme à midi, à l'heure du repas.

Ah oui, TP, chez moi signifie "travaux pratiques". Quel est le rapport avec les traitements d'exploitation ?

Et quand je dis être le seul à monopoliser la base de données, cela signifie aussi que des travaux d'exploitations peuvent se faire aussi en parallèle.
Donc c'est bien avant le début des travaux d'exploitation, durant la phase de démarrage qui est la phase de maintenance quand on prépare l'environnement d'exploitation.

[quote="Escartefigue"]dans les deux cas, les actions sont réalisées en temps réel, ...
Et là, la première âneries !
Temps réel signifie que vous monopolisez l'ordinateur. C'est la priorité la plus haute du passage d'un traitement !!!
Alors que juste avant, vous me dites que cela va "mettre carrément par terre la base de données !".

Citation:

Envoyé par Escartefigue

... certainement pas lors d'une maintenance !

Et la deuxième ânerie !
Ah bon, alors quand devons-nous faire cette suppression de masse ?
Je le répète, cela se fait avant les travaux d'exploitations de nuit, dans la phase de maintenance, quand on prépare l'environnement d'exploitation !
Je suppose que vous l'ignoriez.

Et quand vous êtes à court d'arguments, vous me dites :

Citation:

Envoyé par Escartefigue

Encore une fois, certains clients ne veulent pas en entendre parler sur les tables métier à forte volumétrie pour ces raisons, ces clients ne sont pas tous des imbéciles

Quelles raisons ? Vous ne les avez pas dites.
Et ce n'est pas la première fois que je vois des clients se copier les uns les autres en affirmant que c'est la bonne méthode, mais ne comprennent pas, par la suite, pourquoi ils ont des erreurs, genre problème d'intégrité des données dans leur base.
Donc non, ce n'est pas un argument en votre faveur.

Je vais reformuler ce que vous ne comprenez pas.
Vous vous êtes focalisés sur le "on delete set null" comme étant la seule méthode valable de la suppression de masse.
Je ne dis pas que cela est faux, mais ce n'est pas cela qui est important dans la suppression de masse.
N'importe quelle méthode de suppression, comme le "on delete cascade" peut être utilisé.

L'important dans la suppression de masse est d'être le seul à monopoliser la base de données à ce moment là. Pourquoi ?
Car sans cela vous risquez d'avoir des problèmes d'intégrités lors de vos suppressions si un utilisateur venait à faire une mise à jour sur la base.
Mais surtout, il faut réorganiser la base afin de la rendre performante car une suppression de masse crée un table espace trop grand et donc gaspillage d'occupation de disque.

Le temps d'exécution de cet suppression de masse est secondaire car cela n'impacte pas les travaux d'exploitation.
Bon, d'accord, il ne faut pas que cela dure cinq heures, sinon les travaux d'exploitations risques de se terminer à midi et donc d'empêcher le démarrage de la journée d'autant.

J'arrête là car les prises de têtes avec vous n'en finissent pas !

@+
14/08/2017, 12h55
escartefigue

C'est l'hôpital qui se fout de la charité !
Entre les réponses à rallonge et vides de contenu, et les tentatives répétées de s'approprier l'argumentaire des autres en feignant d'en être l'auteur, vous ne manquez pas d'air !

En tout cas

Citation:

Envoyé par Artemus24

J'arrête là car les prises de têtes avec vous n'en finissent pas

C'est une excellente nouvelle et pourvu que cela dure !
22/08/2017, 14h33
lepatantpato

Merci

Bonjour et merci de vos contributions à vous deux, Escartefigue et Artemus 24.