I want to Optimize upload time of Excel file records saving to Database in asp.net mvc

Question

I want to upload the excel file of record 2500. This process takes time more than 5 minutes approximate. I want to optimize it to less than a minute maximum. Find the best way of optimization of that code. I am using Dbgeography for location storing. File is uploaded and save the data properly. Everything is working find but I need optimization.

public ActionResult Upload(HttpPostedFileBase FileUpload)
    {
        if (FileUpload.ContentLength > 0)
        {
            string fileName = Path.GetFileName(FileUpload.FileName);
            string ext = fileName.Substring(fileName.LastIndexOf('.'));
            Random r = new Random();
            int ran = r.Next(1, 13);
            string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), DateTime.Now.Ticks + "_" + ran + ext);
            try
            {
                FileUpload.SaveAs(path);
                ProcessCSV(path);
                ViewData["Feedback"] = "Upload Complete";
            }
            catch (Exception ex)
            {
                ViewData["Feedback"] = ex.Message;
            }
        }

        return View("Upload", ViewData["Feedback"]);
    }

Here is other method from where the file is uploaded and save it to the database..

private void ProcessCSV(string filename)
    {
        Regex r = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
        StreamReader sr = new StreamReader(filename);
        string line = null;
        string[] strArray;
        int iteration = 0;
        while ((line = sr.ReadLine()) != null)
        {

                if (iteration == 0)
                {
                    iteration++;
                    continue;                   //Because Its Heading
                }

                strArray = r.Split(line);
                StoreLocation store = new StoreLocation();
                store.Name = strArray[0];
                store.StoreName = strArray[1];
                store.Street = strArray[2];
                store.Street2 = strArray[3];
                store.St_City = strArray[4];
                store.St_St = strArray[5];
                store.St_Zip = strArray[6];
                store.St_Phone = strArray[7];
                store.Office_Phone = strArray[8];
                store.Fax = strArray[9];
                store.Ownership = strArray[10];
                store.website = strArray[11];
                store.Retailer = check(strArray[12]);
                store.WarehouseDistributor = check(strArray[13]);
                store.OnlineRetailer = check(strArray[14]);
                string temp_Add = store.Street + "," + store.St_City;
                try
                {
                    var  point = new GoogleLocationService().GetLatLongFromAddress(temp_Add);
                    string points = string.Format("POINT({0} {1})", point.Latitude, point.Longitude);
                    store.Location = DbGeography.FromText(points);//lat_long
                }
                catch (Exception e)
                {
                    continue;
                }
                db.StoreLocations.Add(store);
                db.SaveChanges();  
        }

Answer 1

The call to db.SaveChanges() which updates the database, this is happening for every line.

while (...)
{
   ...
   db.StoreLocations.Add(store);
   db.SaveChanges();              // Many database updates
}

Instead, just call it once at the end:

while (...)
{
   ...
   db.StoreLocations.Add(store);
}

db.SaveChanges();                 // One combined database update

This should speed up your code nicely.

Answer 2

The obvious place to start would be your external call to the Geo Location service, which it looks like you are doing for each iteration of the loop. I don't know that service, but is there any way you can build up a list of addresses in memory, and then hit it once with multiple addresses, then go back and amend all the records you need to update?