Discussion :

[my_diva]

Bonjour,

Voilà j'ai un script qui a besoin d'une optimisation parce que il prends beacuoup de temps. Pourriez vous m'aider s'il vous plait ?

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
DECLARE @I [INT]
DECLARE @LIMITE [INT] 
DECLARE @QUOTIENT [BIGINT]
DECLARE @ID_COT [BIGINT]
SET @LIMITE = (SELECT COUNT(*) FROM #ID_COT_TMP)
SET @I = 1
 
WHILE @I < @LIMITE + 1
BEGIN 
 
SET @QUOTIENT = (SELECT FLOOR (MAX ( (((CODE_CLE / 100) + 100000) * 100) + 5)) FROM #REF_CLE)
SET @ID_COT = (SELECT ID_COT FROM #ID_COT_TMP WHERE ID = @I)
 
UPDATE #COT_TMP 
SET ID_COT = @QUOTIENT
WHERE ID_COT = @ID_COT
 
INSERT INTO #REF_CLE (CODE_CLE, SRC_CLE, lib_cle)
SELECT DISTINCT ID_COT AS CODE_CLE, SRC_CLE, LIB_CLE
FROM #COT_TMP C
WHERE ID_COT  = @QUOTIENT
 
SET @I = @I + 1
PRINT @I;
END

[mikedavem]

Hello,

Je ne connais ni les tenants ni les aboutissants de cette requête mais je peux déjà dire en voyant seulement la requête que tu risques d'avoir des temps de traitements longs si le volume de données devient important.

Il faut éviter de faire des traitements ligne à ligne ... utilise plutôt une logique ensembliste. Je pense que vu le code que tu as posté c'est largement faisable.

Evite également les tables temporaires si tu peux ...

Maintenant il faut que tu nous en dises plus sur le contexte. Que doit faire ton code exactement ?

++

[my_diva]

Hello,

Alors j'ai 500 000 observations dans #cot_tmp et un select distinct sur cette table sur la variable "id_cot" on va créer la table #id_cot_tmp (avec un "id" incrémentale) --> résultat 170 000 observations dans #id_cot_tmp.
Maintenant, j'ai une boucle qui parcours toute la table #id_cot_tmp (i=id), et à chaque fois qu'on lit un id_cot dans #id_cot_tmp, on MET A JOUR toutes le observations de la table #cot_tmp qui ont le id_cot lu AVEC le MAX ( (((CODE_CLE / 100) + 100000) * 100) + 5), CODE_CLE est récupéré de la table #REF_CLE.
Après mise à jour du id_cot avec la formule ci-dessus, on insert aussi cette nouvelle clé dans #REF_CLE et ainsi de suite jusqu'à la fin de la boucle.

Au final, on aura 170 000 tours de la boucle où on crée 170 000 nouvelle clé qui seront insérées dans #REF_CLE et qui remplacent aussi les id_cot de #cot_tmp tant que #cot_tmp.id_cot=#cot_tmp.id_cot

[aieeeuuuuu]

Bonjour,

Un petit jeu d'essai avec résultat attendu aiderait certainement à mieux comprendre ce que vous voulez.

Il serait aussi utile de savoir ce que fait votre script avant et après. (j'imagine en effet que vous ne faites pas tout cela juste pour insérer dans une table temporaire qui... sera supprimée à la fin )

[marps]

pour avoir réalisé des scripts d'import massif en bdd, je peux vous dire que là c'est un suicide serveur... J'avais à l'époque des triggers qui à chaque insert faisait des calculs et qui prenait 70% du temps d'exécution.

Donc comme le dit mikedavem, le mieux est de faire des update massif et non ligne à ligne surtout avec la masse de données que vous souhaitez traité.

Et comme le dit aieeeuuuuu, un jeu de données "bateau" si ce sont des données sensibles serait le bienvenue pour tenter de comprendre et de pouvoir retravailler le traitement.

[my_diva]

Voilà, mon script a évolué depuis, je n'ai plus besoin d'accéder à la table #ref_cle pour récupérer la plus grande valeur à chaque fois, juste une fois suffit et après j'incrémente.

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
DECLARE @I [INT]
DECLARE @LIMITE [INT] 
DECLARE @QUOTIENT [BIGINT]
DECLARE @ID_COT [BIGINT]
SET @LIMITE = (SELECT COUNT(*) FROM #ID_COT_TMP)
SET @I = 1
 
SET @QUOTIENT = (SELECT FLOOR (MAX (CODE_CLE_COT) / 100) FROM #REF_CLE_COT)+1
 
WHILE @I < @LIMITE + 1
BEGIN 
 
SET @QUOTIENT =( ((@QUOTIENT + 100000) * 100) + 5)
SET @ID_COT = (SELECT ID_COT FROM #ID_COT_TMP WHERE ID = @I)
 
UPDATE #COT_TMP 
SET ID_COT = @QUOTIENT
WHERE ID_COT = @ID_COT
 
INSERT INTO #REF_CLE_COT (CODE_CLE_COT, SRC_CLE_COT, lib_cle_cot)
SELECT DISTINCT ID_COT AS CODE_CLE_COT, SRC_CLE_COT, LIB_CLE_COT
FROM #COT_TMP C
WHERE ID_COT  = @QUOTIENT
SET @QUOTIENT=(@QUOTIENT/100)+1
SET @I = @I + 1
END

Après pour le jeu de test, imaginez la table #cot_tmp qui contient 5 observations où id_cot=17 pour toutes les observations. #id_cot_tmp prendra donc une seule observation (id=1, id_cot=17). Dans #ref_cle, la plus grande valeur est 1005, le quotient prendra donc la valeur 11. La boucle tourne donc une seule fois puisque y a une seule observation dans #id_cot_tmp, i=1 > quotient=10001105 > les 5 observations de #cot_tmp prennent la valeur 10001105 au lieu de 17 pour id_cot et la nouvelle valeur c.-à-d 10001105 est inséré dans #ref_cle

[aieeeuuuuu]

Après pour le jeu de test, imaginez la table #cot_tmp qui contient 5 observations où id_cot=17 pour toutes les observations. #id_cot_tmp prendra donc une seule observation (id=1, id_cot=17). Dans #ref_cle, la plus grande valeur est 1005, le quotient prendra donc la valeur 11. La boucle tourne donc une seule fois puisque y a une seule observation dans #id_cot_tmp, i=1 > quotient=10001105 > les 5 observations de #cot_tmp prennent la valeur 10001105 au lieu de 17 pour id_cot et la nouvelle valeur c.-à-d 10001105 est inséré dans #ref_cle

Rien compris !

Donnez le jeu d'essai sous forme d'INSERT dans les tables concernées... vous aurez beaucoup plus de chances d'avoir des réponses, et elles seront plus justes !

Au passage, donnez aussi la structure des tables en question sous forme de CREATE TABLE.

Par ailleurs, vous n'avez toujours pas indiqué le but du traitement. Que faites vous de ces tables temporaires ?

[my_diva]

Voilà, désolé

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
CREATE TABLE REF_CLE_COT(
 [SRC_CLE_COT] [VARCHAR](100) NULL
,[CODE_CLE_COT] [BIGINT] NOT NULL
,[LIB_CLE_COT] [VARCHAR](150) NULL
)
 
INSERT INTO REF_CLE_COT (SRC_CLE_COT, CODE_CLE_COT, LIB_CLE_COT) VALUES ('TOTO', 17, 'TATA')
INSERT INTO REF_CLE_COT (SRC_CLE_COT, CODE_CLE_COT, LIB_CLE_COT) VALUES ('PIPO', 1005, 'PAPA')
 
 
CREATE TABLE #ID_COT_TMP(
 ID          [INT] NOT NULL IDENTITY(1, 1) PRIMARY KEY
,ID_COT	 [INT] NOT NULL
)
 
INSERT INTO #ID_COT_TMP (ID_COT) VALUES (17)
 
 
CREATE TABLE #COT_TMP(
	[ID_COT] [BIGINT] NOT NULL,
	[SRC_CLE_COT] [VARCHAR](100) NULL,
	[LIB_CLE_COT] [VARCHAR](150) NULL,
        [EXERCICE] [INT] NOT NULL
)
 
 
INSERT INTO #COT_TMP (ID_COT, EXERCICE, SRC_CLE_COT, LIB_CLE_COT) VALUES (17, 2013, 'TOTO', 'TATA')
INSERT INTO #COT_TMP (ID_COT, EXERCICE, SRC_CLE_COT, LIB_CLE_COT) VALUES (17, 2014, 'TOTO', 'TATA')
 
 
 
DECLARE @I [INT]
DECLARE @LIMITE [INT] 
DECLARE @QUOTIENT [BIGINT]
DECLARE @ID_COT [BIGINT]
SET @LIMITE = (SELECT COUNT(*) FROM #ID_COT_TMP)
SET @I = 1
 
SET @QUOTIENT = (SELECT FLOOR (MAX (CODE_CLE_COT) / 100) FROM REF_CLE_COT)+1
 
WHILE @I < @LIMITE + 1
BEGIN 
 
SET @QUOTIENT =( ((@QUOTIENT + 100000) * 100) + 5)
SET @ID_COT = (SELECT ID_COT FROM #ID_COT_TMP WHERE ID = @I)
 
UPDATE #COT_TMP 
SET ID_COT = @QUOTIENT
WHERE ID_COT = @ID_COT
 
INSERT INTO #REF_CLE_COT (CODE_CLE_COT, SRC_CLE_COT, LIB_CLE_COT)
SELECT DISTINCT ID_COT AS CODE_CLE_COT, SRC_CLE_COT, LIB_CLE_COT
FROM #COT_TMP C
WHERE ID_COT  = @QUOTIENT
SET @QUOTIENT=(@QUOTIENT/100)+1
SET @I = @I + 1
PRINT @I;
END

Après la boucle y aura :
Dans REF_CLE_COT : (SRC_CLE_COT, CODE_CLE_COT, LIB_CLE_COT)=('TOTO', 17, 'TATA'), ('PIPO', 1005, 'PAPA'), ('TOTO', 10001105, 'TATA')
Dans #COT_TMP (ID_COT, EXERCICE, SRC_CLE_COT, LIB_CLE_COT)=(10001105, 2013, 'TOTO', 'TATA'),(10001105, 2014, 'TOTO', 'TATA')


Toutes les tables temporaires seront supprimées aprés avoir insérer le contenu de la table #COT_TMP dans une autre table