c# SQL slow write

I'm writing a program to push text lines that has been read from a couple of files to a SQL database. But the thing is that the text files can be very long containing a lot of lines and this is causing the performance to be really poor, what i think is a result of writing all these lines individually.

So I'm wondering is there a way to increase performance by a lot? store the data in a array or list and push the list to prevent a lot of server data being send around? I've read things about batch and SQL but have no idea on how to apply.

I'm already using ADO.NET to read data from the database

folders i'm reading contains from 0 to 8 .txt files that can be pritty lengthy, the data needs to be combined and stored in a sql database removing duplicates.

If some one has advice on how to solve this issue I will be very grateful!

example of a .txt file content

01/12/2009 09:37:11:178 06-00-46  Present 508 Def. of incoh. richtwaarde voor blending
etat_x_convoi_ok = 0x01 '' 
APP_eds_inco_h = 2.01327e+009 
APP_eds_haut_h = 3.35872e+008 
APP_eds_bas_b = 1.34218e+009 
APP_edsoll = 4.17759e+009 
SUD_edsoll_inco = 0x01 '' 
SUD_edsoll_haut = 0x00 ' 
SUD_edsoll_bas = 0x00 ' 
etat_x_convoi_ok = 0x01 '' 
etat_commande = 0 
APP_sens_de_marche = 33554432 
mesuinond = 2.53388e+009 
APP_vitesse1 = 8.38861e+007 
APP_COM_couple_rea = 0.00000e+000 
APP_COM_cons_couple = 0.00000e+000 
FIL_c_uindc = 2.26578e+009 
APP_ul_crete = 0.00000e+000 
type_cat_in = 256 


09/07/2009 03:53:26:105 01-00-10  Present 0 Blokkering van een i960-opdracht
etat_x_convoi_ok = 0x01 '' 
survl_cpt_memo = 38536192 
survl_cpt = 88867840 
CT = 0x03 '' 
CS = 0x01 '' 
RF = 0xFD 'ý' 
WDG_erreur = 117440512 
WDG_demarrage = 50331648 


13/07/2009 18:36:34:704 05-00-03  Present 1 Overschakeling van MPU
etat_x_convoi_ok = 0x01 '' 
ctx_num_new_mpu = 0x01 '' 
ctx_num_old_mpu = 0x00 ' 

Code:

private void PushFoutenToSQLdb(string datum, string tijd, string foutcode, string module, string omschrijving, string teller, string absentPresent, string treinNaam)
        {
            var myCommand = new SqlCommand("INSERT INTO [Events].[dbo].[Fouten]  (Datum ,FoutCode, Omschrijving, Module,Time,Teller,Mnemo, TreinId)  Values (@datum , @foutcode, @omschrijving, @module, @tijd, @teller, @absentPresent ,(SELECT TreinId from [Events].[dbo].[Treinen] WHERE Name = @treinNaam))", myConnection);
            myCommand.Parameters.AddWithValue("@datum", datum);
            myCommand.Parameters.AddWithValue("@tijd", tijd);
            myCommand.Parameters.AddWithValue("@foutcode", foutcode);
            myCommand.Parameters.AddWithValue("@module", module);
            myCommand.Parameters.AddWithValue("@teller", teller);
            myCommand.Parameters.AddWithValue("@omschrijving", omschrijving);
            myCommand.Parameters.AddWithValue("@absentPresent", absentPresent);
            myCommand.Parameters.AddWithValue("@treinNaam", treinNaam);

            try
            {
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
        }

Answers


Microsoft Provided some feature as FILESTREAM in SQL Server 2008 & FILE Table in 2012 to store text data in file. I think you should use that instead of storing text data in table. It will give you good performance.

let me know if you face any issue at rajesh.kuwnar@hotmail.com.

Thanks


Need Your Help

mergesort array of int using pointers

c pointers merge sorting

For school I have to program a merge sort using only pointers.

Tag Property in WPF DataGrid Column

wpf datagrid tags datagridcolumn

I need to save an string inside a Datagrid Column which differs from the Header.