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.

Recommended Answers

All 6 Replies

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

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)

As Suzie said, seeing the code would help here.

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();
        }
    }



}

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

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...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.