Parallelism in Sql Server with C#

Question

I am implementing async methods in C# and would like to know how to proceed when I arrive in Sql.

When I open a connection to sql server, does it allow me to parallelize through just one connection? That is, I can send him several queries that he executed in parallel?

If yes, how many parallel queries can he do in parallel?

If not, I need several different connections so that he understands that he can do everything at the same time?

for any of the answers I need to configure something in sql or queries(whether they are SP or Commands)?

Answer 1

According to this link (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming), if its version . Net less than 4.5, then it is necessary to inform Asynchronous Processing=true in your Connection object. Otherwise Sqldatareader has async methods for read/write.

When I open a connection to sql server, does it allow me to parallelize through just one connection? That is, I can send him several queries that he executed in parallel?

Yes

If yes, how many parallel queries can he do in parallel?

I don’t have an exact number, but probably the limit of simultaneous queries that sql server supports

for any of the answers I need to configure something in sql or queries(whether they are SP or Commands)?

From what I understand who will wait for the asynchronous answer would be your application C#, and not SQL Server itself, so who should do the async query is the client and your SP/Trigger should be written in the default way.

There are other considerations that I won’t go into deeply here, because it wasn’t the focus of your question, like Polling and Events, which are the ways to wait for a query to be executed. In this particular case I suppose it is Event by callback and await.

Example (If version . Net < 4.5):

static void Terminou(IAsyncResult result) {
// callback que será invocado
}

void main() {
    // Código principal
    IAsyncResult result = command.BeginExecuteReader(new AsyncCallback(Terminou));
}

Example (Version > 4.5):

SqlDataReader reader;
await reader.ReadAsync(); // aguarda retorno assíncrono

Answer 2

Fernando, Sql Server accepts that several queries are made for a single connection. The biggest question here is: which operation will you perform in parallel? When there is competition in the database (multiple simultaneous accesses) Locks/locks can occur in addition to Deadlocks. If they are read operations, see if there is a need to perform clean readings (i.e., with the most recent position of the bank, waiting for no blocking in the tables to perform the reading), if there is no need, use (nolock) in the from and be happy. Now, if they are inclusion, amendment and delete, There may be many Locks and deadlocks and your bank will crash. In this case, you can repress the changes during parallel processing on the application side and apply the changes on the batch bench.