Member Avatar for 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.

  • 4 Contributors
  • 6 Replies
  • 306 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Suzie999

Dani AI

Generated

Quick diagnosis based on 's code: the biggest slow points are (1) pre-counting lines with File.ReadLines(...).Count (this forces a full file scan before processing), (2) creating/throwing away a SqlCommand and calling ExecuteNonQuery for every single row, and (3) doing the whole job in an ASP.NET Page_Load (the web request/thread will block and the browser/worker will appear unresponsive). was right to ask for code and 's suggestion about grouping operations is on track — but a single gigantic transaction is not always the right fix (it can bloat the log and block other activity).

Practical, high-impact fixes (apply in this order):

  • Move the import off the web request: run as a console app/Windows service, SSIS package, or SQL Agent job so the UI thread is not involved.
  • Avoid row-by-row database calls. Use SqlBulkCopy, BULK INSERT/BCP, or send batches via a Table-Valued Parameter (TVP). Example pattern (fill an in-memory DataTable, write in batches):
var table = new DataTable();
// define columns with correct types
table.Columns.Add("LogDate", typeof(DateTime));
table.Columns.Add("SourceIP", typeof(string));
// add parsed rows to table...
using(var b = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null))
{
    b.DestinationTableName = "dbo.Logs";
    b.BatchSize = 10000;
    b.WriteToServer(table);
}
table.Clear();

If a stored-proc must be used, reuse one SqlCommand created outside the loop, add SqlParameters once, then update parameter.Value each iteration. Avoid AddWithValue; declare SqlDbType and size to prevent type conversion overhead. Commit in moderate batches (e.g., every 5k–50k rows) to limit transaction-log growth and locking.

Server-side tuning: disable nonessential indexes/triggers during load and rebuild after; consider SIMPLE or BULK_LOGGED recovery temporarily if backups/requirements allow; run the import on a server with fast I/O or on the same network segment as SQL Server to reduce network latency (as noted). Profile iteratively: test with small batches, measure wall time and log usage, then scale.

Recommended Answers

All 6 Replies

Member Avatar for Suzie999

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

Member Avatar for 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)
Member Avatar for Mike Askew

As Suzie said, seeing the code would help here.

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



}
Member Avatar for 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.

Member Avatar for 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...

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.