Save Records in Database asynchronously Or Parallely in .net c#

Question

I have a service in which i have to save a large number of records to database after getting from an API. At the same time i have to return those records to from service to the caller. But problem is that i saving records in DB is taking long time so Service is getting slow. I have searched about this and found some conecept of Parallel task OR async & await.

I am new to this concept and getting confused in its usage

I looked into :

Running multiple C# Task Async http://msdn.microsoft.com/en-us/library/hh191443.aspx

But i am not getting what to do.Please help me:

below is code:

public List<SearchedItems> SearchItems(string ItemToSearch, string AuthenticationToken)
{
    var _list= getRecords from Api //100 records    

     //Task<int>.Factory.StartNew(() => _objBLLNutritionLog.FillNutritionTable(_tempList)); // also tried this

    saveToDb(_list); // need to run this asynchronously Or parallel (Taking long time)

    return _list;

}

i want to return result to caller and on other side want to fill db. Please suggest.

Thank you

Answer 1

I hope you are using .NET 4.5.

So let's get started. First mark your SearchItems function with async:

public async List<SearchedItems> SearchItems(string ItemToSearch, string AuthenticationToken)
{
}

Then you can await for saveToDb task:

var result = await Task.Factory.StartNew(()=> saveToDb(_list));

Answer 2

Asynchronsous or parallel execution of database commands will not speed-up bad database code, but it can hurt performance. 100 records is a very small number which means something weird is going on in the database access code. This won't be fixed if you execute multiple statements in parallel.

First, in order for operations to execute in parallel, you need a separate connection for each operation - which results in more locks, more blocking, more time spent going over the wire.

Second, much more time is spent in establishing connections and sending data over the wire than is spent executing an actual INSERT or UPDATE. Even if you execute one statement per record, you shouldn't have any performance problems. If you do, it's probably because saveToDb does something strange, or you don't have the proper indexes, or the statement performs really badly.

Finally, the best way to increase database inserts is to use bulk operations, like SQL Server's BULK INSERT to insert a large number of records in one go, or the SqlBulkCopy class. These operations are optimized to handle a large number of records (many thousands).

In any case, it pays much more to fix the statements and the database access code than to run the statements in parallel.

Answer 3

I would advise against using async-await unless the operation is really asynchronous. If your database client supports async operations then go ahead, but using Task.Factory.StartNew or Task.Run is just offloading the work to a different thread.

You could however run those tasks in parallel, but that will only be faster if your database works in parallel well. If let's say it's an SQL database and you're inserting data to the same table, that will probably lock the table and make your tasks run even slower.

If, though, all you want is to not block the caller and return the result before it was fully inserted to the database you could do this:

public List<SearchedItems> SearchItems(string ItemToSearch, string AuthenticationToken)
{
    var list = getRecords from Api
    Task.Run(() => SaveToDB(list));
    return list;
}

Answer 4

Use the following:

FX db1 = new FX();
LibraryManagementSystemEntities db2 = new LibraryManagementSystemEntities();

Thread thread1 = new Thread(delegate()
{
    db1.insert2(TextBox1.Text,TextBox2.Text);
    db2.insert1(TextBox1.Text,TextBox2.Text);
});
thread1.Start();