Discussion :

[Takuan75]

Bonjour à tous,

Je travaille sur l'intégration d'un fichier EDI et je souhaiterai optimiser le traitement auquel j'ai abouti et qui ne me donc pas satisfaction en terme de temps d'exécution.
Je travaille sur SQL Server 2008 R2 hébergé sur un serveur Azure et donc avec des performance assez moyenne.

Le fichier d'entrée est un fichier texte qui présente des informations sur un colis. Sur la ligne 1 nous allons trouver l'identifiant du colis, sur la ligne 2 un code d'événement, sur la ligne 3 la date de l'événement et se schéma se répète : ligne 4 identifiant du colis, ligne 5 événement, ligne 6 date etc. Il peut y avoir plusieurs milliers de ligne dans chaque fichier

J'ai commencé par faire un BCP du fichier dans une table temporaire puis j'ai extrait l'information de chaque ligne ce qui donne une table qui ressemble à ceci :

Ligne du fichier	Identifiant Colis	Evénement	Date
CNIxxxxxNNNNNxxxx	NNNNN
STS20xxxxxxxxxxxxLIVxxxxxxCFM		LIVCFM
DTMxxxxxxxxxx20140801xxxxxxxxx1002			01/08/2014 10:02

Cette partie du traitement est relativement légère. Ce qui pose problème c'est de remettre toutes les informations relative à un colis sur une seule ligne et je n'ai pour le moment pas trouvé d'autre moyen qu'un curseur qui va générer autant de requête qu'il y a de ligne dans la table temporaire.

Si quelqu'un a une idée géniale, je suis preneur !
Merci

Fred

[GeekMokona]

Effectivement les curseurs c'est a éviter (voir bannir) , il sont généralement remplaçable par des CTE , Mais pour que l'on puisse te répondre au mieux ...

Quel est l'info dans ta table permettant de relier les lignes d'un colis entre elles ?

Peux-tu donner le code a optimiser ainsi que la définition de ta table (DDL) ?

[Takuan75]

Je suis d'accord avec toi ; quand on peut éviter les curseur ... mais là je n'ai pas du tout réussi à trouver une solution d'où mon appel à idée.

Il n'y a pas de champs qui permette de regrouper les différentes lignes liées à un colis, c'est bien la le problème. La seule solution est de prendre les lignes dans l'ordre : ligne 1, 2 et 3 concerne le colis 1, les lignes 4, 5 et 6, le colis 2 etc.

Ce n'ai pas vraiment une optimisation du code existant que je cherche, mais une idée qui me permettrait de me passer du curseur.

[aieeeuuuuu]

Bonjour,

Dans une table, il n'y a pas de ligne 1, ligne 2, ligne 3. Il y a juste des lignes, qui n'ont aucun ordre !

L'idéal serait sûrement d'utiliser un langage de programmation pour pivoter votre fichier source avant son insertion en base.
A défaut, au moins numéroter les lignes afin de leur attribuer le fameux ordre manquant dans la base.

[Takuan75]

Bonsoir,

Les ligne peuvent être ordonnées en fonction d'un champ (j'ai ajouté un champ identity) donc le problème n'est pas vraiment là.

Modifier le fichier avant le BCP est le plan B ...

[GeekMokona]

Bonjour,

Si tu es libre sur La structure de ta table temporaire contenant :

Ligne du fichier Identifiant Colis Evénement Date
CNIxxxxxNNNNNxxxx NNNNN
STS20xxxxxxxxxxxxLIVxxxxxxCFM LIVCFM
DTMxxxxxxxxxx20140801xxxxxxxxx1002 01/08/2014 10:02

le plus judicieux est d'utilisé PIVOT http://technet.microsoft.com/en-us/l...=sql.105).aspx

Pour ce faire je te conseil de modifier la structure de ta table comme suit

Code :

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

1
2
3
4
5
6
create table ColisAvantPivot
(
IdLignesColis int,
TypeValeur varchar(50),
Valeur varchar(50)
)

Donc après ton BCP et ton extraction de valeur tu aura dans cette table :

Code :

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

1
2
3
4
5
6
7
8
9
 
IdLignesColis |TypeValeur	        |Valeur
-----------------------------------------------
0	            |IdentifiantColis	|NNNNN
1	            |Evénement	        |LIVCFM
2	            |Date	                |01/08/2014 10:02
3	            |IdentifiantColis	|AAAAA
4	            |Evénement	        |BADC
5	            |Date	                |02/08/2014 11:05

Puis tu Pivote ta table Via

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select 
   IDColis
   ,[IdentifiantColis]
   ,[Evénement]
   ,[Date]
from 
        (Select (IDLignesColis-1)/3 as IDColis  , TypeValeur ,Valeur From dbo.colisAvantPivot) SourceTable
Pivot
    (Min(Valeur)
     For TypeValeur In ([IdentifiantColis],[Evénement],[Date]))as pvt

Résultat

Code :

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

1
2
3
4
5
 
IDColis	| IdentifiantColis  	|Evénement	|Date
--------------------------------------------------------------
0	        |NNNNN	                |LIVCFM	         |01/08/2014 10:02
1	        |AAAAA	                |BADC	         |02/08/2014 11:05

Ps L'ID Colis qui en résulte n'est pas a prendre comme PK , c'est juste un identifiant temporaire ....

[aieeeuuuuu]

Les ligne peuvent être ordonnées en fonction d'un champ (j'ai ajouté un champ identity) donc le problème n'est pas vraiment là.

C'est une supposition optimiste et dangereuse...
Que se passera-t-il par exemple si le moteur décide de paralléliser l'import du fichier ?

Il ne me semble pas que bcp garantisse que l'ordre d'insertion des lignes dans la tables corresponde à leur ordre dans le fichier source. Mais effectivement, avec des petits fichiers et un peu de chance, ça devrait fonctionner dans la majorité des cas (typiquement, en dev), et échouer dans de très rares cas (typiquement, après la mise en prod, un week end, la nuit ).

Je vous en courage a envisager plus sérieusement votre plan B !

[GeekMokona]

C'est une supposition optimiste et dangereuse...
Que se passera-t-il par exemple si le moteur décide de paralléliser l'import du fichier ?

Il ne me semble pas que bcp garantisse que l'ordre d'insertion des lignes dans la tables corresponde à leur ordre dans le fichier source. Mais effectivement, avec des petits fichiers et un peu de chance, ça devrait fonctionner dans la majorité des cas (typiquement, en dev), et échouer dans de très rares cas (typiquement, après la mise en prod, un week end, la nuit ).

Je vous en courage a envisager plus sérieusement votre plan B !

Je suis complètement d'accord ! Donc Takuan75 au minimum insert un numéro de ligne ( fiable ) dans ton plan B avant le BCP

[Takuan75]

effectivement l'ordre n'est pas garanti à 100 % ... je vais donc me rabattre sur le plan B.

Dommage car la solution avec le pivot était assez séduisante.

Merci à tous pour vos contributions !

[SQLpro]

C'est une supposition optimiste et dangereuse...
Que se passera-t-il par exemple si le moteur décide de paralléliser l'import du fichier ?

Il ne me semble pas que bcp garantisse que l'ordre d'insertion des lignes dans la tables corresponde à leur ordre dans le fichier source. Mais effectivement, avec des petits fichiers et un peu de chance, ça devrait fonctionner dans la majorité des cas (typiquement, en dev), et échouer dans de très rares cas (typiquement, après la mise en prod, un week end, la nuit ).

Je vous en courage a envisager plus sérieusement votre plan B !

BCP comme BULK INSERT permet de définir un ORDER BY pour l'insertion des lignes dans la table. En revanche, cela dégrade les performances.
Pour augmenter les performances, vous devez aussi donner le nombre approximatif de lignes à traiter pour la commande BCP via le paramètre BATCHSIZE (de mémoire). Cela aide l'optimiseur à fournir un plan de requête optimisé. En l'absence de ce paramètre, l'estimation est systématiquement de 1000 lignes !

A +

[aieeeuuuuu]

BCP comme BULK INSERT permet de définir un ORDER BY pour l'insertion des lignes dans la table. En revanche, cela dégrade les performances.

Il me semblait que cela permettait plutôt de spécifier au moteur que les lignes du fichier étaient déjà triées, et que cette option permettait donc d'améliorer les performances lorsque par exemple l'ordre des lignes du fichier correspondait à l'index cluster...
D'ailleurs en l’occurrence, je ne vois pas comment utiliser cette option : ORDER BY quoi ?, il n'y a qu'une seule colonne, et il n'est surtout pas question de trier dessus.
Donc je ne vois pas comment garantir l'ordre d'insertion des lignes dans ce cas.

[mikedavem]

Il me semblait que cela permettait plutôt de spécifier au moteur que les lignes du fichier étaient déjà triées, et que cette option permettait donc d'améliorer les performances lorsque par exemple l'ordre des lignes du fichier correspondait à l'index cluster...

Exact. Les instructions BCP et BULK INSERT ne garantissent pas l'ordre d'insertion dans les tables concernées.

Exemple basé sur celui de Aaron avec un fichier qui contient les données suivantes :

Code :

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

1
2
3
1,2,1
2,1,2
1,2,3

Fichier format :

Code :

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

1
2
3
4
5
8.0
3
1    SQLCHAR    0    12    ","    2    A    ""
2    SQLCHAR    0    12    ","    3    B    ""
3    SQLCHAR    0    12    "\r\n"    4    C    ""

3 tables A, B et C avec

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 TABLE dbo.A 
( 
   id INT IDENTITY(1,1), 
   A INT, 
   B INT, 
   C INT 
); 
GO 
CREATE CLUSTERED INDEX A ON dbo.A(A); 
GO 
 
CREATE TABLE dbo.B 
( 
   id INT IDENTITY(1,1), 
   A INT, 
   B INT, 
   C INT 
); 
GO 
CREATE CLUSTERED INDEX B ON dbo.B(B); 
GO 
 
CREATE TABLE dbo.C 
( 
   id INT IDENTITY(1,1), 
   A INT, 
   B INT, 
   C INT 
); 
GO 
CREATE CLUSTERED INDEX C ON dbo.C(C); 
GO

Une instruction bulk insert et ORDER :

Code :

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

1
2
3
4
5
6
7
8
9
BULK INSERT dbo.A FROM 'E:\SQLSERVER\MSSQLSERVER\BACKUP\blat.txt' 
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'E:\SQLSERVER\MSSQLSERVER\BACKUP\test.fmt', ORDER ( C ASC )); 
GO 
BULK INSERT dbo.B FROM 'E:\SQLSERVER\MSSQLSERVER\BACKUP\blat.txt' 
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'E:\SQLSERVER\MSSQLSERVER\BACKUP\test.fmt', ORDER ( C ASC )); 
GO 
BULK INSERT dbo.C FROM 'E:\SQLSERVER\MSSQLSERVER\BACKUP\blat.txt' 
WITH (ROWTERMINATOR = '\r\n', FORMATFILE = 'E:\SQLSERVER\MSSQLSERVER\BACKUP\test.fmt', ORDER ( C ASC )); 
GO

On voit que seule la table C suit l'ordre du fichier du fait de l'index cluster posé sur la colonne C et qui suit l'ordre pour la colonne C. Le paramètre ORDER ne garantit pas forcément l'ordre d'insertion dans la table tel qu'il apparaît dans le fichier.



++

[Sennad]

Salut !

Et si à chaque fois que tu insères une ligne dans ta db, tu insères aussi dans une colonne en plus, un numéro, numéro qui sera incrémenté à chaque nouvel enregistrement ?
Du coup là on serrait sur d'avoir les enregistrements dans l'ordre non ? Plus avec l’ID mais avec cette colonne.

[aieeeuuuuu]

Le problème est justement que les lignes ne sont pas ajoutées les unes après les autres, mais toutes en même temps, par le biais d'un utilitaire d'import en bloc...

[Sennad]

Ah pardon, je pensai qu'il avait codé quelque chose qui lis la ligne, l'insère dans la DB, puis lis la suivante et ainsi de suite..
Désolé alors...