Optimisation effacement d'entrées sur une très grosse table (100 millions d'entrée)

**DanyDanch** · 01/09/2015, 11h14

Hello,

Voici la description de mon problème:

Je dois faire du nettoyage sur 2 trés grandes table dans une application. Ces 2 tables ont un lien direct (1:N)
Comme ces tables sont employés par notre application, je dois le faire en même temps que l'application tourne et je dois veillez a éviter de rallentir l'application et surtout ne déclencher des timeout sur les requêtes de l'application.
Après analyse je devrais effacer environ 25'000'000 d'entrées dans la table FLAGS_AUTOMATE et environ 8'000'000 d'entrées dans la table RESULTATS_AUTOMATE.

Suivant la stratégie adopté par la base de donnée, ma requête de delete sur 10'000 entrées peut durée entre 1s et plus de 25 secondes. Cette procédure va tourner de 20h à 5h du mat voilà pourquoi je fais un contrôle sur l'heure

Voici le script que j'employe :

Code :

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
DECLARE @delay INT, @nbrRowToDelete INT;
DECLARE @MyDateTime DATETIME
SET @delay = 30
SET @nbrRowToDelete = 10000
SET @MyDateTime = DATEADD(s,1,GETDATE())
WHILE 1 = 1 
BEGIN 
    IF((DATEPART(hh, GETDATE()) > 5) and (DATEPART(hh, GETDATE())) < 20)
		BREAK
    DELETE TOP (@nbrRowToDelete) FA FROM FLAGS_AUTOMATE FA INNER JOIN RESULTATS_AUTOMATE RA ON
    RA.RESULTAT_ID =  REF_ID WHERE AUTMANAGER_STATUT='M' AND CDE4 IS NULL
    IF @@ROWCOUNT = 0 
        BREAK 
    COMMIT;   
    PRINT  cast(@nbrRowToDelete  as varchar) + ' ROWS EFFACEES ' + (CONVERT( VARCHAR(24), GETDATE(), 121))
    SET @MyDateTime = DATEADD(s,@delay,GETDATE())
    WAITFOR TIME @MyDateTime;
END

Pour info cette requête a tourné correctement sur le système d'un client mais a poser soucis après environs 3 h d'éxecution !

Comment faire pour optimiser ma requête ? Comment faire pour garantir que sql server garde mes tables en mémoire ? Est-ce que si je fais un select du même type que le delete mais avant le delete cela améliorera le temps d'execution de ma requete delete ?

Merci beaucoup pour vos conseils !

**mikedavem** · 01/09/2015, 12h03

Hello,

Pour info cette requête a tourné correctement sur le système d'un client mais a poser soucis après environs 3 h d'éxecution !

C'est à dire? Blocages, lenteur d'exécution ?

Autre question, le nombre de lignes que tu veux supprimer est visiblement très important. Quel est le pourcentage de données supprimé par rapport au nombre de lignes total de tes tables?

++

**DanyDanch** · 01/09/2015, 13h30

Envoyé par mikedavem

Hello,

C'est à dire? Blocages, lenteur d'exécution ?

Autre question, le nombre de lignes que tu veux supprimer est visiblement très important. Quel est le pourcentage de données supprimé par rapport au nombre de lignes total de tes tables?

++

1) par lenteur, blocage je voulais dire que notre application faisait des timeout sur les requetes sql lié à ces 2 tables. Le delai est de 30 secondes par sql.
2) cela représente environ 20% du contenu de chacune des tables !

**mikedavem** · 01/09/2015, 13h40

Ok de ce que tu décris, je pense vu de loin que tes lenteurs peuvent être liées aux statistiques qui deviennent obsolètes au fur et à mesure du nombre de suppressions que tu effectues.
J'ai déjà vu ce genre de problèmes chez certains clients. Tu peux le tester en intégrant de temps à autre une mise à jour des statistiques de ta table dans ta routine de suppression . .

Le temps perdu pour la mise à jour des statistiques (phénomène non bloquant du point de vue utilisateur mais peut être consommateur de ressources .. à voir le nombre de pages à lire pour ta table) sera largement compensé par les performances de tes requêtes si je ne me trompe pas.

Edit : cependant reste à voir aussi si cela n'est pas causé par des blocages sur un ensemble de données touché plus particulièrement par les requêtes utilisateurs ... ne connaissant ni ton contexte ni la structure de tes tables etc ...

++

**DanyDanch** · 01/09/2015, 13h51

Envoyé par mikedavem

Ok de ce que tu décris, je pense vu de loin que tes lenteurs peuvent être liées aux statistiques qui deviennent obsolètes au fur et à mesure du nombre de suppressions que tu effectues.
J'ai déjà vu ce genre de problèmes chez certains clients. Tu peux le tester en intégrant de temps à autre une mise à jour des statistiques de ta table dans ta routine de suppression . .

Le temps perdu pour la mise à jour des statistiques (phénomène non bloquant du point de vue utilisateur mais peut être consommateur de ressources .. à voir le nombre de pages à lire pour ta table) sera largement compensé par les performances de tes requêtes si je ne me trompe pas.

Edit : cependant reste à voir aussi si cela n'est pas causé par des blocages sur un ensemble de données touché plus particulièrement par les requêtes utilisateurs ... ne connaissant ni ton contexte ni la structure de tes tables etc ...

++

quand tu dis

de temps à autre

tu penses à quel delai environ ?

Est-ce que tu penses que si je fais un select avec le même critère que le delete avant le delete cela pourrais améliorer le temps du delete (donc la durée du lock sur la table) ?

**mikedavem** · 01/09/2015, 14h08

Est-ce que tu penses que si je fais un select avec le même critère que le delete avant le delete cela pourrais améliorer le temps du delete (donc la durée du lock sur la table) ?

A part effectuer 2 fois une lecture cela ne t'apportera pas grand chose selon moi.

tu penses à quel delai environ ?

Difficile à déterminer mais si on prend ton problème qui se produit au bout de 3h et qui commence à générer des lenteurs .. il faut regarder à combien de lignes cela correspond ... je dirais de prendre ce nombre de lignes / 2 .. mais cela reste une évaluation plutôt hasardeuse .. il faut tester malheureusement .. pas de réponse toute faite à te fournir.

A+

**elsuket** · 01/09/2015, 14h11

Bonjour,

Pour avoir eu ce type d'opérations à réaliser un paquet de fois, j' ai trouvé que la manière la plus fiable et la moins perturbatrice est d'utiliser une table accessoire dans laquelle on stocke la valeur des colonnes de l'index cluster (souvent celles de la clé primaire) de la table à nettoyer. De cette façon le tri pour la requête DML (dans votre cas un DELETE, mais il peut aussi s'agir d'un UPDATE) n'est effectué qu'une seule fois, ce qui fait que la vitesse à laquelle le lot de lignes à supprimer sont localisées dans la table est assez constante à chaque exécution de boucle, puisqu'on passe directement par l'index cluster de la table.

En ce qui concerne leur suppression, elle peut en effet être affectée par le verrouillage, mais comme on supprime peu de lignes à chaque exécution de boucle, cette problématique est généralement d'effet négligeable.

Voici ce que cela donne :

Code :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SELECT		FA.ColonnesDeLaClePrimaire
INTO		dbo.FLAGS_AUTOMATE_FLUSH
FROM		dbo.FLAGS_AUTOMATE AS FA
INNER JOIN	dbo.RESULTATS_AUTOMATE RA
			ON RA.RESULTAT_ID =  REF_ID
WHERE		AUTMANAGER_STATUT = 'M'
AND		CDE4 IS NULL
GO
 
ALTER TABLE dbo.FLAGS_AUTOMATE_FLUSH
ADD CONSTRAINT PK_FLAGS_AUTOMATE_FLUSH PRIMARY KEY (ColonnesDeLaClePrimaire)
GO
 
DECLARE @dt datetime
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.FLAGS_AUTOMATE_FLUSH
)
BEGIN;
	SET @dt = GETDATE();
 
	BEGIN TRY;
		BEGIN TRANSACTION;
			WITH
				CTE AS
				(
					SELECT	TOP (5000) ColonnesDeLaClePrimaire
					FROM	dbo.FLAGS_AUTOMATE_FLUSH
					ORDER	BY ColonnesDeLaClePrimaire
				)
			DELETE	FROM 	dbo.FLAGS_AUTOMATE
			FROM		dbo.FLAGS_AUTOMATE AS FA
			INNER JOIN	CTE AS C
						ON FA.ColonnesDeLaClePrimaire = C.ColonnesDeLaClePrimaire;
 
			WITH
				CTE AS
				(
					SELECT	TOP (5000) ColonnesDeLaClePrimaire
					FROM	dbo.FLAGS_AUTOMATE_FLUSH
					ORDER	BY ColonnesDeLaClePrimaire
				)
			DELETE	FROM 	dbo.FLAGS_AUTOMATE_FLUSH
			FROM		dbo.dbo.FLAGS_AUTOMATE_FLUSH AS FAF
			INNER JOIN	CTE AS C
						ON C.ColonnesDeLaClePrimaire = FAF.ColonnesDeLaClePrimaire;
		COMMIT TRANSACTION;
 
		PRINT 'Deleted 5000 rows in ' + DATEDIFF(millisecond, @dt, GETDATE());
	END TRY;
	BEGIN CATCH;
		-- Capture des détails de l'erreur
		DECLARE @err_msg nvarchar(2048) = ERROR_MESSAGE()
			, @err_svt int = ERROR_SEVERITY()
			, @err_stt int = ERROR_STATE()
			, @err_num int = ERROR_NUMBER();
 
		-- Annulation de la transaction, si elle est toujours active
		IF XACT_STATE() <> 0
		BEGIN;
			ROLLBACK TRANSACTION;
		END;
 
		-- Levée de l'exception
		RAISERROR(@err_msg, @err_svt, @err_stt);
 
		-- Si vous êtes sous SQL Server 2012, ou suivants, vous pouvez aussi écrire :
		-- THROW @err_num, @err_msg, @err_stt;
	END CATCH;
END;

Par ailleurs, une fois la possible erreur éliminée, on peut continuer / reprendre sans problème, puisque seules les valeurs des colonnes participant à l'index cluster qui sont à supprimer sont stockées dans cette table accessoire. Une fois qu'il n'y a plus de valeurs des colonnes participant à l'index cluster dans cette table, le batch s'arrête. Vous pouvez alors faire un DROP TABLE de la table accessoire, que j'ai ici nommée dbo.FLAGS_AUTOMATE_FLUSH.

Pour terminer, comme suggéré, exécutez un UPDATE STATISTICS sur la table cible du nettoyage.

@++

Optimisation effacement d'entrées sur une très grosse table (100 millions d'entrée)

Développement SQL Server

Discussions similaires

Partager

Partager