ASP.MVC EF6 base filling blocking user interface

Question

I'm using ASP.MVC 5 and EF6. My purpose is to fill database with data from another server and not to block user interface.

Database filling takes a lot of time, about 10-15 minutes. And user can work with system at this time. Now he has very bad freezes for several minutes. So, the fact is, that user load data from base and filling task load data to database in one time.

I've tried to put database filling code to another threads, but it didn't help. When user log in first time, he execute some action, that start database filling. After this action user can do anything else and interface must not be blocked.

When user use the system, he always need to load some data from my database. For example: when user see some page on 1 minute, there is 10000 loaded datas, on 2 minute there is more datas and so on.

public ActionResult SomeAction()
{
    new Thread(async () =>
    {
        Thread.CurrentThread.IsBackground = true;
        await ImportTask();
    }).Start();
    ... (some code)
    return View();
}

public async Task ImportTask()
{
    for (var i = 0; i < 180; i++)
    {
        using (var context = new ApplicationDBContext())
        {
            // synchronized data download with some sided library
            var data = GetSomeDataFromAnotherServer(i); 
            foreach (var dataPart in data)
            {
                var stat = context.Data
                    .FirstOrDefault(x =>
                        x.EnumProperty == dataPart.EnumProperty &&
                        x.LongProperty == dataPart.LongProperty &&
                        x.DateTimeProperty == dataPart.DateTimeProperty)
                    ?? new DataType()
                    {
                        EnumProperty = dataPart.EnumProperty,
                        LongProperty = dataPart.LongProperty,
                        DateTimeProperty == dataPart.DateTimeProperty
                    };
                ... (some another stat filling)

                context.Statistics.AddOrUpdate(stat);
            }
            await context.SaveChangesAsync();
        }
    }
}

And there is another question: i have ability to download data with about 10 threads in one time. How i can modify this code to do so?

p.s. code can contain some typos here

score 0 · Answer 1 · answered Mar 24 '16 at 10:47

Don't use the Thread class, it's an old arcane, hard to understand/use api that has become obsolete for 99 % of use cases. Prefer Task.Run for doing CPU bound work, although don't use that either in asp.net. asp doesn't play well with you when you steal threads from it, and it's easy to create deadlocks.

When dealing with a true asynchronous api (like DbContext.SaveChangesAsync()), just use async/await, don't bother with threads at all, they don't give you any performance gains in a web context, and they even hurt it (and hurt scalability too).

Hubert Rybak · Answer 2 · 2016-03-24T13:21:41.583

0

Try:

     public async Task<ActionResult> SomeAction()
    {
        await Task.Run(() => { ImportTask(); });
        return View();
    }

    public async Task ImportTask()
    {
        using (var context = new ApplicationDBContext()) 
        {
            context.Configuration.AutoDetectChangesEnabled = false;
            for (var i = 0; i < 180; i++)
            {
                var data = GetSomeDataFromAnotherServer(i);
                foreach (var dataPart in data)
                {
                    var stat = context.Data.FirstOrDefault(x => x.EnumProperty == dataPart.EnumProperty && x.LongProperty == dataPart.LongProperty && x.DateTimeProperty == dataPart.DateTimeProperty) 
                        ?? new DataType()
                                                                                                                                                                                                              {
                    ...
                    (some
                    another stat 
                    filling)

                    context.Statistics.AddOrUpdate(stat);
                }
            }
            await context.SaveChangesAsync();
        }
    }

Using using (var context = new ApplicationDBContext()) inside for (var i = 0; i < 180; i++) results in 180 create & dispose operations of DbContext. Also for bulk inserts, for speed up, you can use context.Configuration.AutoDetectChangesEnabled = false; or try this library https://efbulkinsert.codeplex.com/

edited Mar 24 '16 at 13:21

answered Mar 24 '16 at 11:49

Hubert Rybak

1
2

I can't see way to use only 1 AppDBContext for all operations. Problem is that context can be suddenly closed after 1-2 minutes with exception. We avoid this problem with Owin context, but it works only for actions, and task is not an action. – mikhail-koviazin Mar 24 '16 at 13:02
Recreate context after n inserts, or use `System.Data.SqlClient.SqlBulkCopy`. Exception after 1-2 minutes you have is probably `OutOfMemoryException`. See: [Fastest Way of Inserting in Entity Framework](http://stackoverflow.com/a/5942176/5246410) – Hubert Rybak Mar 24 '16 at 13:12
`SaveChanges` should also be outside loop, so you don't end up with database call for every item update. – Hubert Rybak Mar 24 '16 at 13:23
It's actually faster to save individually per this answer http://stackoverflow.com/questions/1930982/when-should-i-call-savechanges-when-creating-1000s-of-entity-framework-object – user1477388 Mar 24 '16 at 14:24
@HubertRybak, the problem with SqlBulk is that we need to recognize, if there is existing entity in our base with some same properties. So some of data will be inserted to base but some will be updated. I think, it's the main problem of my code. – mikhail-koviazin Mar 24 '16 at 14:50
Consider using temporary table for bulk insert and then Sql Upsert, some example: http://www.jarloo.com/c-bulk-upsert-to-sql-server-tutorial/. In general EF is not good choice for such heavy db operations. – Hubert Rybak Mar 25 '16 at 11:27

score 0 · Answer 3 · answered Mar 24 '16 at 14:01

You don't need to be creating raw threads or spinning out tasks via task.Run() in an ASP.NET context, as it's mainly just a performance hindrance in an ASP.NET context. I wouldn't use it for a "fire-and-forget" implementation either as it's highly unreliable. If you wanted a fire and forget you could look into using: HostingEnvironment.QueueBackgroundWorkItem, but I'm not sure that's the best approach either.

Have you considered perhaps creating another process to perform this instead of it happening during a web request? Perhaps a service running on the server could be monitoring when someone logs in and does the movement of data? Another option may be that you call this data movement operation from some javascript on the browser to kick it off?

ASP.MVC EF6 base filling blocking user interface

3 Answers3