Optimiser un delete

Bonjour à tous,

Je suis très limité en SQL, j'ai un peu cherché la réponse à ma question sur le net mais je l'ai pas trouvée, si tant est qu'elle existe...

Pour commencer, j'utilise Sybase, je ne pense pas que ma question soit spécifique à ce SGBD, donc je la pose ici, par contre, pour éviter que Sybase s'emmêle avec ses logs, sur les conseils de gens plus au point en SQL, avant de lancer mes suppressions je fais ça :

Code:

---

set rowcount 5000

---

5000 étant une valeur arbitraire dont on m'a dit qu'elle marchait bien...

Pour en venir à la commande elle-même, j'ai un delete assez simple à faire (en fait j'ai du mal à imaginer plus simple) puisqu'il se base sur un test ne concernant qu'un seul champ (et en plus ce champ est de type INT).
Disons que la ligne suivante fait exactement ce que je veux :

Code:

---

delete table where champàtester = valeur

---

Le problème c'est qu'elle met énormément de temps.

En fait je fais plusieurs delete dans des tables différentes la plus grosse fait un peu moins de 250 000 000 lignes mais celle qui me pose le plus de problème en fait environ 10 fois moins, dans le cas de cette table particulière le champàtester est une des trois clefs d'un index, on m'a conseillé une petite feinte : faire apparaitre les deux autres champs dans ma requête (en testant qu'ils étaient égaux à eux même pour ne pas modifier le résultat final) :

Code:

---

delete table where champàtester = valeur and autrechampdelindex1 = autrechampdelindex1 and autrechampdelindex2 = autrechampdelindex2

---

mais c'est à la fois moche (ça à la limite je peux faire avec) et visiblement pas efficace (même si j'ai fait mes tests empiriquement et sans les compétences nécessaires pour vraiment maîtriser les aléas comme la gestion des caches par Sybase et les autres personnes pouvant accéder à la base en même temps). Au passage l'index (et oui, j'ai encore le fol espoir qu'il puisse me servir à quelque chose) est de type "clustered, unique".

Pour info une suppression "moyenne" concerne environ 10% des données, c'est vraiment à la louche mais c'est pour dire que, a priori, la solution de contournement consistant à sélectionner les lignes à ne pas effacer pour les mettre dans une table temporaire, puis à effacer la table courante et à renommer la table temporaire ne devrait pas être très efficace.

ps : je peux peut être demander la création d'un index (ou autre, je veux dire une modification de la base autre que les données) mais je ne suis pas sûr que ça aboutisse et il me faudrait de solides arguments, dans le même ordre d'idée je peux faire quelques tests mais je peux pas occuper la base toute la journée (et à chaque fois c'est très long).

pps : sans être ultra sensibles mes données sont confidentielles, c'est pour ça que je n'ai pas trop donné de détails même si je pense qu'on voit assez bien le problème, si toutefois vous avez des questions spécifiques...

Voilà, désolé pour la longueur de ce post (qui aurait sans doute tenu en quelques lignes si j'avais su quelles informations étaient vraiment pertinentes), en tout cas si quelqu'un a une idée ça me serait bien utile...

Bonjour

En créant un index uniquement sur le champ utilisé dans la clause where ça devrait améliorer les performances je pense. En tout cas ça me semble logique.

Cordialement

Bonjour,
as tu regardé le plan d'execution du delete ?

d'après tes dires, tu as tout de même 250 000 000 de lignes dans ta table. Ce n'est pas négligeable.

Il m'est arrivé quelque fois d'avoir à delete plusieurs millions de lignes dans une table et ce, en prod. J'étais passé sur un système un peu comme toi de boucle qui me permettait de supprimer les lignes par petit morceau.

C'est un peu lent au départ, (logique plusieurs centaines de millions de lignes) mais avec les caches systèmes et les statistiques du SGBD ça fini par accélérer.

Tu peux, au pire, créer un index temporaire le temps que ton processus s'exécute. Pour ma procédure j'étais partis sur une boucle avec un EXISTS.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SET ROWCOUNT 1 GO   WHILE EXISTS ( SELECT 1 FROM maTable WHERE monChamp = 'x' ) BEGIN   SET ROWCOUNT 100000 --Arbitraire GO   DELETE FROM maTable WHERE monChamp = 'x' GO   SET ROWCOUNT 1 GO   END

---

Le soucis reste la quantité de données de table.

bonjour,


au niveau des criteres de la suppresion, c'est quoi ? une date uniquement ou autre chose ?

Lyche, l'idée est bonne mais la partie EXISTS est inutile et ralenti tout le processus.
Pour une suppression par lot, tout simplement :

Code:

---

1 2 3 4

WHILE (@@ROWCOUNT>0) DELETE TOP 10000 FROM MaTable WHERE monChamp = 'x'

---

Citation:

---

Envoyé par Waldar

Lyche, l'idée est bonne mais la partie EXISTS est inutile et ralenti tout le processus.
Pour une suppression par lot, tout simplement :

Code:

---

1 2 3 4

WHILE (@@ROWCOUNT>0) DELETE TOP 10000 FROM MaTable WHERE monChamp = 'x'

---

---

1 - je n'utilise jamais le TOP ;)
2 - je n'avais absolument pas pensé au ROWCOUNT, je pense que ça pourrait faire gagner pas mal de perf.

Merci :)

Bonjour,
Avez vous pensé que le problème pouvait être ailleurs.
Est-ce que cette table est référencé dans d'autres tables par une foreign key.
Et est-ce que dans cette autre table la foreign key est indexé.
Imaginons que la table qui pose problème s'appelle Pere et a comme clé pere_id
et que Pere est référencé par une table Fille qui a donc une colonne pere_id non indexée.

Quand Sybase va essayer de supprimer dans Pere, il va vérifier s'il y a quelque chose dans fille dont pere_id = id_a_supprimer et c'est cela qui pourrait prendre du temps.

Surtout si Fille est grande ou s'il y a plusieurs Filles.

Cordialement
Soazig

Bonjour,
Concernant les requêtes proposées par Waldar et Lyche, je ne pense pas que ça change grand chose, il a indiqué en début de post qu'il faisait un

Code:

---

SET rowcount 5000

---

ce qui limite de fait le nombre de lignes supprimées.

Cordialement
Soazig