Importation de données massives [Résolu]

Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
- - Dernière réponse : Arthenius
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
- 4 mars 2009 à 08:53
Hello à tous

je suis entrain de bosser sur des importations de données de fichier plat vers notre serveur SQL.

<li>Le principe :
</li>Je récupère un fichier Txt que j'importe dans une table temporaire TMP_maTable.  = ===>BULKINSERT
Puis je creer une procedure stockee qui va parcourir tout les enregistrement de cette table TMP_maTable et faire des INSERTS / UPDATE dans mes tables destination, en récupérant au passage les différentes clés étrangères dont ma table de destination pourrait avoir besoin....en faisant des mise à jour ou des insert dans d'autre tables liées, etc....
Il arrive sur certaine table qu'un insert declenche l'execution d'un trigger, et il faut que cela soit fait.

ci dessous un exemple de SP que je genere
<hr size ="2" width="100%" />SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE [dbo].[IMPORT_ma_table_destination]
GO
CREATE PROCEDURE [dbo].[IMPORT_ma_table_destination]
AS
BEGIN
    SET NOCOUNT ON
    --Delete de la table
    DELETE FROM ma_table_destination;

    DECLARE @mes_variables_TMP varchar(50), .........;
    DECLARE @mes_variables_destination uniqueidentifier, ....

    DECLARE Cur_ CURSOR FOR
        SELECT * FROM TMP_ma_table_temp;
    Open Cur_
    -- Boucle sur le curseur et exécution des requetes
    FETCH NEXT FROM Cur_ INTO @mes_variables_TMP, .....;
    WHILE@@Fetch_Status = 0
    BEGIN
        -- traitement pour remplir les variables à insérer !
        SET @var_dest1 = newid();
        SET @var_dest2 = NULL;        SELECT @var_dest2 FK FROM T_ma_table_foreignkey WHERE Moncode @une_de_mes_variable_tmp;
--        .........
        INSERT INTO ma_table_destination
        (
            mes_champs,.......
        )
        VALUES
        (
            @mes_variables_destination .....
        );

       IF @Une_des_valeurs = 'TOTO'
       BEGIN
          INSERT INTO une_autre_table values (.........................);
       END
         FETCH NEXT FROM Cur_ INTO @mes_variables_TMP, .....;
    END
    -- Fermeture du curseur et libération des ressources
    Close Cur_
    Deallocate Cur_
END

Mon problème :
</li>C'est long sur des fichiers de plusieurs millions de ligne c'est tres long....(environ 20h...), alors que je tape a chaque fois sur des zones indexe...
je me suis dis si j'essayais SSIS, seulement voila j'ai reussi a bidouiller quelques trucs, mais je ne suis pas arriver au même resultat que ce que j'etais capable de faire avec ma SP...genre mettre a jour une table etrangere apres l'insert dans ma table finale etc... => je ne maitrise pas l'outil

bref existe-t-il un moyen d'augmenter les perfs de ce genre d'import en gardant mon principe de procedure stockee, la possibilite de faire des insert a droite a gauche, de declencher ou non certain trigger......

<hr />Arthenius
http://blogs.developpeur.org/Arthenius/

"Ce qui ne me tue pas, me rend plus fort..."
Afficher la suite 

Votre réponse

11 réponses

Meilleure réponse
Messages postés
278
Date d'inscription
samedi 22 février 2003
Dernière intervention
24 avril 2013
3
Merci
Un curseur pour faire ca??? je vois pas l'intérrer, le SQL c'est pas fais pour faire de l'itératif, il Faut Réfléchir en therme d'ensemble :

pourquoi une boucle dans laquel tu fais un insert avec au préalable un précalcule alors que tu peux faire un truc du genre

INSERT INTO Final1
( C1,C2,C3)
SELECT 
   CA,
   newID();
   (SELECT truc FROM Bidule WHERE Machin = Source.B)
FROM Source

pour l'insert conditionnel dans ta boucle tu fais

INSERT INTO Final2
( C1,C2,C3)
SELECT 
   CA,
   newID();
   (SELECT truc FROM Bidule WHERE Machin = Source.B)
FROM Source
Where C = N'Toto'

tu vas voir qu'en te métant a réfléchir de cette maniére et en vacuant les reflexe de programation classique (Bouble, itération etc...) les perfs n'ont plus rien a voir!

Dire « Merci » 3

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources a aidé 104 internautes ce mois-ci

Commenter la réponse de cs_Malkuth
Messages postés
278
Date d'inscription
samedi 22 février 2003
Dernière intervention
24 avril 2013
3
Merci
Niveau perf, aucun doute à avoir, SQL serveur optimiseras bien mieux un insert massif qu'une sucsession de millions d'insert (fais un tit code simple avec et sans urseur pour t'en convaincre), c'est même a cette condition que SQL prend son vrai sens. D'une maniére général les curseurs sont a proscrire, les cas ou ils s'avére nésséssaire sont trés rares et le plus souvent ils sont signe d'un défaut de "vision", le probléme c'est qu'un programmeur "classique" se dirigera facilement vers cette solution car elle lui est familiére en programation SQL c'est une erreur trés courrante d'optimisation.

Concernant ton probléme de table lié :
admetons les tables suivantes :

ImportSource(Nom (nvarchar), Catégorie (nvarchar))
Catégories(ID (int auto), Nom (nvarchar))
Personnels(ID (int auto), Nom(nvarchar), ID_Cat (int))

Tu commence par traiter l'ajout des nouvelle Catégorie :

INSERT INTO Catégories (Nom)
SELECT DISTINCT Catégorie FROM ImportSource 
WHERE Catégorie NOT IN(SELECT Nom From Catégories)
   
La clause where permet de ne pas ajouter les catégories déjà existante, le Distinct évite d'ajouter une nouvelle catégorie qui aparaitrait 2 fois dans la ImportSource.

Ensuite tu ajoute les Personnels :

INSERT INTO Personnels (Nom, ID_Cat)
SELECT ImportSource.Nom, Catégories.ID
FROM ImportSource.
INNER JOIN Catégories
   ON ImportSource.Catégorie = Catégorie.Nom

Grace à une jointure, il est maintenant aisé de reconstruire les dépandances ^^

Dans le cas présent, un Index sur Catégories.Nom seras trés profitable pour la rapiditée des requettes. les INDEX sont LE facteur d'optimisation Premier d'une base de donnée aprés la conception des requettes elles même.

Dire « Merci » 3

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources a aidé 104 internautes ce mois-ci

Commenter la réponse de cs_Malkuth
Messages postés
1137
Date d'inscription
lundi 17 novembre 2003
Dernière intervention
23 janvier 2016
0
Merci
Salut,

Heu, ça t'arrive souvent le : 'sur des fichiers de plusieurs millions de ligne' ?

Sinon "Integration Services" est un bon outil pour ce genre de chose, on peut tout faire avec, mais au niveau des perfs ça changera pas grand chose.
La seule solution est d'améliorer ta procédure par tous les moyens,
si ton curseur est LOCAL et FORWARD_ONLY, autant utiliser une boucle WHILE ou revoir la méthode du ligne par ligne et essayer dans la mesure du possible de penser bloc.
Mais bon, je pense que tu le sais déjà...
Commenter la réponse de yann_lo_san
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
ben plusieurs fichiers a plus de 4 millions
bon je craignais ce genre de reponse

ben on va tacher d'optimiser

merci a toi

<hr />Arthenius
http://blogs.developpeur.org/Arthenius/

"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius
Messages postés
39
Date d'inscription
mardi 21 décembre 2004
Dernière intervention
15 février 2010
0
Merci
Oh que si SSIS sera plus rapide, transformation massive sur un dataset en cache plutôt qu'une lecture ligne par ligne via un curseur.

Sincèrement SSIS. De plus, certains éléments via du VB.Net peuvent être bcp plus rapide que via le moteur SQL.

Bien utilisé, il résoudra bcp de problème.
Commenter la réponse de hymnuade
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
tu pense donc qu'en faisdant un programme plutot qu'une sp cela pourrait etre plus rapide ??

a tu de bon lien pour ssis ??

merci de ta reponse

<hr />Arthenius
"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius
Messages postés
39
Date d'inscription
mardi 21 décembre 2004
Dernière intervention
15 février 2010
0
Merci
Le msdn bien sûr :-)

Sinon, un très bon bouquin "Professional SQL server 2005 Integration Services" trouvable au format chm.
Il contient à la fin une étude de cas très intéressante.
Un programme plus rapide qu'une sp ... sais pas, mais plus rapide qu'un curseur, je pense sincèrement que oui.

Sinon, quelques remarques qui s'appliquent à SSIS au SQL ou au 2 :
1. Tu fais un DELETE de ta table au début sans condition. Donc, tu peux utiliser un TRUNCATE peut-être, plus rapide, remise à zéro du compteur auto s'il y a, et surtout, selon ton mode de log, les DELETE sont très longs à inscrire alors qu'un TRUNCATE est une opé unique.
2. Dans ton exemple, je crois avoir vu que tu récupérais ligne par ligne les valeurs des id des FK en fonction de leur valeur. En SSIS, tu pourras massiver tout ça via un merge ou un lookup ou tout autre selon ton approche. Cad, fusionner 2 dataset; 1 équivalent à ta table temporaire, l'autre à la table de référence, les joindre via la clé de valeur de texte (La, y'a pas le choix à première vue) et paramétrer une sortie avec la clé récupérée uniquement.
3. Tu fais un INSERT INTO ligne par ligne ... très très long sans compter encore une fois le mode de log. Tu dois avoir un LDF énorme. Or via SSIS, tu vas pouvoir constituer et transformer ton dataset selon tes besoins puis tout insérer d'un coup via une destination SQL Server ou OleDb.

Sincèrement, gain de temps non négligeable je pense.
Commenter la réponse de hymnuade
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
je vais mediter sur tout cela
merci en tout cas pour tes pistes

<hr />Arthenius
"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
je vais tester cette méthode, effectivement avec des case when et autre on peu ce passer de la boucle et autre test, reste a voir au niveau perf.

J'ai juste sur certain traitement des insert dans d'autre table (par exmple une table User mise a jour par les utilisateur ayant saisie une commande) ==> il faudra que je parcourt les different user avant et que je fasse les insert en amont de mon import de commande

merci je regarde tout ca et je reposte :)

<hr />Arthenius
"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
oui c'est exactement ce que je comptais faire pour les tables satelittes ;)

<hr />Arthenius
"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius
Messages postés
1183
Date d'inscription
mercredi 21 janvier 2004
Dernière intervention
6 septembre 2011
0
Merci
je reviens donner des news de la méthode insert into select

alors les plus :

ca depote grave :) 1heure pour 1 millions de ligne de table entête commande qui a des jointures dans tout les sens
contre une 20taine d'heure avec la méthode du curseur..., bref y a pas photo

le moins :
seul inconvénient de cette méthode, pas de possibilité de gérer les erreurs de données (a part les voir quand l'insert select claque...), m'enfin on peu le relancer 20 fois pour tester vu le temps que cela met :)

Merci a vous tous , je pense que je vais changer mes imports :)

<hr />Arthenius
"Ce qui ne me tue pas, me rend plus fort..."
Commenter la réponse de Arthenius

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.