Discussion :

[sevyc64]

Bonjour à tous.

Je dois préparer un script de purge partielle d'une base de données. La base comporte près de 300 tables, certaines des tables peuvent dépasser les 10 millions d'enregistrements. Le but est de supprimer toute une partie de l'activité dans la base, typiquement, ça pourrait être, par exemple, toute l'activité d'un département dans une entreprise car le département en question est dissout ou vendu, etc...
L'ensemble des 300 tables sera donc impacté, à priori !

J'ai certaines tables qui sont référencées par plus d'une centaine de clés étrangères, dont les données peuvent être référencées par des enregistrements qui vont être supprimés (donc théoriquement à supprimer), mais qui peuvent être, en même temps, référencées par des enregistrements qui ne seront pas supprimés.
C'est typiquement le cas d'une table annuaire qui regrouperait les infos de toutes les personnes physiques ou morales utilisées ailleurs dans la base.


Est-ce qu'il y a moyen de construire une requête DELETE qui, sur le principe, supprimerait les enregistrements qu'elle peut supprimer, non référencés donc, et qui, pour les enregistrements qui ne peuvent pas être supprimés
- ne génère pas d'erreur ou d'exception
- n'arrête pas ou ne fait pas planter le script dans lequel elle sera
- ignore l'enregistrement non supprimable
- passe à l'enregistrement suivant pour tenter de le supprimer

L'analyse n'est pas terminée, mais un certain nombre de tables sera concerné par ce type de requête
Ces requêtes seront dans un script plus complet qui fera la purge partielle de la totalité des tables
La totalité des purges sera, normalement sous couvert d'une transaction

[tatayo]

Bonjour,
Au départ j'aurai parlé d'une simple requête avec des "WHERE NOT EXISTS", mais avec des centaines de clé étrangères c'est pas gagné.
Tu peux peut-être la générer via du code en "tapant" dans les tables sys.tables, sys.foreign_keys et sys.foreign_key_columns.

Autre solution (mais j'ai un petit doute): créer une vue qui ne renvoie que ce qui peut être supprimé, et lancer la suppression sur cette vue.

Mais je me demande ce que va en penser Sql Serveur, et ce que ça va donner niveau perf.

Tatayo.

[sevyc64]

Oui, ça risque de pas être si simple que ça.

En continuant mon analyse, je me rends compte que je ne vais sans doute pas pouvoir m'appuyer uniquement sur une approche pure SQL, mais que je vais devoir aussi y intégrer une approche métier.

J'ai déjà trouvé le cas d'une table qui s'auto-référence directement, j'en ai une autre qui s'auto-référence par l'intermédiaire d'une table association.

Je vais sans doute aussi avoir des cas ou un enregistrement père ayant des fils ne serait théoriquement pas supprimable, sauf que, en ne tenant pas compte de certains fils, il le deviendrait et ces fils-là serait du coup aussi supprimable.
Là, on est clairement sur du métier, plus sur du sql pur. Et du coup je pense que je vais pouvoir abandonner tout espoir d'une automatisation de la génération des scripts de suppression.


Mais l'idée du DELETE sans erreur va rester valable malgré tout pour certaines tables.

[escartefigue]

Une centaine de clefs étrangères dans une même table est symptomatique d'un très gros problème de modélisation

Cela étant dit, si les colonnes FK font l'objet d'une clause ON DELETE CASCADE, le ménage sera fait automatiquement par le SGBD, mais ça peut être long si la profondeur de la cascade est importante

[tatayo]

Une centaine de clefs étrangères dans une même table est symptomatique d'un très gros problème de modélisation

J'avais traduit sa phrase d'une autre façon: une centaines de clé étrangères qui "pointent" vers la même table.

Mais dans les deux cas je me demande bien comment on peut en arriver là.

Tatayo.

[JeitEmgie]

Mais dans les deux cas je me demande bien comment on peut en arriver là.

Il suffit d'un modèle implémentant une taxonomie avec une table par niveau hiérarchique et d'avoir plus de 100 "business objects/classes" héritant de la racine...
Genre de modèle assez courant avec des outils comme Alfresco, Documentum, et d'autres... et dans ce genre d'utilisation, 100 classes c'est même assez moyen comme quantité.

[sevyc64]

Une centaine de clés qui pointent vers la même table.

Et oui, on y arrive très vite.
Si on considère, comme dans l'exemple que je donnais, une table Tiers, ou Annuaire, ou Personne, on l'appelle comme on veut, qui contient les informations de toutes les personnes physiques ou morales utilisées dans la base, on a donc toutes les personnes, parents, enfants, conjoints, notaires, fournisseurs, centre des impôts, magasins divers, etc, etc,
C'est évidemment le cas le plus extrême, mais oui ça va très très vite.

Et le ON DELETE CASCADE n'est pas la solution à tout. Et est même, dans beaucoup de cas, une solution très dangereuse.
De toute façon, je n'en ai pas dans la base, donc ce problème-là est réglé