Looping through an Excel document in C#

Question

Before looping through an Excel document using a library I found, and wanted to know how long it would take to loop through the whole thing.

There are 40k rows.

I looped through only the first 5k and it took just 2 minutes. So, I thought, that looping through 40k would take just 16 minutes total.

However, the program has been running for over an hour now, and it still hasn't completed.

What would be slowing this thing down? Granted, I didn't program it for high efficiency, but there must be something that is slowing this down... Why wasn't my initial estimate accurate?

Here is my method:

public static void Validate(ExcelConfiguration config)
{
    var ctx = new IntakeEntities();
    Stopwatch sw = new Stopwatch();
    sw.Start();
    HashSet<string> missingEntities = new HashSet<string>();

    Console.WriteLine("Validating...");

    using (ExcelPackage xlPackage = new ExcelPackage(config.file))
    {
        ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[config.WorkSheet];

        // loop through each row
        for (int iRow = config.DataRowStart; iRow <= config.DataRowFinish; iRow++)
        {
            // get home country from database
            string homeCountryName = worksheet.Cell(iRow, config.HomeCountryColumnIndex).Value.Trim();
            Guid? homeCountryId = null;

            try
            {
                homeCountryId = ctx.Countries.AsNoTracking().Where(x => x.Name == homeCountryName).First().Id;
                //Console.WriteLine(homeCountryId + " - " + homeCountryName);
            }
            catch (Exception ex)
            {
                //Console.WriteLine("No record found for " + homeCountryName);
                missingEntities.Add(homeCountryName);
            }

            // get host country from database
            string hostCountryName = worksheet.Cell(iRow, config.HostCountryColumnIndex).Value.Trim();
            Guid? hostCountryId = null;

            try
            {
                hostCountryId = ctx.Countries.AsNoTracking().Where(x => x.Name == hostCountryName).First().Id;
                //Console.WriteLine(hostCountryId + " - " + hostCountryName);
            }
            catch (Exception ex)
            {
                //Console.WriteLine("No record found for " + hostCountryName);
                missingEntities.Add(hostCountryName);
            }

            if (config.HomeLocationColumnIndex != null && config.HostLocationColumnIndex != null)
            {
                // get home country from database
                string homeLocationName = worksheet.Cell(iRow, (int)config.HomeLocationColumnIndex).Value.Trim();
                Guid? homeLocationId = null;

                try
                {
                    homeLocationId = ctx.Locations.AsNoTracking().Where(x => x.Name == homeLocationName).First().Id;
                    //Console.WriteLine(homeCountryId + " - " + homeCountryName);
                }
                catch (Exception ex)
                {
                    //Console.WriteLine("No record found for " + homeLocationName);
                    missingEntities.Add(homeLocationName);
                }

                // get host country from database
                string hostLocationName = worksheet.Cell(iRow, (int)config.HostLocationColumnIndex).Value.Trim();
                Guid? hostLocationId = null;

                try
                {
                    hostLocationId = ctx.Locations.AsNoTracking().Where(x => x.Name == hostLocationName).First().Id;
                    //Console.WriteLine(hostCountryId + " - " + hostCountryName);
                }
                catch (Exception ex)
                {
                    //Console.WriteLine("No record found for " + hostLocationName);
                    missingEntities.Add(hostLocationName);
                }
            }
        }
    }
    Console.WriteLine("Validation complete...");
    Console.WriteLine("Time elapsed: {0} minutes", sw.Elapsed.Minutes);

    Console.WriteLine("No records found for:");
    foreach (string entity in missingEntities)
    {
        Console.WriteLine(entity);
    }
}

Here is the accompanying object:

public class ExcelConfiguration
{
    public FileInfo file { get; set; }
    public int HomeCountryColumnIndex { get; set; }
    public int HostCountryColumnIndex { get; set; }
    public int? HomeLocationColumnIndex { get; set; }
    public int? HostLocationColumnIndex { get; set; }
    public int WorkSheet { get; set; }
    public int DataRowStart { get; set; }
    public int DataRowFinish { get; set; }
}

Answer 1

• Have performance issues? Use a profiler. (There is a built-in in certain versions of Visual Studio and a bunch of third-party tools, with evaluation times). Find out what is taking time and we can give you an advice for optimization after that.
• As already mentioned, do not use exceptions for normal program flow. Use FirstOrDefault() method instead of First() so you don't get an exception for missing items.
• If you want a guess - you have 4 database queries per row => 160k queries. Either adjust database indexes, or load whole tables into Dictionary<> if they are small enough (Countries table, for example, is probably small)

Answer 2

public static void Validate(ExcelConfiguration config)

I wouldn't make this a static method, but without knowing more about your project it's just a nitpick. The name is highly misleading though. Without looking at the code, one would expect that the method validates the passed ExcelConfiguration - I don't know, maybe check that DataRowStart is within a specific range (like, a positive number), and that the File (capital F) exists... wait, you've got a FileInfo there, that's rather surprising. Also WorkSheet should be called WorksheetIndex... but I digress.

var ctx = new IntakeEntities();

I like that you're using var. However if this IntakeEntities is what I think it is, it's derived from DbContext and implements IDisposable, and should be wrapped in a using block.

Stopwatch sw = new Stopwatch();

Why not use var again? Be consistent! That said, you're doing the right thing - StopWatch is how you should time execution... but that stop watch should be in the method's calling code, starting just before the method call and stopping right after it returns. Writing the results to the console doesn't belong in that method either.

---

Are you actually catching many exceptions? Are exceptions exceptional or just a "neat" way of adding items to missingEntities?

I think you're misusing exceptions, because if you didn't systematically tried to get the Id property of an object that's potentially null, you could do a null check instead, and to everything without catching a NullReferenceException - use SingleOrDefault instead of Where and then First, it makes it clearer that you're expecting zero or one values:

var homeCountry = ctx.Countries.AsNoTracking().SingleOrDefault(x => x.Name == homeCountryName);
homeCountryId = (homeCountry == null ? 0 : homeCountry.Id);

if (homeCountryId == 0)
{
    missingEntities.Add(homeCountryName);
}

Throwing and catching exceptions without a real reason, in a loop, 40K times, can slow things down if half the iterations throw 2-3 exceptions. I'd try to eliminate the throwing and catching first, see what I get.

Then I'd try to reduce the number of Excel worksheet reads, if that's at all possible.

Answer 3

With appreciation for the answers and sound advice provided here, I have concluded that the real source of the issue was not my improper use of try/catch nor FirstOrDefault() or any of that, but rather, it was the Excel package.

To fix this, the only solution would be to convert the Excel into a CSV which would allow for faster access of data. Instead of that, I just chose to modify my DataRowStart and DataRowFinish by shrinking the window so it wouldn't get bogged down.

Update:

After converting my document to CSV and using FileHelpers and changing the runtime configuration of my DbContext like so:

yourContext.Configuration.AutoDetectChangesEnabled = false;
yourContext.Configuration.ValidateOnSaveEnabled = false;

... I was able to validate and insert all 40k records in under 15 minutes; whereas, before it would've taken hours (if it finished at all). I think most of the benefit actually came from the DbContext change. That alone was a major improvement.

Answer 4

The one thing that people are touching on but not saying outright is the fact that Exception handling takes a lot of processing power away from what you want going on, just the operation of catching the exception itself is what is slowing you down.

you are using Catch statements inappropriately.

if you know the exception then you need to code so that it won't happen.

a try catch statement is more for the unexpected errors.

like @Mat's Mug was saying, check to see if it exists, if it doesn't add it to the missing entities collection, don't let it throw an error.

---

as far as your estimate being wrong.

I looped through only the first 5k and it took just 2 minutes. So, I thought, that looping through 40k would take just 16 minutes total.

the issue with this is that for the first 5k records there is (theoretically) nothing being held in memory or processor cache or heap, so when the next 5k records go through there is stuff being processed already these 5k records have to wait in line, stack another 5k on top of that, now the processor is getting warm which slows it down a little bit, it's trying to multi thread information is getting backed up. it's like having 40 tabs open on a browser, I am done with the first 35 but have things running super slow on the remaining 5 it's slow because there is stuff in the way sitting in the receiving and sending area.

okay so I BS'd my way through that, but you get the point right?