I've a problem)
I try to reproduсe something like several sp (stored procedure) calls in my c# code, but i want to do it in async way.
TSQL example:
( Execute sp @key = 15072000173475; Execute sp @key = 15072000173571; ... Execute sp @key = n;
)
[TestClass]
public class UnitTestNomenclature {
[TestMethod]
public void ParallelSQLMethod() {
Task scropeTasks = null;
//real amount is more then 1500
long[] keys = new long[] {15072000173475,15072000173571 ... n };
try {
var tasks = keys.Select( i => Task.Run(async () => { await RunStoredProc(i); }));
scropeTasks = Task.WhenAll(tasks);
scropeTasks.Wait();
} catch (Exception ex) {
Debug.WriteLine("Exception: " + ex.Message);
Debug.WriteLine("IsFaulted: " + scropeTasks.IsFaulted);
foreach (var inx in scropeTasks.Exception.InnerExceptions) {
Debug.WriteLine("Details: " + inx.Message);
}
}
Assert.AreEqual(1, 1);
}
public async Task RunStoredProc(long scollNumbParam) {
const string strStoredProcName = @"[dbo].[sp]";
using (SqlConnection conn = new SqlConnection(@"data source=SERVER;initial catalog=Db;integrated security=True;Trusted_Connection=Yes;")) {
await conn.OpenAsync();
Debug.WriteLine("============================================ Connection is open: ==============================================");
// info
Debug.WriteLine(String.Format("Connection: {0}", conn.ClientConnectionId));
Debug.WriteLine(String.Format("State: {0}", conn.State.ToString()));
using (SqlCommand cmd = new SqlCommand(strStoredProcName, conn) { CommandTimeout = 120, CommandType = CommandType.StoredProcedure }) {
SqlParameter scrParam = new SqlParameter() {
ParameterName = "@KEYKRT",
Value = scollNumbParam,
SqlDbType = SqlDbType.BigInt
};
cmd.Parameters.Add(scrParam);
Debug.WriteLine("Start of Proccesing: " + scollNumbParam);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
Debug.WriteLine("End of Proccesing: " + scollNumbParam);
}
}
Debug.WriteLine("============================================ Connection is closed: ==============================================");
}
}
This's what i get in output window:
========== Connection is open: ========
Connection: 5be9c681-6eb5-422f-a22c-b49689a2d912
State: Open
Start of Proccesing: 15072000173475
========== Connection is open: ==========
Connection: cfb66041-6646-4b56-be1c-2afb26a18cb8
State: Open
Start of Proccesing: 15072000173571
.....
End of Proccesing: 15072000173475
=========== Connection is closed: =========
End of Proccesing: 15072000173571
=========== Connection is closed: =========
....
A timeout occurred while waiting for memory resources to execute the query in resource pool 'default' (2). Rerun the query.
Actual error number: 8645
Actual line number: 98
Also debug says that connection pool is overflowed I think main reason that connection doesn't appropriatly dispose, but how i can achive that with async?
If i try just open one connection before the async tasks are declared and pass this to my RunStoredProc method then i get connection doesn't support MultipleActiveResultSets
using (SqlConnection conn = new SqlConnection(@"data source=SERVER;initial catalog=Db;integrated security=True;Trusted_Connection=Yes;)) {
conn.OpenAsync();
var tasks = keys.Select(i => Task.Run(async () => { await RunStoredProc(i, conn); }));
scropeTasks = Task.WhenAll(tasks);
scropeTasks.Wait();
}
Debug.WriteLine("========== Connection is closed: ==========");
This's what i get in output window:
Connection: 5be9c681-6eb5-422f-a22c-b49689a2d912
State: Open
Start of Proccesing: 15072000173475
======= Connection is open: =============
Connection: cfb66041-6646-4b56-be1c-2afb26a18cb8
State: Open
Start of Proccesing: 15072000173571
========= Connection is open: =========
Async unit tests that return Task have none of the problems of async unit tests that return void. Async unit tests that return Task enjoy wide support from almost all unit test frameworks.
NUnit supports out of the box asynchronous methods by wrapping properly executing them in an asynchronous context and unwrapping eventual exception raised by the code itself or by failed assertions. To make sure the runner properly waits for the completion of the asynchronous tests, these cannot be async void methods.
Unit testing is an essential part of the software development process that helps you ensure the high quality of your product: it allows developers to check the performance of each unit and prevent possible problems in advance.
You have 1500 or so tasks all executing at the same time and also mixing async and blocking calls ( like .Wait
) which can cause deadlocks.
Make the test async and try to avoid async void
unless it is on an event handler.
Try iterating them in sequence. It will take longer but at least the connections will get disposed properly so as not to overload the resources. You could also consider doing them in reasonably sized batches.
[TestMethod]
public async Task ParallelSQLMethod() {
//real amount is more then 1500
var keys = new long[] {
15072000173475,
15072000173571,
//....., n
};
var tasks = keys.Select(i => RunStoredProc(i));
var batchSize = 50; //Or smaller
//run tasks in batches
var sequence = tasks;
while (sequence.Any()) {
var batch = sequence.Take(batchSize);
sequence = sequence.Skip(batchSize);
await Task.WhenAll(batch);
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With