Save records to database asynchronously or Parallely in .net C #

Question

I have a service in which I have to save a large number of records in the database after retrieving from the API. At the same time, I have to return these entries from the service to the caller. But the problem is that I save the records in the database, it takes a lot of time, so the service slows down. I searched about this and found some cone of the parallel as OR task async and wait.

I am new to this concept and confused about its use

I looked:

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

But I do not understand what to do. Please help me:

below is the 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 the result to the caller, but on the other hand I want to fill in db. Please suggest.

thanks

Answer 1

Hope you are using .NET 4.5.

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

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

Then you can await to execute the saveToDb task:

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

Answer 2

Asynchronous or parallel execution of database commands will not speed up bad database code, but can hurt performance. 100 entries is a very small number, which means that something strange is happening in the database access code. This will not be fixed if you execute multiple statements in parallel.

Firstly, in order for operations to be performed in parallel, a separate connection is required for each operation, which leads to more locks, more locks, and longer time spent on the wire.

Secondly, much more time is spent on establishing connections and transferring data via cable than it did on actual INSERT or UPDATE . Even if you execute one write statement, you should not have performance issues. If you do this, it's probably because saveToDb doing something weird, or you don't have the appropriate indexes, or the statement really works very poorly.

Finally, the best way to increase database insertions is to use bulk operations, such as SQL Server BULK INSERT , to insert a large number of records at a time or SqlBulkCopy . 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 access code for the database than to perform parallel operations.

Answer 3

I would advise using async-await if the operation was not asynchronous. If the database client supports asynchronous operations, then use Task.Factory.StartNew or Task.Run , just offloading the work to another thread.

However, you can perform these tasks in parallel, but it will only be faster if your database is running in parallel. Assuming this is an SQL database and you are inserting data into the same table, this will probably lock the table and make your tasks even slower.

If, however, all you need to do is not block the caller and return the result before it is fully inserted into the database, you can 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();