SPLIT SQL SANS TABLE TEMPORAIRE

Commentaire de tmcuh le 25/02/2009 12:54:54

Pourrais-tu donner un exemple de données, car je vois pas à quoi ça sert...

Commentaire de TheOnlyMaX le 25/02/2009 14:13:53

Dans le principe, l'instruction SELECT générée me permet de faire un "INSERT INTO tableFinale [Select généré]"

J'ai adapté rapidement ma source pour la mettre à dispo ici, mais ça mérite encore quelques modifs et un exemple. Je m'en occupe dans la semaine, merci.

Commentaire de tmcuh le 26/02/2009 16:03:11

L'idée est intéressante, mais cela aurait été beaucoup plus puissant si tu recrée ta table final dynamiquement. En effet, dès le départ compter le nombre de ; pour connaitre le nombre de colonnes.
Mais pourquoi faire les étapes : 1) importer fichier csv dans sql    2) découper l'information dans sql.
Pourquoi ne pas avoir fait l'opération depuis ton code c#, qui est quand même assez puissant et rapide pour permettre ce type de man½uvre (découpe de l'information) avant de l'importer dans SQL.

Cordialement

Commentaire de Malkuth le 26/02/2009 18:37:27

Je rejoins tmcuh, je pense que la método n'est pas optimal(mais on as pas forcerment toute les infos...)

a lors actuel tu fais :
BulkInsert d'un CSV en table intermédiaire
Nettoyage/insertion final

Clairement SQL n'est pas le meilleur choix pour le nétoyage(qq bench pourrons facilement le prouver).

je pense que tu aurais plutot du utilisé la métho suivante :
Lecture/Nétoyage du CSV (netoyage a la volée)
bulkinsert dans la table final.

tu dispose de la classe [System.Data.SqlClient.SqlBulkCopy] qui permet de commander facilement l'insertion Bulk directement depuis C#.

D'abord ca me semble plus logique et surtout, Ca permet de déporter la charge de nettoyage sur une machine différente (voir plusieurs...)

Commentaire de Malkuth le 26/02/2009 22:18:48

Lecture/ nétoyage à la volé ca veut dire en gros tu lis une ligne du fichier, tu la notoie aussitôt et tu la transmet au serveur.

donc, lire une à une les ligne d'un fichier texte ca donne un truc du genre :

        public IEnumerable<string> lireLignes(string cheminfichier)
        {
            string ligne;
            using (TextReader txtstrm = File.OpenText(cheminfichier))
                while ((ligne = txtstrm.ReadLine()) != null)
                    yield return ligne;
        }

ensuite on doit les néttoyé on va séparé ca en 2 phases : l'extraction des données puis leur vérification pour des d'optimisation certain les 2 étape peuvent être plus ou moins mélé, au passage on crée une petite structure légére pour conservé les donnée nétoyé (et typé d'ailleurs...)


        public struct donnéestructurées
        {
            public string col_nom;
            public int col_age;
        }


        public IEnumerable<donnéestructurées> nettoyerlignes(IEnumerable<string> lignesbrute)
        {
            foreach (string ligne in lignesbrute)
            {
                donnéestructurées lignepropre = new donnéestructurées();

                //extraction des données
                string[] donnesintermediaire = ligne.Split(';');
                if (donnesintermediaire.Length != 2) continue;// si ca va pas on reprend le traitement a la ligne suivante
                lignepropre.col_nom = donnesintermediaire[0];
                int.TryParse(donnesintermediaire[1],out lignepropre.col_age);

                //Vérification de la bonne conformitée des données
                if (lignepropre.col_age < 10
                || lignepropre.col_age > 20
                || string.IsNullOrEmpty(lignepropre.col_nom))
                    // si ca va pas on reprend le traitement a la ligne suivante
                    continue;

                //Si tout va bien on envoie la ligne propre et on continue ;)
                yield return lignepropre;
            }
        }

maintenant, on a donc moyen des récupéré des donnée propres juste en faisant:

nettoyerlignes(lireLignes("monfic.csv"));

(je ne sais pas ta connaissance éxacte de C# mais si tu fais une exécution pas à pas de ce code tu véras que contrairement au aparance, il lit une ligne, la nétoi, lit la suivante, la nétoi etc, si ce mécanisme t'est inconnue renseigne toi sur le mot clef yield, si connais déjà tout ca, oublie mon commentaire ;) )


alors maintenant on as des donnée propre, il faut les envoyés au serveur, solution un, une suite d'insert, a oublier dans ton cas on est bien d'accord, Solution 2, envoyé les donnée sous forme XML trés bien pour un volume moyen mais pas assez rapide pour 10millions de lignes ^^ solution 3 le Bulk insert (ouai on est d'accord ^^) et pour le bulkinsert y'a une classe pas mal foutu en .NET : System.Data.SqlClient.SqlBulkCopy

ca permet d'accéder a la plupart des fonctionnalité  de Bulkinsert directement depuis le code(donc sans avoir besiion de réécrire les données dans un fichier.

voici un exemple


        public void Insererligne(IEnumerable<donnéestructurées> données)
        {
            using(SqlConnection con = new SqlConnection("maconnexion"))
            {
                using (SqlBulkCopy cpy = new SqlBulkCopy(con))
                {
                    cpy.BulkCopyTimeout = 100000;
                    cpy.BatchSize = 100000;
                    cpy.DestinationTableName = "matablefinal";
                    cpy.ColumnMappings.Add("col_nom", "Nom");
                    cpy.ColumnMappings.Add("col_age", "Age");

                    DataTable donnéespretes = new DataTable();

                    DataColumn cNom = new DataColumn("col_nom", typeof(string));
                    donnéespretes.Columns.Add(cNom);

                    DataColumn cAge = new DataColumn("col_age", typeof(int));
                    donnéespretes.Columns.Add(cAge);
                    int nblignes = 0;
                    foreach (donnéestructurées donné in données)
                    {
                        DataRow row = donnéespretes.NewRow();
                        row[cNom] = donné.col_nom;
                        row[cAge] = donné.col_nom;
                        donnéespretes.Rows.Add(row);

                        if((nblignes%100000)==0)
                            cpy.WriteToServer(donnéespretes);
                    }
                }
            }
        }

donc maintenant tu fais : Insererligne(nettoyerlignes(lireLignes("monfic.csv")));
et ca roule.

Le code es pas optimiser pour 2 sous mais même en l'état il doit être bien plus performant.

Commentaire de tmcuh le 27/02/2009 08:55:04

Très intéressant ton code malkuth. je connaissais la méthode bulkinsert, mais ton optimisation par le yield est intéressante.

Commentaire de TheOnlyMaX le 27/02/2009 12:02:18

Ta solution est extrêmement intéressante Malkuth ! Merci pour ces infos, je ne connaissais ni le yield ni le SqlBulkCopy.
Cependant, je crains que les perfs au niveau de la mémoire vive ne soient moins bonnes ? Sur un serveur d'application, c'est très important ! 100 000 lignes en mémoire, ca commence à faire... Et si on baisse cette valeur ?
-> Pourquoi ne pas faire un benckmark ?

Commentaire de rhwy le 27/02/2009 15:43:58

si tu cherchais au départ un sql split voici un code que j'ai posté il y a quelques temps:

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'fSplit') AND XTYPE IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION fSplit
GO

CREATE FUNCTION fSplit(
    @String NVARCHAR (4000), @Delimiter NVARCHAR (10)
)
RETURNS @T TABLE ([Value] NVARCHAR(4000))
BEGIN
    DECLARE @NEXTSTRING NVARCHAR(4000)
    DECLARE @POS INT,@DELIM_SIZE INT
    DECLARE @NEXTPOS INT

    SELECT
        @NEXTSTRING = '',
        @String = @String + @Delimiter,
        @DELIM_SIZE = LEN(@Delimiter)

    SET @POS = CHARINDEX(@Delimiter,@String)
    SET @NEXTPOS = 1

    WHILE (@POS <>  0)  
    BEGIN
        SET @NEXTSTRING = SUBSTRING(@String,1,@POS - 1)
        INSERT INTO @T ( [VALUE]) VALUES (@NEXTSTRING)
        SET @String = SUBSTRING(@String,@POS +@DELIM_SIZE,LEN(@String))
        SET @NEXTPOS = @POS
        SET @POS  = CHARINDEX(@Delimiter,@String)
    END

RETURN
END
GO

Ce qui pourra s'utiliser directement par:

-- version directe
SELECT * FROM dbo.fSplit('a1--b2--c3','--')
-- avec une jointure
SELECT * FROM MaTable A INNER JOIN dbo.fSplit('a,b,c',',') B ON A.id = B.Value


et si tu as beaucoup de lignes à traiter d'un bloc, je te conseillerai meme de faire un cross apply et de tout traiter directement en sql, voici un exemple:


IF EXISTS (SELECT * FROM sys.tables WHERE name = 'splitData')
DROP TABLE splitData
GO

CREATE TABLE splitData (id INT IDENTITY(1,1), data NVARCHAR(2000))
GO

INSERT INTO splitData (data) VALUES (CAST(NEWID() AS NVARCHAR(MAX)))
GO 5000

SELECT A.id,B.Value
FROM splitData A
CROSS APPLY dbo.fSplit(A.data,'-') B

Commentaire de Malkuth le 27/02/2009 16:59:28

Le cross aplly est effectivement trés pratique mais je doute vraiment que les performances soient au rendez vous.

sur 10 000 000 de lignes traitée la charge de travail deviens vraiment colosale et je crois que chaque partie d'un produit doit être utilisé pour ce qu'il sait le mieux faire, et pour moi C# est bien plus performant en transformation linéaire de donnée que SQL Serveur.

Sle projet avais été pure SQL pourquoi pas (enfin je pense que TheOnlyMaX à une solution plus performante) mais étant donnée qu'il nous présente un code C# j'en conclu qu'il a donc du C# dans son projet donc autant l'utiliser là ou il est mieux indiquer...

@TheOnlyMaX : serait'il possible que tu me passe un de tes fichiers d'importation type? (un Fake bien entendu) avec la table dans laquelle les données sont placé au final(déclaration SQL) afin de consolidé mon code pour un cas réaliste? pour infos je suis à un temps de 11 sec de traitement pour lecture/néttoyage/vérification métier sur un fichier de 10 000 000 de ligne et 3 colones (reste a traiter le bulkinsert en lui même) et ce code est trés loin d'un code optimisé.

Commentaire de TheOnlyMaX le 10/03/2009 16:45:59

En ajoutant un # devant le nom de la table temporaire, elle devient une "vraie" table temporaire pour SQL Serveur, donc elle reste uniquement en mémoire, et on améliore considérablement les perfs (jai mis à jour le zip). Donc au final, on se retrouve avec le même mode de fonctionnement qu'avec ton code Malkuth (mise en mémoire des lignes, nettoyage, écriture des lignes propres).

@Malkuth : J'ai testé la classe SqlBulkCopy en utilisant une connexion OleDb au fichier. L'implémentation est bien plus simple. En gros, ça donne :

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(mySqlConnection, SqlBulkCopyOptions.TableLock, mySqlTransaction);
sqlBulkCopy.DestinationTableName = "TABLE_FINALE";
sqlBulkCopy.BulkCopyTimeout = 10*60;

string folder = @"D:\test";
string connectionStringTextFile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folder + ";Extended Properties='Text;'";
System.Data.OleDb.OleDbConnection connectionTextFile = new System.Data.OleDb.OleDbConnection(connectionStringTextFile);
connectionTextFile.Open();
string fileName = "fichier.txt"; // Attention, toutes les extensions ne sont pas prises en compte !
int nbLignesHeader = 3;
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + fileName, connectionTextFile);
DataSet dsFile = new DataSet(fileName);
da.Fill(dsFile, fileName);
DataTable dtFile = dsFile.Tables[fileName];

int idxLigne=0;
foreach (DataRow row in dtFile.Rows)
{
// Ignorer les lignes de Header
if (idxLigne++ < nbLignesHeader) continue;

ligne = row[0].ToString(); // ligne entière

// Splitter la ligne
tabLigne = ligne.Split(';');

for (int i=0; i<tabLigne.Length; i++)
{
valeur = tabLigne[i];
valeur = valeur.ToString().Trim();
rowOut[i] = valeur;
}
dtOut.Rows.Add(rowOut);

if (idxLigne % 10000 == 0)
{
sqlBulkCopy.WriteToServer(dtOut);
dtOut.Rows.Clear();
}
}
if (dtOut.Rows.Count > 0)
{
sqlBulkCopy.WriteToServer(dtOut);
dtOut.Rows.Clear();
}
connectionTextFile.Close();



-> Maintenant, si on imagine une procédure stockée classique qui splitte les données en utilisant une vraie table temporaire, est-on encore gagnant avec ma méthode ?

Commentaire de TheOnlyMaX le 11/03/2009 15:14:14

Oops, le code ci-dessus n'utilise pas la classe SqlBulkCopy bien sûr, mais juste une connexion OleDb au fichier !