Very slow foreach loop

Question

I am working on an existing application. This application reads data from a huge file and then, after performing some calculations, saves the data in another table.

But the loop doing this (see below) takes a lot of time. Since the file sometimes contains 1000 entries, the whole process takes several days.

Is it possible to replace this foreach with something else? I tried using Parallel.ForEach and it helped. I am new to this, so appreciate your help.

 foreach (record someredord Somereport.r) { try { using (var command = new SqlCommand("[procname]", sqlConn)) { command.CommandTimeout = 0; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(…); IAsyncResult result = command.BeginExecuteReader(); while (!result.IsCompleted) { System.Threading.Thread.Sleep(10); } command.EndExecuteReader(result); } } catch (Exception e) { … } } 

After looking at the answers, I uninstalled Async and used the edited code as shown below. But this did not improve performance.

 using (command = new SqlCommand("[sp]", sqlConn)) { command.CommandTimeout = 0; command.CommandType = CommandType.StoredProcedure; foreach (record someRecord in someReport.) { command.Parameters.Clear(); command.Parameters.Add(....) command.Prepare(); using (dr = command.ExecuteReader()) { while (dr.Read()) { if () { } else if () { } } } } } 

Answer 1

Instead of looping the sql connection multiple times, ever consider retrieving an entire dataset from an SQL server and processing data through a dataset?

Edit: decided to explain what I had in mind. You can do the following, pseudo code, as follows

• Use select * and get all the information from the database and save them in the class or dictionary list.
• Make your foreach (write someRecord in someReport) and negotiate the conditions as usual.

Answer 2

Step 1: Retry asynchronously. It is not executed properly and you are blocking anyway. So just follow the procedure and see if it helps.

Step 2: Move SqlCommand outside the loop and reuse it for each iteration. Thus, you do not incur the cost of creating and destroying it for each element in your cycle.

Warning: Make sure that you reset / clear / delete parameters that you do not need from the previous iteration. We did something similar with additional parameters and had a "bleeding" from the previous iteration, because we did not clear the parameters that we do not need!

Answer 3

The biggest problem is that you are fixated on this:

 IAsyncResult result = command.BeginExecuteReader(); while (!result.IsCompleted) { System.Threading.Thread.Sleep(10); } command.EndExecuteReader(result); 

The whole idea of ​​the asynchronous model is that the calling thread (the one that does this loop) needs to deploy ALL asynchronous tasks using the Begin method before starting to work with the results using the End method. If you use Thread.Sleep () in your main call flow to wait for the asynchronous operation to complete (like you are here), you are doing it wrong, and ultimately what happens is that each command one at a time is called and then waits until next next.

Instead, try something like this:

 public void BeginExecutingCommands(Report someReport) { foreach (record someRecord in someReport.r) { var command = new SqlCommand("[procname]", sqlConn); command.CommandTimeout = 0; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(…); command.BeginExecuteReader(ReaderExecuted, new object[] { command, someReport, someRecord }); } } void ReaderExecuted(IAsyncResult result) { var state = (object[])result.AsyncState; var command = state[0] as SqlCommand; var someReport = state[1] as Report; var someRecord = state[2] as Record; try { using (SqlDataReader reader = command.EndExecuteReader(result)) { // work with reader, command, someReport and someRecord to do what you need. } } catch (Exception ex) { // handle exceptions that occurred during the async operation here } } 

Answer 4

In SQL, at the other end of the record is a (one) disk. You can rarely write faster in parallel. In fact, in parallel, this often slows it down due to index fragmentation. If you can sort the data using the primary (cluster) key before loading. Under heavy load, even disconnect other keys, drag and drop data recovery keys.

I'm not sure what he is doing in asingha, but, of course, he did not do what he expected, as he expected it.

 try { using (var command = new SqlCommand("[procname]", sqlConn)) { command.CommandTimeout = 0; command.CommandType = CommandType.StoredProcedure; foreach (record someredord Somereport.r) { command.Parameters.Clear() command.Parameters.Add(…); using (var rdr = command.ExecuteReader()) { while (rdr.Read()) { … } } } } } catch (…) { … } 

Answer 5

As we said in the comments, storing this data in memory and working with it can be more efficient.

So, one easy way to do this is to start with the Entity Framework. Entity Framework automatically generates classes for you based on your database schema. Then you can import the stored procedure that contains the SELECT statement. The reason I suggest importing a stored process into EF is because this approach is usually more efficient than doing your queries in LINQ for EF.

Then run the saved process and save the data in a List like this ...

var data = db.MyStoredProc().ToList();

Then you can do whatever you want with data . Or, as I mentioned, if you look at primary keys a lot, use ToDictionary() something like this ...

var data = db.MyStoredProc().ToDictionary(k => k.MyPrimaryKey);

In any case, you will work with your data in memory at this point.

Answer 6

It seems that executing your SQL command puts the lock on some required resources and that the reason forced you to use Async methods (my guess).

If the database is not used, try using its exclusive access. Even so, some internal transactions, due to the complexity of the data model, consider consulting a database designer.