Discussion :

[m-mas]

Bonjour,
j'utilise une procédure stocké de Mise à jour de données sur une table pouvant contenir quelques milliers de lignes (bien sûr les curseurs deviennent trés pénalisant à ce niveau)

j'expose le problème : j'ai 2 tables, une table de référence (TR) contenant des compte généraux théorique (CG) et leur codification (CCG).

une deuxième table utilisateur (TU) qui contient des comptes généraux utilisateur (CGU) et un champs CGUT à mettre à jour avec CCG

le but est de mettre à jour le compte général théorique de la table utilisateur (UPDATE TU SET CGUT = TR.CCG)

cependant ceci est plus compliqué qu'il ne paraisse, je doit comparer gauche(TR.CG, LEN(TU.CGU)) avec TU.CGU et trouver la codification exacte, si pas de codification, je décrémente LEN(TU.CGU) de 1 et je reteste jusqu'a trouvé la codification (ou ne rien trouver en atteignant 1)

donc, j'ai utilisé 2 curseurs, 1 sur la table TU et à l'intérieur de ce curseur, un deuxième curseur sur TR qui fait les comparaisons et décrémente s'il ne trouve rien

voila je ne suis pas trés inspiré !

est ce que vous voyez une optimisation ? (je pense que le problème consiste à la dé-crémentation, sinon j'aurais pu utiliser des jointure entre requêtes)

Merci.

[HULK]

Bon je dis peut etre une betise, mais si j'ai bien compris tu peux peut etre dégrossir les données à traiter avec tes curseurs.

Est ce que tu peux pas deja Updater les champs qui on une codification qui existe en faisant juste une rlation entre tes deux tables (tu ajoute un champ boolean qui tu passe à true pour ces champs là)

Et tu passe ton curseur que sur ceux qui ont un flag à false.Tu aura peut etre dejà beaucoup moins de lignes à traiter avec tes curseurs.

[m-mas]

oui HULK je creuse dans ce sens, déjà je veux supprimer mon deuxième curseur et n'utiliser que le premier ! au lieu de faire une boucle et des tests si j'ai trouvé la valeur, je vais essayer d'utiliser les requêtes directement !

[SQLpro]

Vous pouvez complétement supprimez vos curseurs. Dans un de mes puzzles je montre comment réaliser une telle opération consistant à trouver la meilleure occurence partielle en une seule requête. Si ce puzzle est proche de votre demande :
http://sqlpro.developpez.com/SQL_AZ_P.html
"Problème n° 18 - Meilleure correspondance partielle "
postez le DDL de vos tables (create table) et un jeu d'essais) et je vous donnerais la solution.

A +

[m-mas]

Vous pouvez complétement supprimez vos curseurs. Dans un de mes puzzles je montre comment réaliser une telle opération consistant à trouver la meilleure occurence partielle en une seule requête. Si ce puzzle est proche de votre demande :
http://sqlpro.developpez.com/SQL_AZ_P.html
"Problème n° 18 - Meilleure correspondance partielle "
postez le DDL de vos tables (create table) et un jeu d'essais) et je vous donnerais la solution.

A +

Merci beaucoup

demain, je posterais le DDL avec le jeu d'essais ainsi que la procédure que j'utilise (j'ai réussi à me débarasser d'un curseur et ne garder qu'un, avec 15000 lignes de données, temps de traitement devient 1 m 15 au lieu de 12 min avec 2 curseurs)

Encore Merci sqlPro

[SQLpro]

Inspirez vous de l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...r_avoidCursor/

A +

[m-mas]

justement, je me suis inspiré de cet article pour optimiser !

voici la structure des tables ainsi que quelques lignes de données :

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
19
20
21
22
23
24
25
26
27
 
CREATE TABLE TRef(
TRef_ID int NOT NULL,
TRef_CG varchar(50),
TRef_CG_CODAGE varchar(50),
CONSTRAINT PK_TRef PRIMARY KEY (TRef_ID)
)
 
INSERT INTO TRef Values (1,401,4401)
INSERT INTO TRef Values (2,4019,44019)
INSERT INTO TRef Values (3,4017,44017)
INSERT INTO TRef Values (4,403,4403)
INSERT INTO TRef Values (5,4041,44041)
INSERT INTO TRef Values (6,40417,440417)
 
CREATE TABLE TUti(
TUti_ID int NOT NULL,
TUti_CG varchar(50),
TUti_CG_CODE varchar(50),
CONSTRAINT PK_TUti PRIMARY KEY (TUti_ID)
)
 
INSERT INTO TUti Values (1,401,'')
INSERT INTO TUti Values (2,4011,'')
INSERT INTO TUti Values (3,4017,'')
INSERT INTO TUti Values (4,5,'')
INSERT INTO TUti Values (5,403,'')

le but est de mettre à jour TUti_CG_CODE avec TRef_CG_CODAGE (selon les conditions cité dans mon premier post).
j'ai simplifié bien sûr les tables et les données, et je n'ai mis que ce que j'ai comme difficulté à optimiser.
Voici la procédure stocké que j'utilise pour mettre à jour (et qui marche trés bien):

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
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
 
CREATE  PROCEDURE Update_TUtil AS
 
DECLARE @nb_car_max INT
SELECT @nb_car_max =  MAX(LEN(TRef_CG)) 
FROM TRef
 
DECLARE @UTI_CG VARCHAR(50)
 
DECLARE @n INT		--n = nombre de caractère de TUti_CG
DECLARE @Ok INT
-- declaration du 1er curseur
DECLARE MyCursor CURSOR 
FOR 
   SELECT TUti_CG FROM TUti
 
OPEN MyCursor
FETCH MyCursor INTO @UTI_CG
 
WHILE @@fetch_Status = 0
BEGIN
 
SELECT @n  = LEN(@UTI_CG)
 
--traitement
IF @n>@nb_car_max SET @n=@nb_car_max
 
DECREMENT:
 
SELECT @Ok = count(1) FROM TRef
WHERE TRef_CG = LEFT(@UTI_CG,@n)
 
IF @Ok =1 
	update TUti set TUti_CG_CODE =( 
	SELECT TRef_CG_CODAGE 
	FROM TRef
	WHERE TRef_CG = LEFT(@UTI_CG,@n))
	WHERE TUti_CG = @UTI_CG
 
IF @Ok <> 1 AND @n <> 1
	BEGIN
	SET @n = @n-1
	GOTO DECREMENT
	END
 
--suivant 
   FETCH MyCursor INTO @UTI_CG
END
 
CLOSE myCursor
DEALLOCATE myCursor
 
GO

la difficulté est donc pour mettre à jour la deuxième ligne de Tuti (4011), il n'y a pas de correspondance avec TRef, il faut donc décrémenter le 4011 de 1 et donc chercher une correspondance entre 401 et TRef (on va trouver 4401 ...).
On doit aller jusqu'a ce que n =1, si on trouve rien on met à jour avec NULL (comme pour la ligne avec TUti_CG = 5)

Voila, je ne sais pas si j'ai la bonne inspiration à faire comme ça !

A+