Discussion :

[mister3957]

Bonjour à tous,

J'ai un souci avec une requête qui demande beaucoup de ressources à s'exécuter.

Voici un exemple de ce que j'essaye de faire.

Admettons on a une table
- t_clients (id)
- t_commandes (id, client_id, date_validation)

Et on veut supprimer les commandes dont la date de validation est avant le 1er janvier 2015, et supprimer les clients qui n'ont pas de commande après.

Code :

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

1
2
3
 
DELETE FROM t_commandes WHERE t_commandes.date_validation < '2015-01-01';
DELETE FROM t_clients WHERE NOT EXISTS (SELECT * FROM t_commandes WHERE t_commandes .client_id = t_clients.id);

La seconde requête exploite à fond le fichier de log, jusqu'à ce que l'OS manifeste qu'il n'y a bientôt plus d'espace disque et ensuite c'est SQL Serveur qui lance une erreur "impossible d'agrandir le fichier de log".

Est-ce qu'il n'y a pas moyen de contourner ce "WHERE NOT EXISTS" ? On peut utiliser des procédures, des tables temporaires etc.

Merci beaucoup par avance,

A bientôt

[aieeeuuuuu]

Bonjour,

Si c'est le fichier de log qui sature, alors remplacer le NOT EXISTS ne changera pas grand chose.

Quelle est la taille du fichier de log ? la taille disponible sur le disque ? quel est le mode de récupération de votre base ?
Quel est le nombre de clients qui devraient être supprimés par votre requete ?

[mister3957]

Bonjour,

Si c'est le fichier de log qui sature, alors remplacer le NOT EXISTS ne changera pas grand chose.

Quelle est la taille du fichier de log ? la taille disponible sur le disque ? quel est le mode de récupération de votre base ?
Quel est le nombre de clients qui devraient être supprimés par votre requete ?

J'ai fait un "shrink" du fichier de log pour le ramener à 1Mo, et la taille disponible sur le disque est de 11Go (c'est une machine virtuelle).

Il y a à peu d'un milliard de commandes, dont 90% à supprimer, et un peu moins en client mais avec le même rapport, 90%.

(Les clients et les commandes étaient un exemple pour poser deux tables qui se joignent)

[Invité]

Fais ça par petit paquet genre :

Code :

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

1
2
3
4
5
6
7
select 1
while @@rowcount > 0
        DELETE TOP(50000) FROM t_commandes WHERE t_commandes.date_validation < '2015-01-01';
 
select 1
while @@rowcount > 0
        DELETE TOP(50000) FROM t_clients WHERE NOT EXISTS (SELECT * FROM t_commandes WHERE t_commandes .client_id = t_clients.id);

[escartefigue]

Il n'est pas raisonnable de supprimer 90% des enregistrements d'une table d'un milliard de lignes par opération delete :
- delete charge inutilement la log ce qui ralentit l'opération et pénalise les autres transactions
- delete est lent puisqu'il affecte data et index
- delete desorganise le tablespace

Pour cette volumétrie, je décharge la table, traite le fichier unload pour filtrer ce qui est à conserver, trie le fichier résultant sur l'index cluster, et recharge la table en mode replace en activant les statistiques
Ainsi, les données sont purgées, la table est réorganisée, les stats sont à jour et la log n'a pas bougé

Traitement à lancer hors autres activités batch et TP sur la table bien sur

[mister3957]

Fais ça par petit paquet genre :

Code :

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

1
2
3
4
5
6
7
select 1
while @@rowcount > 0
        DELETE TOP(50000) FROM t_commandes WHERE t_commandes.date_validation < '2015-01-01';
 
select 1
while @@rowcount > 0
        DELETE TOP(50000) FROM t_clients WHERE NOT EXISTS (SELECT * FROM t_commandes WHERE t_commandes .client_id = t_clients.id);

Ça ça a l'air pas mal, je connaissais pas DELETE TOP(X).

En revanche, ne faut-il pas rajouter des COMMIT dans les boucles ?

Sinon ça risque de revenir au même, dans qu'une transaction n'a pas été validée, ça conserve "quelque part" les impacts des opérations pour faire un rollback si un problème survient. Enfin je pense..

[mister3957]

Il n'est pas raisonnable de supprimer 90% des enregistrements d'une table d'un milliard de lignes par opération delete :
- delete charge inutilement la log ce qui ralentit l'opération et pénalise les autres transactions
- delete est lent puisqu'il affecte data et index
- delete desorganise le tablespace

Pour cette volumétrie, je décharge la table, traite le fichier unload pour filtrer ce qui est à conserver, trie le fichier résultant sur l'index cluster, et recharge la table en mode replace en activant les statistiques
Ainsi, les données sont purgées, la table est réorganisée, les stats sont à jour et la log n'a pas bougé

Traitement à lancer hors autres activités batch et TP sur la table bien sur

C'est scriptable ça ?

[escartefigue]

Le mieux est de lancer ce type de traitement la nuit par scheduler (automate d'exploitation) avec blocage des ressources pour éviter le lancement de travaux concurrents qui partiraient en -904 ou -911 à cause des utilitaires en cours

[mister3957]

Le mieux est de lancer ce type de traitement la nuit par scheduler (automate d'exploitation) avec blocage des ressources pour éviter le lancement de travaux concurrents qui partiraient en -904 ou -911 à cause des utilitaires en cours

Je m'en suis sorti à coups de modulo parce que avec un DELETE TOP(X) il aurait fallût y claquer un COMMIT derrière et appuyer sur F5 en attendant "0 lignes affectés" étant donné qu'avec un while @@ROWCOUNT ça revenait au même, l'ensemble exécuté sous une même transaction.

La donne à changée, on a compris qu'il ne s'agissait pas d'un script de maintenance, mais d'un script pompier pour débloquer une situation causée par un défaut de maintenance, ou plutôt un défaut de penser à la maintenance..

Sur serveur de production d'un client grand compte à plus d'1 million d'utilisateur quotidien de leur service... Mon métier m'étonnera toujours, et ça c'est super cool !

[Invité]

Je m'en suis sorti à coups de modulo parce que avec un DELETE TOP(X) il aurait fallût y claquer un COMMIT derrière et appuyer sur F5 en attendant "0 lignes affectés" étant donné qu'avec un while @@ROWCOUNT ça revenait au même, l'ensemble exécuté sous une même transaction.

N'importe quoi. Le commit est implicite par défaut avec MS SQL Server. Sur Oracle, non.

[mister3957]

N'importe quoi. Le commit est implicite par défaut avec MS SQL Server. Sur Oracle, non.

Manifestement nous n'étions pas dans un contexte "par défaut", ni sous Oracle.

[StringBuilder]

Enlevez-moi d'un doute...

Le fichier de log... On parle bien du LDF non ?

Si c'est le cas, je vois pas trop quelle différence il y a entre :

delete matable;

delete matable where id mod 2 = 0;
delete matable where id mod 2 = 1;

=> En effet, dans les deux cas, l'intégralité des lignes supprimées vont se retrouver dans le LDF, histoire de pouvoir être récupérées si besoin lors d'une restauration... Non ?

C'est pas plutôt TEMPDB (MDF et LDF) qui posaient problème ? (recopie de 90% d'une table de 1G lignes, ça bouffe effectivement de la place...)

Autant sous Oracle, avec le rollback segment, je comprends la différence entre les deux requêtes, autant avec SQL Server, j'ai un peu plus de mal...

[SQLpro]

Oui les lignes sont biens journalisées pour un DELETE et même pour un TRUNCATE !

A +

[escartefigue]

Seul le load peut - optionnellement - ne pas journaliser

D'où l'intérêt de la solution par unload =>tri => reload/stat pour des suppressions fortement massiques

[SQLpro]

Seul le load peut - optionnellement - ne pas journaliser

D'où l'intérêt de la solution par unload =>tri => reload/stat pour des suppressions fortement massiques

En mode de journalisation BULK ou SIMPLE, oui.

Mais je pense que les temps de chargement et déchargement des fichiers ainsi que le tri externe sera plus long. Notamment si bon parallélisme...

Sans les tables sous forme DDL, les contraintes et la hardware difficile de donner des voies d'optimisation !

A +

[elsuket]

Toute transaction journalise; en revanche effectivement, en mode BULK, la journalisation est minimale (mais pas inexistante).

Une alternative aurait été :

1. scripter la table pour la recréer sous un autre nom
2. réaliser l'INSERT des lignes à conserver
3. échanger les tables par métadonnées, avec un ALTER TABLE maGrosseTable SWITCH to maNouvellePetiteTable

Rétrécir le fichier du journal des transactions à 1Mo se fait au détriment des performances, notamment parce que ce fichier grossira de nouveau, et ensuite parce que le nombre de fichier virtuels (VLF) ce de ce fichier va exploser. Or, traverser un VLF est coûteux aussi bien en CPU qu'en IO, donc il faut bien paramétrer l'incrément de fichier, et peut-être considérer prendre des sauvegardes ce ce fichier plus fréquemment ou passer en mode de récupération BULK

@++