Running an inserting to database task more faster

Question

I asked a question that was how can I run a task faster but none of the answers worked for me. I have the following code which inserts 1500 records to the database but the problem is it takes about 4 seconds. I used async/await, parallel loop and AddRangeand also I disabled change auto detection and validation on save but none of them has effect. My code is this:

async void button7_Click(object sender, EventArgs e)
{
    var task = await Task.Run(() =>
    {
        Random rnd = new Random();
        for (int i = 0; i <= 1500; i++)
        {
            db.Tbls.Add(new Tbl()
            {
                Name = "User" + i + 1,
                Num = rnd.Next(10, i + 10) / 10
            });
        }

        db.SaveChanges();
        return db.Tbls.Count();

    });
}

And with AddRange:

async void button7_Click(object sender, EventArgs e)
{
    var task = await Task.Run(() =>
    {
        Random rnd = new Random();
        var tbls = new List<Tbl>();
        for (int i = 0; i <= 1500; i++)
        {
            tbls.Add(new Tbl()
            {
                Name = "User" + i + 1,
                Num = rnd.Next(10, i + 10) / 10
            });
            progress.Report(i * 100 / 1500);
         }
        db.Tbls.AddRange(tbls);
        db.SaveChanges();
        return db.Tbls.Count();

    });
}

And with parallel loop:

var task = await Task.Run(() =>
{
    int seed = Environment.TickCount;
    var random = new ThreadLocal<Random>(() => new Random(Interlocked.Increment(ref seed)));
    var tbls = new ConcurrentBag<Tbl>();
    Parallel.For(0, 1500, (i) => {
        tbls.Add(new Tbl()
        {
            Name = "User" + i + 1,
            Num = random.Value.Next(10, i + 10) / 10
        });
    });                
    db.Tbls.AddRange(tbls);
    db.SaveChanges();
    return db.Tbls.Count();
});

Does anyone know what is the problem?

Does your table have primary key? How much time takes inserting 1500 records without any asyncronous and/or parallel execution? Looking at SQL Profiler output, what 'invisible' operations besides `insert` commands you see there? — Arvo, May 23 '16 at 11:24
Yes. My table has primary key. With or without asyncronous and/or parallel execution it takes about 4 seconds. I used code first approach also. — , May 23 '16 at 11:30
There is no parallel database access in any of these snippets. All these snippets do exactly the same thing - call `SaveChanges` synchronously. — Panagiotis Kanavos, May 24 '16 at 09:58

ConcernedOfTunbridgeWells · Answer 1 · 2016-05-23T11:20:35.740

1

If you want to load a significant number of rows into the database in one hit then you will probably get better performance from bulk loading. I could imagine 1500 inserts taking 4 seconds quite easily if you load individual rows - and you probably won't get individual inserts running significantly faster as the principal bottleneck is the minimum I/O needed for any database transaction.

With a bulk load API - e.g. SQLBulkCopy - you prepare a record set and then load it into the database in one hit. This will be much, much faster than 1,500 individual inserts.

You haven't specified the DB platform that you need to have a driver that supports bulk loading. The SQL Server one does; some of (for example) the Oracle ones don't unless you buy certain editions.

edited May 23 '16 at 11:20

answered May 23 '16 at 11:16

ConcernedOfTunbridgeWells

64,444
15
143
197

I use sql server. I'm sure that my code should runs less than one second at least. But I don't know why and what is the issue which caused it takes too long. – May 23 '16 at 11:20
Can you please show me an example in entity framework? – May 23 '16 at 11:21
OTOH I don't know if entity framework supports bulk loading. You may need to use the SQLBulkCopy API directly. – ConcernedOfTunbridgeWells May 23 '16 at 11:23
Well one of the answerer of my old question said: "I've tested this and with AddRange instead of Add inside a loop it takes 700ms for me versus 6.6 seconds with your original code." so I'm sure this should be doable with entity framework but i don't know how? – May 23 '16 at 11:25
There is a bulk insert extension for EF on codeplex discussed in your previous [here](http://stackoverflow.com/a/22236694/15401). It might do what you want. However, if you can't get what you need from it you will probably have to drop down to using bulk inserts directly. – ConcernedOfTunbridgeWells May 23 '16 at 11:25
Thanks but I don't want any extension because as I said this code has worked for one of the answerer in my old question in 700ms without any extension so I hope to know why my code should not work about 700ms. – May 23 '16 at 11:27
chekc for locking,blockings – TheGameiswar May 23 '16 at 11:49
@user5032790 using an ORM like EF for bulk operations is wrong, period. The "extension" here is the ORM, not the bulk operation. Parallel operations for such a tiny amount of data will simply create more delays due to locking. There's nothing wrong with SQL Server, it's the method that can't scale – Panagiotis Kanavos May 24 '16 at 09:55
@user5032790 all snippets actually execute a blocking `SaveChanges`. You don't actually use parallel database operations anywhere. You *can't* use the same context/connection from different threads at the same time. – Panagiotis Kanavos May 24 '16 at 10:02

Running an inserting to database task more faster

1 Answers1