JSON Array to Entity Framework Core VERY Slow?

Question

I'm working on a utility to read through a JSON file I've been given and to transform it into SQL Server. My weapon of choice is a .NET Core Console App (I'm trying to do all of my new work with .NET Core unless there is a compelling reason not to). I have the whole thing "working" but there is clearly a problem somewhere because the performance is truly horrifying almost to the point of being unusable.

The JSON file is approximately 27MB and contains a main array of 214 elements and each of those contains a couple of fields along with an array of from 150-350 records (that array has several fields and potentially a small <5 record array or two). Total records are approximately 35,000.

In the code below I've changed some names and stripped out a few of the fields to keep it more readable but all of the logic and code that does actual work is unchanged.

Keep in mind, I've done a lot of testing with the placement and number of calls to SaveChanges() think initially that number of trips to the Db was the problem. Although the version below is calling SaveChanges() once for each iteration of the 214-record loop, I've tried moving it outside of the entire looping structure and there is no discernible change in performance. In other words, with zero trips to the Db, this is still SLOW. How slow you ask, how does > 24 hours to run hit you? I'm willing to try anything at this point and am even considering moving the whole process into SQL Server but would much reather work in C# than TSQL.

static void Main(string[] args)
{
    string statusMsg = String.Empty;

    JArray sets = JArray.Parse(File.ReadAllText(@"C:\Users\Public\Downloads\ImportFile.json"));
    try
    {
        using (var _db = new WidgetDb())
        {
            for (int s = 0; s < sets.Count; s++)
            {
                Console.WriteLine($"{s.ToString()}: {sets[s]["name"]}");

                // First we create the Set
                Set eSet = new Set()
                {
                    SetCode = (string)sets[s]["code"],
                    SetName = (string)sets[s]["name"],
                    Type = (string)sets[s]["type"],
                    Block = (string)sets[s]["block"] ?? ""
                };
                _db.Entry(eSet).State = Microsoft.EntityFrameworkCore.EntityState.Added;

                JArray widgets = sets[s]["widgets"].ToObject<JArray>();
                for (int c = 0; c < widgets.Count; c++)
                {
                    Widget eWidget = new Widget()
                    {
                        WidgetId = (string)widgets[c]["id"],
                        Layout = (string)widgets[c]["layout"] ?? "",
                        WidgetName = (string)widgets[c]["name"],
                        WidgetNames = "",
                        ReleaseDate = releaseDate,
                        SetCode = (string)sets[s]["code"]
                    };

                    // WidgetColors
                    if (widgets[c]["colors"] != null)
                    {
                        JArray widgetColors = widgets[c]["colors"].ToObject<JArray>();

                        for (int cc = 0; cc < widgetColors.Count; cc++)
                        {
                            WidgetColor eWidgetColor = new WidgetColor()
                            {
                                WidgetId = eWidget.WidgetId,
                                Color = (string)widgets[c]["colors"][cc]
                            };
                            _db.Entry(eWidgetColor).State = Microsoft.EntityFrameworkCore.EntityState.Added;
                        }
                    }

                    // WidgetTypes
                    if (widgets[c]["types"] != null)
                    {
                        JArray widgetTypes = widgets[c]["types"].ToObject<JArray>();

                        for (int ct = 0; ct < widgetTypes.Count; ct++)
                        {
                            WidgetType eWidgetType = new WidgetType()
                            {
                                WidgetId = eWidget.WidgetId,
                                Type = (string)widgets[c]["types"][ct]
                            };
                            _db.Entry(eWidgetType).State = Microsoft.EntityFrameworkCore.EntityState.Added;
                        }
                    }

                    // WidgetVariations
                    if (widgets[c]["variations"] != null)
                    {
                        JArray widgetVariations = widgets[c]["variations"].ToObject<JArray>();

                        for (int cv = 0; cv < widgetVariations.Count; cv++)
                        {
                            WidgetVariation eWidgetVariation = new WidgetVariation()
                            {
                                WidgetId = eWidget.WidgetId,
                                Variation = (string)widgets[c]["variations"][cv]
                            };
                            _db.Entry(eWidgetVariation).State = Microsoft.EntityFrameworkCore.EntityState.Added;
                        }
                    }
                }
                _db.SaveChanges();
            }
        }

        statusMsg = "Import Complete";
    }
    catch (Exception ex)
    {
        statusMsg = ex.Message + " (" + ex.InnerException + ")";
    }

    Console.WriteLine(statusMsg);
    Console.ReadKey();
} 

Answer 1

If you're making thousands of updates then EF is not really the way to go. Something like SQLBulkCopy will do the trick.

You could try the bulkwriter library.

IEnumerable<string> ReadFile(string path)  
{
using (var stream = File.OpenRead(path))
     using (var reader = new StreamReader(stream))
    {
        while (reader.Peek() >= 0)
       {
              yield return reader.ReadLine();
       }
    }
}

var items =  
    from line in ReadFile(@"C:\products.csv")
    let values = line.Split(',')
    select new Product {Sku = values[0], Name = values[1]};

then

using (var bulkWriter = new BulkWriter<Product>(connectionString)) {  
     bulkWriter.WriteToDatabase(items);
}

Answer 2

I had an issue with that kind of code, lots of loops and tons of changing state.

Any change / manipulation you make in _db context, will generate a "trace" of it. And it making your context slower each time. Read more here.

The fix for me was to create new EF context(_db) at some key points. It saved me a few hours per run!

You could try to create a new instance of _db each iteration in this loop

contains a main array of 214 elements

If it make no change, try to add some stopwatch to get a best idea of what/where is taking so long.