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]

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 ?

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