Discussion :

[yoyo88]

Bonjours a tous,

voila j'ai besoin de vos lumière pour trouver une solution optimiser a un problème assez simple.

J'ai une table avec 50 000 000 de lignes, je dois faire un ménage dedans.
je souhaite donc faire un insert de mes lignes que je vais supprimer dans une base de données qui me servira de base d'historique.
j'ai donc ce code très simple.

Code :

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

1
2
3
4
5
6
7
8
9
 
INSERT INTO [BD_HISTORIQUE].[dbo].[T_LINE]
SELECT
  FROM [BD].[dbo].[T_LINE]
  WHERE yr < 2014
 
DELETE
  FROM [BD].[dbo].[T_LINE]
  WHERE yr < 2014

Rien de méchant, mais y'a t'il une méthode plus optimiser en terme de temps de traitement ?

[escartefigue]

Ce n'est pas tant les 50 000 000 de lignes présentes qui comptent, que le nombre de lignes candidates à suppression à chaque traitement.
Si par exemple il y a 10 000 000 de lignes < 2014 alors il est fortement recommandé de ne PAS FAIRE DE DELETE mais un traitement batch de purge comme suit :
- déchargement de la table par unload
- sélection des enregistrements à conserver reconduits dans un fichier pour rechargement
- tri de ce fichier sur la clef cluster si index cluster il y a
- rechargement en mode replace avec option statistiques et log no
- rebind des packages

La solution Delete sera lente voire extrêmement lente si pas d'index sur le critère date ou index non discriminant, et chargera lourdement et inutilement la log

[Drizzt [Drone38]]

Il faut voir aussi si c'est du one-shot ou si c'est une opération qui sera repetée souvant.

Faire la procédure decrite par escartefigue pour du one-shot, c'est probablement un peu couteux si tu ne l'as jamais fait. Et pour du one-shot, les perfs ne sont pas forcément problématiques (surtout pour 50 millions de lignes qui reste une volumétrie raisonnable). Cela suppose à minima un index (ou partitionnement) sur le champ yr cependant (sinon la oui ça va être catastrophique).

Dans tous les cas, si effectivement il y a beaucoup de lignes concernées par la supression (ie > 500 000), le insert et delet brut comme cela ne fonctionnera probablement pas. Tu vas saturer le log/tablespace temporaire ce qui fera planter ta requête.

Si tu restes sur un process par requête, il faudra donc travailler par paquet et valider la transaction entre chaque.

[yoyo88]

Merci pour vos réponses,

Non le champ yr n'es pas indexé et pas d'index tous cour...
C’est aussi pour cela que l'on souhaite purger la table.

C’est un traitement que l'on souhaite exécuter chaque année.

J’en conclue qu'il faut que je mette en place la procédure de escartefigue, j'index ma table, puis je fais un job avec des requêtes pour les prochaines années (il y aura beaucoup moins de ligne et ça devrait passer).

[aieeeuuuuu]

Non le champ yr n'es pas indexé et pas d'index tous cour...
C’est aussi pour cela que l'on souhaite purger la table.

Donc vous faites cela pour résoudre des problèmes de performances ?
Pourquoi ne pas indexer ? ce serait plus simple et plus efficace non ?

C’est un traitement que l'on souhaite exécuter chaque année.

alors un index sera certainement inutile, puisque le delete concernera la quasi totalité de la table !

Expliquez un peu votre situation de façon plus globale, et détaillez vos problèmes, on pourra mieux vous aider. Précisez aussi votre SGBD

[yoyo88]

SGBD : SQLServer 2005

en gros, on a une table non indexé qui est devenu tellement énorme que la moindre requête et devenu extrêmement compliqué.
On souhaite pouvoir l'indexé.
mais comme elle est trop énorme c'est impossible.

Comme on n'a pas besoin de garder toutes les données, nous nous somme dis que l'on pourrait l'amputée d'un partie de ses données. mais l'idéal serrait bien sur de les mettre de coté (au cas ou)

mais problème, la table est trop grosse et pas indexé

(visiblement mon problème est plus complexe que se que j'avais imaginer... )

[Drizzt [Drone38]]

mais comme elle est trop énorme c'est impossible.

Et pourquoi cela ? Pas assez de place sur le serveur ?

La création de l'index va être un peu douloureuse mais ensuite ça sera noel. 50M de lignes sans index... comment dire il y a de quoi énerver un ou deux utilisateurs de l'application là


Sinon un partitionnement par année ou mois pourrait être une bonne idée aussi. (En plus pour les suppression d'un mois complet il suffira de faire un Drop partition)

Sinon voir aussi les tutoriels et cours de formation pour apprenfre SQL

[escartefigue]

Une table sans index est d'un intérêt très limité (hors table archives)

Il y aura de toutes façon besoin d'index si les données sont utilisées que ce soit en TP ou en batch,
Donc, purge ou pas, il faut réfléchir aux types d'accès et créer les index correspondants, ca va prendre un peu de temps mais c'est inévitable

[yoyo88]

Et pourquoi cela ? Pas assez de place sur le serveur ?

La création de l'index va être un peu douloureuse mais ensuite ça sera noel. 50M de lignes sans index... comment dire il y a de quoi énerver un ou deux utilisateurs de l'application là


Sinon un partitionnement par année ou mois pourrait être une bonne idée aussi. (En plus pour les suppression d'un mois complet il suffira de faire un Drop partition)

Je suis très loin d’être un spécialiste de SQL mais la création de l'index sur une table a 50M ligne qui est en prod, ça risque pas d’être extrêmement douloureux pour mon serveur ?


@escartefigue : oui c'est inévitable, mais encore faut il pouvoir les créer.

[SQLpro]

Je suis très loin d’être un spécialiste de SQL mais la création de l'index sur une table a 50M ligne qui est en prod, ça risque pas d’être extrêmement douloureux pour mon serveur ?


@escartefigue : oui c'est inévitable, mais encore faut il pouvoir les créer.

C'est aussi douloureux que d'éradiquer chirurgicalement un cancer... Soit on le garde, soit on l'enlève... Quel mal préférez-vous ?

Effectivement ne pas mettre de clef primaire, d'index est de contraintes sur une table est d'un niveau de stupidité abyssal. Ceux qui font ça font encore du CoBOL sans la savoir !

A +

[yoyo88]

Je viens d'apprendre que une indexation a déjà été tenté , mon ça tourne dans le vide :/

[CinePhil]

Ça me rappelle une histoire que racontait un ingénieur qualité en début de formation au personnel...

Un type se promène et voit un pauvre bougre suer sang et eau à essayer de scier une grosse branche. Il s'approche et dit :
" Bonjour ! Ne croyez-vous pas que vous devriez aller aiguiser votre scie ?"
Et le pauvre bougre de répondre :
" Je n'ai pas le temps, il faut que je scie ! "

Je crois qu'il est grand temps que vous aiguisiez votre table à coup d'index !

Une table de 50 millions de lignes sans index... autant utiliser un fichier plat, l'ouvrir avec un éditeur de texte (s'il y arrive) et utiliser la fonction rechercher de celui-ci !

[escartefigue]

Je viens d'apprendre que une indexation a déjà été tenté , mon ça tourne dans le vide :/

NON ça ne tourne pas dans le vide, quand on essaye de créer des index, il y a une log avec des messages et des codes diagnostic !

Si ca n'a pas abouti, il faut consulter cette log et corriger

Plein de possibilités : problèmes de droits insuffisants, index space full, clefs en double (très probable vu qu'il n'y avait pas d'index) etc...

[yoyo88]

Bon,
j'ai trouver une solution, loin d’être une solution miracle, mais qui va me permette de pas mettre mon serveur a plat quand je créai mes index (parce que visiblement le problème lors de la création des index était que le serveur n'arrivais tous simplement pas a suivre...).

je vais déjà purger ma table par petit bouts au lieu de couper a grand coup de hache.
après un premier test mes requête dure 1 minute en moyenne. bon je vais devoir faire ça 500 fois mais au moins ça permettra de pas mettre le serveur HS.

après il faudra indexé, mais ça serra beaucoup plus supportable pour le serveur puisque ma table va être réduite de 3/4.

[escartefigue]

Plutôt que de faire 500 fois des delete, ce qui est une opération risquée car répétitive (donc la vigilance va baisser et c'est là que les bêtises se produisent), et le problème de log surchargée ne sera d'ailleurs toujours pas résolu, je maintiens qu'il vaut bien mieux décharger par unload, purger puis recharger (cf. mon post précédent).
C'est loin d'être compliqué et beaucoup plus sécurisé
Autre avantage, la table sera automatiquement réorganisée à la fin de l'opération

[yoyo88]

En faite, j'ai fais un PS, et en gros j'ai plus cas l'exécuter. le risque est maîtrisé et NON je compte pas lancer 500 fois à la mains mes requête

pour votre solution, pour ma part, elle m'a l'air compliqué (mes connaissance en SQL sont limité, et se que vous m'expliquer et complexe pour moi). après c'est pas inintéressant j’aurai un peu plus de connaissance j’aurai sans doute foncer.

[StringBuilder]

J'ai un peu de mal à suivre le problème avec 50 millions de lignes dans une table sous SQL Server... Même si 2005 est un peu vieillissant, ça reste un moteur puissant et robuste...

Sur mon pauvre portable à deux balles, Windows 7, SQL Server 2014 Express, sans disque SSD, je viens de faire tourner ce script :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table test50M
(
	id uniqueidentifier default newid(),
	annee int,
	valeur varchar(50) 
);
go
 
declare @i int;
declare @cos float;
set @i = 0;
 
while @i < 5000000
begin
	select @cos = cos(@i);
	insert into test50M (annee, valeur) values (2010 + cast(@cos * 10 as int), cast(@cos as varchar(50)));
	select @i = @i + 1;
end;

Des données donc assez aléatoires, pas de clé, pas d'index. Rien, le néant. 50 millions de lignes purement merdiques, réparties selon n'importe quel ordre (avec toutefois une certaine périodicité pour les années, en raison du COS, ce qui implique une bonne répartition des années dans la table).

Au bout de quelques minutes (avec une installation de Visual Studio en parallèle, ce qui n'est pas rien en termes de surcharge IO), j'ai mes 50 millions de lignes.

J'ai pu sans problème créer un index dessus... Je vois pas trop le problème en fait...

Code sql :

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

1
2
 
create index idx_annee on test50M (annee);

(????????)

On est bien d'accord... On parle de 50 pauvres petits millions de lignes, pas de 50 milliards hein ?

Bon, ok, après mes lignes ne font même pas 100 octets de long... Certes... Mais j'ai un portable, pas un serveur non plus...


PS : Par contre, je ne vois aucun conseil quant au principal goulot d'étranglement ici... Les disques dur.

Créer la table d'archive dans un fichier de base de données placé sur un disque dur distinct de la base actuelle. Ainsi la copie des données sera très largement plus rapide.

[Drizzt [Drone38]]

Code sql :

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

1
2
3
4
5
6
7
8
while @i < 5 000 000
begin
	select @cos = cos(@i);
	insert into test50M (annee, valeur) values (2010 + cast(@cos * 10 as int), cast(@cos as varchar(50)));
	select @i = @i + 1;
end;

Il te manque un 0 là
Bon ça ne change pas que 50M ça devrait pas poser de problème majeur pour créer un index.

[escartefigue]

@ stringbuilder : le problème n'est pas l'insertion mais la suppression, le delete en masse n'est jamais une bonne solution

[yoyo88]

Oui, faire un Select ou un insert ne devrait pas poser de problème, sauf que on parle d'une table très demandée sur une base très demandée, sur un serveur très demandé.

je ne dis pas qu'il n'y a pas d'autres problèmes. mais le fait que j'ai pas mal de user qui travaillent dessus ça n'aide pas.


la solution que j'ai trouvée, bien que pas top, semble arranger les choses. une fois que j’aurai fini ça je m'attaquerai à l'indexation comme prévu. (car oui, le but initial était de pouvoir indexer la table)


en tous cas merci pour votre aide.
et notament escartefigue, faut que je creuse ta solution, elle pourrait m'aider pour d'autres tables du genre.