Discussion :

[m-mas]

Bonjour,

SQL 2000

je charge par bcp une table de 210000 lignes, le chargement ne prend que 1 minutes !
je veux faire une mise à jour sur un des champs de la table (une mise à jour de 210000 ligne), en fait la mise à jour est de concaténer 2 champs et de les mettres dans un troisième champs :

Code :

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

1
2
3
4
5
6
7
 
UPDATE T_ECRITURE_COMPTA
SET T_ECRITURE_COMPTA.SCHEMA_COMPTABLE = A.MAJ
FROM T_ECRITURE_COMPTA,
(SELECT distinct ECRITURE_NUM, dbo.FN_GENERER_SCHEMA_COMPTABLE(ECRITURE_NUM) As MAJ
from T_ECRITURE_COMPTA) A
WHERE T_ECRITURE_COMPTA.ECRITURE_NUM = A.ECRITURE_NUM

ma fonction FN_GENERER_SCHEMA_COMPTABLE consiste en :

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
 
CREATE FUNCTION FN_GENERER_SCHEMA_COMPTABLE (@ECR_NUM INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @MAJ VARCHAR(8000)
 
SET @MAJ = ''
 
SELECT @MAJ = RACINE_GENE + ' ' +  SENS_ECRITURE + ' - ' + @MAJ
FROM   T_ECRITURE_COMPTA
WHERE  ECRITURE_NUM = @ECR_NUM 
 
GROUP BY  RACINE_GENE, SENS_ECRITURE , ECRITURE_NUM
 
 
RETURN Left(@MAJ, Len(@MAJ)-1)
 
END

la table T_ECRITURE_COMPTA contient plus de 50 colonnes (ce n'est pas moi qui l'a définit)
comme vous le voyer, le champs ne peut pas être un champs calculé pour qu'il soit directement alimenté lors de l'import bcp !!

cette requête de MAJ (procédure stocké) me prend plus qu'une heure qui est trop long pour mon utilisateur

j'ai essayé de mettre un index non ordonné sur RACINE_GENE et un autre index non ordonné sur SENS_ECRITURE mais cela ne change absolument rien, j'ai toujours mon heure de traitement !

Voila, avez vous des idées d'amélioration du temps de traitements notament la création d'index ...

En vous remerciant !

[rudib]

Bonjour,

en effet un index n'a sans doute pas beaucoup d'effet, le principal problème vient du passage dans la fonction, pour générer une chaîne récursive, si j'ai bien compris. Chaque ligne doit être processée, avec un passage dans la fonction. Vous faites faire à SQL Server une sorte de code procédural pour lequel il n'est pas particulièrement conçu.
N'avez-vous pas un moyen de ne pas dénormaliser ainsi, par exemple en laissant cette concaténation pour le SELECT, à l'appel des lignes, ou dans une vue, si vous retournez peu de résultats à la fois lorsque vous appelez les données pour consultation (en limitant le nombre de lignes touchées par la fonction) ?

[m-mas]

tu as tout compris !!

A titre d'indication, sur 1000 lignes de ma table, la mise à jour met 30 s

Et en fait le champs que je met à jour est celui sur lequel je vais faire un certain nombre de requête (de sélection) mais je vais comme même creuser cette idée !!

Merci.

[rudib]

C'est dommage de faire des sélections sur une colonne concaténée, j'espère que tu ne fais pas de substrings.

Entre-temps ça m'a trotté dans la tête. Je me dis qu'en faisant une sous-requête, tu dois probablement générer une table temporaire. Regarde ce que te dit le plan d'exécution. Peut-être peux-tu déplacer l'appel de ta fonction dans le corps de l'UPDATE, comme ceci :

Code :

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

1
2
3
 
UPDATE dbo.T_ECRITURE_COMPTA
SET SCHEMA_COMPTABLE = dbo.FN_GENERER_SCHEMA_COMPTABLE(ECRITURE_NUM)

pour voir ce que ça donne. Ca m'étonnerait cependant qu'il en sorte quelque chose de miraculeux, voire même d'intéressant.

Dans la fonction, également, tu pourrais mettre un table hint : WITH (READUNCOMMITTED), pour éviter de locker ta recherche dans la même table que tu traites dans l'UPDATE.

Ce ne sont que de petits pis-aller, l'idéal étant de ne pas faire l'UPDATE et de se débrouiller pour faire des SELECT sur la table telle qu'elle est. Tu peux toujours poster les SELECT, peut-être que les gens ici auront des inspirations à leur sujet.

[rudib]

Une autre idée qui me vient, toujours en y repensant : un index composite qui "couvre" la requête de ta fonction, cad qui comporte les trois colonnes ECRITURE_NUM, RACINE_GENE, SENS_ECRITURE. ECRITURE_NUM en premier (à moins qu'elle soit index ordonné déjà, auquel cas pas besoin de la mettre. J'imagine que la colonne ECRITURE_NUM est la plus sélective (celle qui contient le moins de doublons).

[m-mas]

Une autre idée qui me vient, toujours en y repensant : un index composite qui "couvre" la requête de ta fonction, cad qui comporte les trois colonnes ECRITURE_NUM, RACINE_GENE, SENS_ECRITURE. ECRITURE_NUM en premier (à moins qu'elle soit index ordonné déjà, auquel cas pas besoin de la mettre. J'imagine que la colonne ECRITURE_NUM est la plus sélective (celle qui contient le moins de doublons).

tout a fait !! l'index composite n'a pas amélioré grande chose !!

je vais creuser ta première solution : je pense que ma fonction doit me retourner une table avec le ECRITURE_NUM et le résultat de la concaténation, ensuite je fait l'update en faisant la jointure entre la table retourné par ma fonction et ma table réelle

En tous cas merci pour les idées, je retourne bosser !!

[m-mas]

ma fonction est devenu :

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
 
CREATE FUNCTION FN_GENERER_SCHEMA_COMPTABLE (@ECR_NUM numeric)
 
RETURNS @T table (SchemaCompta varchar(8000), EcrId numeric)
AS
BEGIN
 
DECLARE @Ch3 VARCHAR(8000)
 
SET @Ch3 = ''
 
SELECT @Ch3 = RACINE_GENE + ' ' +  SENS + ' - ' + @Ch3
FROM   T_ECRITURE_COMPTA
WHERE  ECRITURE_NUM = @ECR_NUM 
 
INSERT INTO @T  VALUES (Left(@Ch3, Len(@Ch3)-1), @ECR_NUM)
 
RETURN 
 
END

l'appel à ma fonction me génère une erreur sur un nom de colonne (qui est passé en paramètre)

Code :

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

1
2
3
4
 
SELECT distinct ECRITURE_NUM, SchemaCompta
from T_ECRITURE_COMPTA,
dbo.FN_GENERER_SCHEMA_COMPTABLE(ECRITURE_NUM)

'ECRITURE_NUM' n'est pas une option OPTIMIZER LOCK HINTS reconnue.

ça m'énerve grave !!!

bon je vais me reposer, la nuit porte conseil !

[rudib]

désolé, je me suis mal fait comprendre, je voulais dire que si tu appelais ta fonction d'origine comme cela :

Code :

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

1
2
3
 
UPDATE dbo.T_ECRITURE_COMPTA
SET SCHEMA_COMPTABLE = dbo.FN_GENERER_SCHEMA_COMPTABLE(ECRITURE_NUM)

l'optimiseur ne générerai par de table temporaire. Mais ce n'est probablement pas une bonne chose, surtout si tu as beaucoup de lignes qui ont la même ECRITURE_NUM. Je pense que ça ne sert à rien de chercher de ce côté si tu veux améliorer très sérieusement les performances. Plus dans la structure de tes tables, et de tes SELECT.