How to improve the performance of below code?

Question

I have written a code to ping 300 systems in a network and update the status(offline/online) in to the Access database file.

I'm using task class for that. Pinging 300 systems taking only less than a second but inserting those status in to database file taking nearly 30 to 40 seconds.

It reducing my application's performance, please take a look in to my code. If

Main method

private static void Main(string[] args)
    {
        #region Reading IpAdddress

        List<string> address = new List<string>();
        Task t = Task.Run(() =>
        {
            var reader = new StreamReader(File.OpenRead(Environment.CurrentDirectory + @"\address.csv"));
            while (!reader.EndOfStream)
            {
                var lines = reader.ReadLine();
                var values = lines.Split(';');
                address.Add(values[0]);
            }
        });

        #endregion

        Stopwatch timeSpan = Stopwatch.StartNew();

        t.Wait();


        AsyncPingTask(address).Wait();
        Console.WriteLine("Update Completed");

        Console.WriteLine(timeSpan.ElapsedMilliseconds);
        Console.ReadLine();
    }

Ping task

 private static async Task AsyncPingTask(List<string> ipaddress)
    {
        try
        {
            Console.WriteLine("Ping Started");                              

            var pingTasks = ipaddress.Select(ip =>
            {
                return new Ping().SendTaskAsync(ip);                    
            }).ToList();

           var replies= await Task.WhenAll(pingTasks);
            Console.WriteLine("Ping Completed");

            int online, offline;
            online = 0;
            offline = 0;
            Console.WriteLine("Update in progress...");                

            foreach (var pingReply in replies)
            {
                var status = "";
                if (pingReply.Reply.Status.ToString() == "Success")
                {
                    online++;
                    status = "Online";

                }
                else
                {
                    status = "Offline";
                    offline++;
                }
                Program p = new Program();                    
                Parallel.Invoke(() =>
                {
                    p.UpdateSystemStatus(pingReply.Address, status);
                });

            }


            Console.WriteLine("Online Systems : {0}", online);
            Console.WriteLine("Offline Systems : {0}", offline);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw;
        }

    }

Update status method

private void UpdateSystemStatus(string ipAddr, string status)
{
   using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\/Topology.accdb"))
   {
      string query = "UPDATE SystemStatus SET SystemStatus=@SystemStatus WHERE IP='" + ipAddr + "'";

      OleDbCommand cmd = new OleDbCommand(query, con);
      con.Open();
      cmd.Parameters.AddWithValue("@SystemStatus", status);

      cmd.ExecuteNonQuery();         
   }
}

Answer 1

This is not my area of expertise but my guess is that there is a lot of overhead in creating a new OleDbConnection for each record that is updated. I could imagine that it at least has to open the access database in the file system, check permissions, parse a bunch of stuff out, etc... An approach where the connection was created once would probably work much better. I also see that there is some Transaction mechanism that could possibly improve performance.

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\/Topology.accdb"))
{
    con.Open();
    var trans = con.BeginTransaction();

    foreach (var pingReply in replies)
    {
        var status = "";
        if (pingReply.Reply.Status.ToString() == "Success")
        {
            online++;
            status = "Online";
        }
        else
        {
            status = "Offline";
            offline++;
        }

        string query = "UPDATE SystemStatus SET SystemStatus=@SystemStatus WHERE IP='" + ipAddr + "'";

        OleDbCommand cmd = new OleDbCommand(query, con);
        cmd.Transaction = trans;
        cmd.Parameters.AddWithValue("@SystemStatus", status);

        cmd.ExecuteNonQuery();         
    }
    trans.Commit();
}