Optimisation d'un UPSERT [Résolu]

[Marty000]

Bonjour,
je reviens vers vous pour une question d'optimisation.
J'ai une table temporaire remplie avec environ 12 000 données, le but est de vérifier dans chaque donnée si elle est déjà enregistrée dans la table A (correspondance d'id), dans ce cas on fait un update, sinon on fait un insert.

Le soucis est que la procédure stockée que je fais pour le moment prend énormément de temps je trouve environ 3-4mn en local et plus sur le serveur de production.

Voici la partie qui prend du temps, si vous avez une idée, j'ai essayé d'éviter les curseurs (même si j'ai essayé pour voir si c'était plus rapide) :

Code :

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
SELECT *, 0 AS OK
		INTO   ##UPDSTAT
		FROM   ##TABLE_TEMP
		WHERE tiersID NOT IN (SELECT tiersID FROM ##TABLE_TEMP_Erreurs)
 
		-- on commence les verifications, et les insertions
		while EXISTS(SELECT * FROM ##UPDSTAT WHERE OK = 0) 
		begin
 
			-- on démarre une transaction par occurence
			begin transaction @nomTransaction	
				SET @errors=0;
				-- dans un premier temps, on recupere l'ensemble des informations de la table temporaire
 
				SELECT TOP 1  @tiersID = tiersID, raison_sociale = @raisonsociale
               FROM   ##UPDSTAT WHERE OK = 0 ORDER  BY 1;
 
				print 'On traite : '+cast(@tiersID AS varchar(50));
				IF EXISTS(SELECT * FROM TABLE_A WHERE tiersID = @tiersID)
				begin	
					-- maj de la table
					UPDATE TABLE_A
						SET raison_sociale = @raisonsociale
						WHERE tiersID = @tiersID;
					SET @errors = @errors + @@ERROR --On additionne l'erreur liée à la dernière requête SQL dans notre variable		
 
				end
				else 
				begin
					-- creation de l'info
					INSERT INTO TABLE_A(tiersID, raison_sociale) 
					VALUES (@tiersID, @raisonsociale);
					SET @errors = @errors + @@ERROR --On additionne l'erreur liée à la dernière requête SQL dans notre variable
 
				end
 
				UPDATE ##UPDSTAT SET OK = 1 WHERE  tiersID = @tiersID
 
				SET @errors = @errors + @@ERROR --On additionne l'erreur liée à la dernière requête SQL dans notre variable
 
			-- on verifie si l'on a eu des erreurs ou non, si oui on exporte un fichier d'erreurs sinon on commit les modifications
			-- si errors est égale à 0, il n'y a eu aucune erreur, on commit la transaction
			IF @errors = 0
			begin
				commit transaction @nomTransaction
			end
			else
			begin
				-- on annule tous les changements de cette transaction
				rollback transaction @nomTransaction
 
				-- on insère toutes les donnees de la table temporaire dans la table temporaire d'erreurs
				INSERT INTO ##TABLE_TEMP_Erreurs(tiersID,raison_sociale)
				VALUES (@tiersID,@raisonsociale) 
 
			end
		end --fin boucle

Il y a sûrement quelque chose de choquant... Mais je ne vois pas où.
Merci de votre aide.

[zinzineti]

Envoyé par Marty000

Bonjour,
le but est de vérifier dans chaque donnée si elle est déjà enregistrée dans la table A (correspondance d'id), dans ce cas on fait un update, sinon on fait un insert.

Si vous travaillez avec une version de SQL SERVER >= 2005 alors utiliser
MERGE. ici une petite démo de MERGE

De + il me semble que l'utilisation de variable table @table est plus conseillé (performant) que l'utilisation de table temporaire ##table .

[Marty000]

Merci pour votre réponse,
malheureusement j'ai oublié de le préciser je suis sous SQL Server 2005, je vais essayer de modifier les tables temporaires.
Si vous avez d'autres idées, merci.

[zinzineti]

Envoyé par Marty000

Merci pour votre réponse,
malheureusement j'ai oublié de le préciser je suis sous SQL Server 2005, je vais essayer de modifier les tables temporaires.
Si vous avez d'autres idées, merci.

Tu peux donc utiliser MERGE et les variables table sous SQL 2005.

[Marty000]

D'après le lien http://technet.microsoft.com/fr-fr/l.../bb510625.aspx, je vois qu'il n'est disponible sous SQL Server 2008, pareil pour ce lien : http://www.developpez.net/forums/d90...ql-server2005/

Il ne me reconnait pas le mot clé MERGE

[zinzineti]

dsl

[Marty000]

Pas grave
Merci de ton aide en tout cas, si quelqu'un à une autre idée.

[Waldar]

Vos problèmes de performances viennent des boucles, vous vérifiez les lignes une par une.

L'idée c'est de faire deux opérations ensemblistes, je ne rentre pas complètement dans le détail mais il faut partir sur quelque chose de ce type :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE TA
   SET TA.raison_sociale = UD.raisonsociale
  FROM TABLE_A AS TA
       INNER JOIN ##UPDSTAT as UD
         ON UD.tiersID = TA.tiersID
 
INSERT INTO TABLE_A (tiersID, raison_sociale)
SELECT UD.tiersID, UD.raison_sociale
  FROM ##UPDSTAT as UD
 WHERE NOT EXISTS (SELECT NULL
                     FROM TABLE_A AS TA
                    WHERE TA.tiersID = UD.tiersID)

[Marty000]

Merci Waldar,
avant de voir votre réponse j'étais parti sur ce genre de solution, le fait que vous avez eu la même idée "valide" mes évolutions.
Merci à tous.