Discussion :

[KHVince]

Bonjour,

Je suis confronté à un problème très gênant de performance SQL !
Je dois insérer un grand nombre de ligne dans une table à partir d'un fichier à plat.
Ce fichier comporte environ 190 000 lignes (ce n'est pas mon plus gros fichier ).

Pour faire ces insertion, j'utilise le BULK INSERT dans une procédure stockée.
code ma procédure :

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
CREATE PROCEDURE [dbo].[SP_IMPORTDATA_WEBIDXPA]
	@Path VARCHAR(48),
	@OutputBulk INT OUTPUT,
	@OutputBefore INT OUTPUT,
	@OutputAfter INT OUTPUT,
	@Reprise INT
AS
 
--Déclaration
DECLARE @ReqBulk NVARCHAR(256)
DECLARE @ReqTmp NVARCHAR(256)
DECLARE @ParamTmp NVARCHAR(64)
 
DECLARE @ACCESR VARCHAR(256)
 
BEGIN
 
	--On vide la table des erreurs
	TRUNCATE TABLE ERREUR_TRAITEMENTS
 
	--Si le parametre @Reprise = 1, on vide la table
	IF @Reprise = 1
		BEGIN
			TRUNCATE TABLE WEBIDXPA
		END
 
	--Test les parametres
	IF @Path IS NULL OR @Path=''
		BEGIN
			INSERT INTO ERREUR_TRAITEMENTS (errTable, errError, errResult) VALUES ('WEBIDXPA', 'Paramétre vide', '(WEBIDXPA) Echec')
			GOTO fin
		END
 
	--Récupération du nombre d'enregistrements de la table WEBIDXPA avant modification
	SET @ReqTmp = ''
	SET @ReqTmp = 'SELECT @OutputBefore=count(*) FROM WEBIDXPA'
	SET @ParamTmp = N'@OutputBefore INT OUTPUT'
	EXECUTE sp_executesql @ReqTmp, @ParamTmp, @OutputBefore OUTPUT
 
	--Création d'une table temporaire identique à WEBIDXPA
	CREATE TABLE #WEBIDXPA_TMP
		(
		NOADSR VARCHAR(8), NOINDR VARCHAR(8), GPDOCR VARCHAR(20), SGDOCR VARCHAR(20), TPDOCR VARCHAR(16), ACCESR VARCHAR(80), DTDOCR VARCHAR(8), DTMAJR VARCHAR(8), NBPAGR VARCHAR(2)
		)
 
	-- Insertion dans la table temporaire
	SET @ReqBulk = 'BULK INSERT #WEBIDXPA_TMP FROM ''' + @Path + '\IDXTemp''  WITH (FIELDTERMINATOR=''|'', ROWTERMINATOR=''\n'', CODEPAGE=''RAW'')'
	-- exécution de la requête pour l'insertion des données dans la table
	ALTER DATABASE AFER SET RECOVERY BULK_LOGGED
	EXECUTE sp_executesql @ReqBulk
	ALTER DATABASE AFER SET RECOVERY FULL
 
	--Récuperation du nombre de ligne inséré dans #WEBIDXPA_TMP
	SET @ReqTmp = ''
	SET @ReqTmp = 'SELECT @OutputBulk=count(*) FROM #WEBIDXPA_TMP'
	SET @ParamTmp = N'@OutputBulk INT OUTPUT' 
	EXECUTE sp_executesql @ReqTmp, @ParamTmp, @OutputBulk OUTPUT
 
 
	--Création du cursor pour la mise à jour de la table WEBIDXPA
	DECLARE cursor_tmpIDX_maj CURSOR FOR
	SELECT ACCESR FROM #WEBIDXPA_TMP
	OPEN cursor_tmpIDX_maj
	FETCH NEXT FROM cursor_tmpIDX_maj
	INTO @ACCESR
	WHILE @@fetch_status = 0
	BEGIN
		IF EXISTS(SELECT 1 FROM WEBIDXPA WHERE ACCESR=@ACCESR) --On met a jour l'enregistrement
			BEGIN
			UPDATE WEBIDXPA
			SET
				NOADSR = source.NOADSR, NOINDR = source.NOINDR, GPDOCR = source.GPDOCR, SGDOCR = source.SGDOCR,
				TPDOCR = source.TPDOCR, ACCESR = source.ACCESR, DTDOCR = source.DTDOCR, DTMAJR = source.DTMAJR, NBPAGR = source.NBPAGR
			FROM #WEBIDXPA_TMP source
			WHERE source.ACCESR = WEBIDXPA.ACCESR
			END
		ELSE --On ajoute un enregistrment
			BEGIN
			INSERT INTO WEBIDXPA 
				(NOADSR, NOINDR, GPDOCR, SGDOCR, TPDOCR, ACCESR, DTDOCR, DTMAJR, NBPAGR)
			SELECT 
				NOADSR, NOINDR, GPDOCR, SGDOCR, TPDOCR, ACCESR, DTDOCR, DTMAJR, NBPAGR
			FROM #WEBIDXPA_TMP
			WHERE #WEBIDXPA_TMP.ACCESR = @ACCESR
			END
		FETCH NEXT FROM cursor_tmpIDX_maj INTO @ACCESR
	END
	CLOSE cursor_tmpIDX_maj
	DEALLOCATE cursor_tmpIDX_maj
 
 
	--Récupération du nombre d'enregistrements de la table WEBIDXPA après modification
	SET @ReqTmp = ''
	SET @ReqTmp = 'SELECT @OutputAfter=count(*) FROM WEBIDXPA'
	SET @ParamTmp = N'@OutputAfter INT OUTPUT'
	EXECUTE sp_executesql @ReqTmp, @ParamTmp, @OutputAfter OUTPUT
 
	--nbOperation : Nombre de ligne insérée dans la base IDX temporaire
	--nbBefore : Nombre de ligne dans la table IDX avant traitement
	--nbAfter : Nombre de ligne dans la table IDX apres traitement
	SELECT @OutputBulk as nbOperation, @OutputBefore as nbBefore, @OutputAfter as nbAfter
 
fin:
 
END
GO

Au niveau temps de traitement, je suis très très inquiet car j'ai lancé le traitement hier vers les 17h - 17h30 et ça c'est fini à 2h du matin

Il y a un problème car mettre 9h pour insérer 190 000 lignes, ce n'est pas normal et ce n'est pas envisageable pour mes traitements

Donc si vous voyez un truc qui cloche dans ma procédure ou des conseils d'optimisation, je suis preneur !

Merci de votre aide
Vincent

[SQLpro]

Faire ceci avec un curseur est la cause principale de vos temps de réponse dégueulasse. Une base de données est faite pour faire de l'ensembliste et non de l'itératif. L'itératif ira 10, 100, 1000 fois moins vite que l'ensembliste en fonction du volume et des index posés.

Dans votre cas il suffit de faire une requête UPDATE avec votre table temporaire et une requête INSERT avec cette même table temporaire.

Il faut aussi indexer votre table temporaire sur le critère de filtrage.

Enfin, il faut désactiver tous les index non sémantiques qui ne sont pas nécessaire au traitement.

Ceci devrait conduire à un temps de l'ordre de quelques minutes.

Si vous n'y arrivez pas, n'hésitez pas à me contacter...

A +

[KHVince]

Merci,
Je vais modifier ma procédure pour éviter l'utilisation du curseur.

Par contre, je n'ai pas approfondi mes tests mais sur des petits volumes de données, cela reste rapide.
Test fait avec 2000 lignes à insérer, cela pris 5s avec l'utilisation d'un cursor.
Je ferrais le même test sans l'utilisation de ce cursor pour voir le temps que cela prend.

Je vous tiens au courant du résultat.

Merci,
Vincent

[SQLpro]

En ce moment je fais des tests d'insertion sur mon portable et pour 63000 lignes cela met 320 ms...... sans curseur bien entendu !

Plus le volume est petit, moins la différence est important. Plus le volum est fort, plus la différence est sensible. C'est exponentiel !

A +

[KHVince]

J'ai changé ma procédure stockée et effectué mes tests.
De 9h de traitement, je suis passé à 1min17 !

Je suis quand même un peu étonné d'un tel écart
Enfin je ne vais pas m'en plaindre !

Merci beaucoup pour l'aide, je vais pouvoir partir en WE tranquillement