CODEWITHSUNDEEP
c#sql-serveref-code-firstasync-awaittask-parallel-library
user5032790
user5032790

Reputation:

Running an inserting to database task more faster

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?

Upvotes: 0

Views: 743

Answers (1)

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66682

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.

Upvotes: 1

Related Questions