SPLIT SQL SANS TABLE TEMPORAIRE
tmcuh
Messages postés
458
Date d'inscription
dimanche 22 décembre 2002
Statut
Membre
Dernière intervention
18 avril 2009
-
25 févr. 2009 à 12:54
TheOnlyMaX Messages postés 37 Date d'inscription lundi 6 octobre 2003 Statut Membre Dernière intervention 26 avril 2010 - 26 avril 2010 à 17:57
TheOnlyMaX Messages postés 37 Date d'inscription lundi 6 octobre 2003 Statut Membre Dernière intervention 26 avril 2010 - 26 avril 2010 à 17:57
Cette discussion concerne un article du site. Pour la consulter dans son contexte d'origine, cliquez sur le lien ci-dessous.
https://codes-sources.commentcamarche.net/source/49355-split-sql-sans-table-temporaire
https://codes-sources.commentcamarche.net/source/49355-split-sql-sans-table-temporaire
26 avril 2010 à 17:57
Le code T-SQL est dispo ici :
http://www.sqlfr.com/codes/SQL-SPLIT-SANS-TABLE-TEMPORAIRE_51689.aspx
23 avril 2010 à 18:54
http://www.developpez.net/forums/d912803/dotnet/general-dotnet/contribuez/sql-split-table-temporaire/#post5161469
11 mars 2009 à 15:14
10 mars 2009 à 18:14
10 mars 2009 à 16:45
@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 ?
27 févr. 2009 à 20:00
http://www.csharpfr.com/codes/BULKINSERT-CSHARP_49377.aspx
27 févr. 2009 à 16:59
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é.
27 févr. 2009 à 15:56
27 févr. 2009 à 15:43
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
27 févr. 2009 à 12:51
Ensuite, ce code peux êtres déployé sur une autre serveur donc on déplace le probléme de charge mémoire/Travaille vers une machine qui n'auras pas la même importance dans le process glabal (voir dédier).
enfin, comme je le disait dans le dernier commentaire la fonction de transfert au serveur accept les interface IDatareader, celle ci représente un flux de lecture de donnée en avant uniquement, elle n'as donc pas besion de "Buffériser" les données, en gros pour expilquer l'interface:
une fonction ReadNext qui avannce d'une ligne le flux.
des fonction GetInt32(int col)/GetString(int col)/... qui lise la donnée de la [col] dans la ligne courante.
plusieur fonctions qui permette de gérés les métadonnée(nom de colone,type des colones etc.
En utilisant les fonction précédante et en les encapsulant dans une classe implémantant cette interface, on peu arrivé a ne jamais avoir plus d'une ligne de donné en mémoire.
Attention, en disant cela jetiens a présisé que je ne connait pas le fonctionnement interne de l'objet SqlBulkCopy, a t'il un buffer interne? a voir... un point important aussi, il faut bien pensé paramettré le Timeout car si l'opération est longue celui ci est vite dépassé et mettre une valeur trop longue peux complétement bugé le tout.
Enfin l'objet SqlBulkCopy posséde plusieur constructeur simpathique dont notement un constructeur permetant de spécifier une transaction et les option de Bulk (déclanchement des triggers,gestion des NULL/AutoIncrément etc, ce qui le rend particuliérement complet.
Un dernier point intérréssant de cette technique est sa souplesse il est trés facile d'introduire plusieurs couches de travaille intermédiare et de faire un code trés modulaire. ex :
Ajout d'un process de validation Métier:
public IEnumerable<donnéestructurées> ValidationMetier IEnumerable<donnéestructurées> donnéesavérifier)
{
foreach (donnéestructurées lignepropre in donnéesavérifier)
{
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;
}
}
Ce qui permet maintenant de faire :
Insererligne(ValidationMetier(nettoyerlignes(lireLignes("monfic.csv"))));
Maintenant, admettons que le fichier de départ est maintenant un XML :
public IEnumerable<donnéestructurées> LireDonnéesXML(string fichierxml)
{
....
yield return ...;
....
}
On peut mtn faire :
Insererligne(ValidationMetier(LireDonnéesXML("monfic.xml")))
La ca devient trés simpa car on a pu reprendre facilement le code commun sans ce prendre la tête et en gardant une compatibilité avec l'ancien code qui peut toujours servir si les deux type de fichier sont utilisé...
Enfin cette maniére de faire permet trés facilemet de test chaque fonction hors context et de valider son process:
public IEnumerable<string> fauxlecteur()
{
yield return "Tom;10;";
yield return "Rom;20";
yield return "Tiemh";
}
public void VérificationNetoyage(IEnumerable<donnéestructurées> données)
{
IEnumerator<donnéestructurées> enum = données.GetEnumerator();
if(!enum.MoveNext()) throw new Exception();
if(enum.Current.col_nom!= "Tom") throw new Exception();
if(enum.Current.col_age!= 10) throw new Exception();
if(!enum.MoveNext()) throw new Exception();
if(enum.Current.col_nom!= "Rom") throw new Exception();
if(enum.Current.col_age!= 20) throw new Exception();
if(enum.MoveNext()) throw new Exception();
}
maintenant si on fais : VérificationNetoyage(nettoyerlignes(fauxlecteur())); et qu'on obtient une exception alors le nétoyage ne fonctionne pas comme on l'attendrais!
Vu que ca à l'air de plaire, je vais trouvé un peux de temps se weekend pour faire une source d'exemple complette.
27 févr. 2009 à 12:02
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 ?
27 févr. 2009 à 09:04
27 févr. 2009 à 08:55
26 févr. 2009 à 22:22
if((nblignes%100000)!=0)
cpy.WriteToServer(donnéespretes);
26 févr. 2009 à 22:18
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.
26 févr. 2009 à 19:45
Je n'ai pas trouvé comment modifier un fichier texte sans le ré-écrire entièrement... Est-ce possible ?
26 févr. 2009 à 18:37
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...)
26 févr. 2009 à 17:15
1) Effectivement Tmcuh, on peut améliorer la source et rendre le nombre de colonnes dynamiques, mais je n'en ai ni l'utilité, ni le temps !
2) J'aurais pu traiter chaque ligne une par une, en faisant un select sur chaque ligne, en splittant la chaine sous C#, puis en générant une instruction INSERT. Mais cela pose de sérieux problèmes de perfs : pour 10 millions de lignes, ca fait 20 millions d'instructions envoyées au serveur !
Les fonctions sql utilisées ici (CHARINDEX, SUBSTRING, LEN, RTRIM, LTRIM) sont extrêmement peu coûteuses, même par rapport à une fonction sql qui utilise une table temporaire, et qui génère autant d'INSERT qu'il y a de colonne, et ce pour chaque ligne + l'INSERT dans la table finale, donc pour 10M de lignes, et 10 colonnes, ca fait 110 millions d'INSERT !
26 févr. 2009 à 16:03
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
26 févr. 2009 à 15:16
25 févr. 2009 à 14:13
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.
25 févr. 2009 à 13:56
La classe StringBuilder a t-elle été retirée du framework?
25 févr. 2009 à 12:54