[OPTIMISATION]fonction de concaténation

**m-mas** · 14/06/2007, 09h56

Bonjour,

je suis sous SQL server 2000

j'ai une fonction de concaténation de différents champs que j'utilise pour insérer des données dans une 2éme table.

Voici ma fonction :

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
 
CREATE    FUNCTION FN_CONCAT (@ECR_ID NUMERIC)
RETURNS VARCHAR(8000)
 
AS
BEGIN
DECLARE @CH3 VARCHAR(8000)
SET @CH3=''
SELECT @CH3=@CH3+
ISNULL(CG_C1,'') + ';'
+ ISNULL(CG_C2,'') + ';'
+ ISNULL(CONVERT(VARCHAR(10),CG_C3),'')+';'
+ ISNULL(CG_C4,'') +';'
+ ISNULL(CG_C5,'') +';'
+ ISNULL(CG_C6,'')
+ ';'
FROM T_ECR
WHERE CG_ECR_ID=@ECR_ID
 
IF (@CH3 <>''  ) SET @CH3= LEFT(@CH3, LEN(@CH3)-1)
 
RETURN @CH3
 
END

l'appel à ma fonction se fait via cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
insert into T_TECH
(TECH_ID,TECH_C)
SELECT distinct CG_ID,
dbo.FN_CONCAT(CG_ECR_ID),
FROM T_ECR
ORDER BY
CG_ECR_ID

A faible volume de ma table T_ECR, cette fonction marche trés bien, mais si j'ai beaucoup de données (30000 lignes par exemple dans T_ECR dont 9000 CG_ECR_ID différents) ma fonction prend 18 minutes

ma table T_ECR continet juste un index sur sa clé primaire (numéro automatique) et même si je crée un index sur l'ensemble des CG_Cn qui sont sélectionnés dans la concaténation, ceci ne s'améliore pas !

Voyez vous quelques choses pour optimiser ?

Merci.

**serge0934** · 14/06/2007, 10h06

c'est ton distinct qui fait ramer car si tu n'insères que 9000 lignes il fait quand même appel 30000 fois a ta fonction
je te conseille de passer par une temp

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT CG_ID,CG_ECR_ID
into #temp
FROM T_ECR
ORDER BY
CG_ECR_ID
 
INSERT INTO T_TECH
(TECH_ID,TECH_C)
SELECT CG_ID,dbo.FN_CONCAT(CG_ECR_ID)
FROM #temp
 
drop table #temp

**m-mas** · 14/06/2007, 11h19

tout à fait ! je n'ai pas pensé à ça

a noter, que si j'utilise des curseurs (qui est déconseillé); j'obtient un temps de traitement de 9 min à peu prés (gain de 50%)

Avec l'utilisation des tables temporaires (comme tu me l'a suggéré) sans curseur ça dure 3 min alors avec un curseur ça ne prend qu' 1 min 30

je pense que je vais opter pour un curseur + table temporaire (1min30) qui est un temps de traitement inespéré

A titre d'indication, avec le curseur ma fonction deviens :

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
 
CREATE    FUNCTION FN_CONCAT_CURSOR (@ECR_ID NUMERIC)
RETURNS VARCHAR(8000)
 
AS
BEGIN
declare @var varchar(255)
declare cur cursor for
(select ISNULL(CG_C1,'') + ';'
+ ISNULL(CG_C2,'') + ';'
+ ISNULL(CONVERT(VARCHAR(10),CG_C3),'')+';'
+ ISNULL(CG_C4,'') +';'
+ ISNULL(CG_C5,'') +';'
+ ISNULL(CG_C6,'')
+ ';'
FROM TSMCG_ECRITURE_MOUVEMENT 
where cg_ecrm_ecriture_id=@ecr_id
 
DECLARE @CH3 VARCHAR(8000)
SET @CH3=''
open cur
fetch cur into @var 
while @@fetch_status =0 
begin
SELECT @CH3=@CH3+@var
IF (@CH3 <>''  ) SET @CH3= LEFT(@CH3, LEN(@CH3)-1)
fetch next from cur into @var
end
 
RETURN @CH3
 
END