slowness in transaction with SQL DataBase

[alaad]

Hello every body,
I working an application in C# to process a log file that has a size 1 terabytes.
I have to read the file row by row and insert each row in the DataBase to search in it, and I use a stored procedure.
But I have a problem in transaction of query to SQL DataBase because it takes a lot of time and the computer be in "not responsible" mode because of the huge number of rows in the file.
How can i reduce the time which the query takes to be executed and optimize the performance.

[Suzie999]

No-one can know until they see how you do it now

[abelingaw]

There are SQL statements which differ in the way they are written but faster when it comes in loading data.

You should also consider some factors:

Network speed (if it is networked)
Transmission line capacity (still, if networked)

[Mike Askew]

As Suzie said, seeing the code would help here.

[alaad]

this is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Globalization;
using System.Data;

public partial class _Default : System.Web.UI.Page
{


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            remplir();
        }
    }
    public void remplir()
    {
        SqlConnection conn = ConnectionManager.getConnection();
        SqlCommand cmd = new SqlCommand();
        string query = string.Empty;
        cmd.Connection = conn;
        int result;
        TimeSpan duration;
        DateTime startDate = DateTime.Now;
        int rownumber =  0;
        try
        {
            string line = string.Empty;
            StreamReader sr = new StreamReader("C:\\Users\\Alaa\\Desktop\\logs\\traineeaa.txt");
            string path = "C:\\Users\\Alaa\\Desktop\\logs\\traineeaa.txt";
            rownumber = File.ReadLines(path).Count(); 



            int flag = 0;

            while ((line = sr.ReadLine()) != null)
            {
                if (line == "") { }
                else
                {
                    if ((line.Substring(0, 1)) == "#")
                    {

                    }
                    else
                    {
                        string[] arr = line.Split(' ');
                        list l = new list();
                        l.setDate(arr[0]);

                        l.setTime(arr[1]);

                        l.setSourceIP(arr[2]);
                        l.setOpp(arr[3]);
                        l.setProtocol(arr[4]);
                        l.setSite(arr[5]);
                        l.setPort(arr[6]);
                        if (arr.Length == 9)
                        {

                            l.setPath(arr[7]);
                            l.setQuery("");
                            l.setS_ip(arr[8]);
                        }
                        else
                        {
                            l.setPath(arr[7]);
                            l.setQuery(arr[8]);
                            l.setS_ip(arr[9]);
                        }

                        if(flag == 0){
                            startDate = DateTime.Now;
                            error.Text = (DateTime.Now.Subtract(startDate)).ToString();
                            flag++;
                        }
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "UpdateLog";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@datee", l.getDate());
                        cmd.Parameters.AddWithValue("@timee", l.getTime());
                        cmd.Parameters.AddWithValue("@c_ip", l.getSourceIP());
                        cmd.Parameters.AddWithValue("@cs_method", l.getOpp());
                        cmd.Parameters.AddWithValue("@cs_uri_scheme", l.getPortocol());
                        cmd.Parameters.AddWithValue("@cs_host", l.getSite());
                        cmd.Parameters.AddWithValue("@cs_uri_port", l.getPort());
                        cmd.Parameters.AddWithValue("@cs_uri_pathh", l.getPath());
                        cmd.Parameters.AddWithValue("@cs_uri_query", l.getQuery());
                        cmd.Parameters.AddWithValue("@s_ip", l.getS_ip());
                        cmd.Parameters.AddWithValue("@namefile", "traineeaa.txt");
                        cmd.Parameters.AddWithValue("@rownumber",rownumber);
                        cmd.Parameters.AddWithValue("@startdate", DateTime.Now);
                        result = cmd.ExecuteNonQuery();
                        l = null;
                        if (result == 0)
                        {
                            error.Text = "insert faild";
                        }


                    }
                    cmd = null;
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                }


            }
            duration = DateTime.Now.Subtract(startDate);
            error2.Text = duration.ToString();
        }
        catch (Exception ex)
        {
            error.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }



}

[Mike Askew]

You should look into using a SQL transaction to execute all of your requests in one go, rather than one at a time.

[Suzie999]

I would hazzard a guess that all the methods used in the loop, are what might be taking the time.

Such as l.getDate() and l.getTime() etc...