[OPTIMISATION]fonction de concaténation

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:

---

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:

---

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.

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:

---

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

---

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 8O

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:

---

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

---

Merci serge ;)

CG_ECR_ID est-il indexé ?

A +

Citation:

---

Envoyé par SQLpro

CG_ECR_ID est-il indexé ?

A +

---

non, faut il le faire ?

en créant l'index, la fonction avec le curseur passe à 3s (je dis bien 3 secondes) 8O

la fonction sans le curseur (1er message) dure 2 minutes !