CODEWITHSUNDEEP
c#sql-serverperformancedatabase-connectionconnection-pooling
Aliasghar Ahmadpour
Aliasghar Ahmadpour

Reputation: 435

How to manage SqlConnection in C# for high frequency transaction?

I have an application that connect to a SQL Server database with high frequency. Inside this service, there are many scheduled tasks that run every second, and each time I'm executing some query.

I don't understand which solution is better in this condition.

I tried second solution, but is there any better choice?

How do ORMs like EF manage connections?

Background Service Manager

As you see i have many service. I cant change interval and the interval is important for me. but the code makes so many calls and im following a better way manage connection over database. Also I'm making connection with Using Statement.

Is there any better solution?

Upvotes: 1

Views: 1077

Answers (2)

nWorx
nWorx

Reputation: 2155

you should use SQL Connection Pool feature for that. It automatically manages in the background if a connection needs to be open or can be reused. Documentation: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling?source=recommendations

Example copied from that page

using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // Pool A is created.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=pubs"))  
    {  
        connection.Open();
        // Pool B is created because the connection strings differ.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // The connection string matches pool A.  
    }

By using the "using" statement, application checks if a connection in this pool can be reused before opening a new connection. So the overhead of opening and closing the connections disappears.

But after your last edit you seem to have other problems in your current architecture. Like the other poster recommends you can try to use the "with (nolock)" parameter in your sql statements. It creates dirty reads, but maybe that's ok for your application.

Alternatively if all your services use the same select statement maybe a stored procedure or a caching mechanism could help.

Upvotes: 3

Mark Redfern
Mark Redfern

Reputation: 507

I assume that you are already opening/closing your SQL connections in either a "using" statement or explicitly in your code ( try/catch/finally ). If so you are already making use of connection pooling as it is enabled in ADO.Net by default ("By default, connection pooling is enabled in ADO.NET").

Therefore I don't think that your problem is so much a connection/resource problem as it is a database concurrency issue. I assume it to be either 1 of 2 issues :

  1. Your code is making so many calls to the SQL server that it is exhausting all the available connections and nobody else can get one
  2. Your code is locking tables in SQL that is causing other code/applications to timeout

If it is case 1, try and redesign your code to be "less chatty" to the database. Instead of making several inserts/updates per second, perhaps buffer the changes and make a single insert/update every 3-5 seconds in batch mode ( obvs if possible ). Or maybe your SQL statements are taking longer than 1 second to execute and you are calling them every second causing in a backlog scenario?

If it is case 2, try and redesign the SQL tables in such a way that the "reading" applications are not influenced by the "writing" application. Normally this involves a service that periodically writes aggregated data to a read-only table for viewing or at very least adding a "WITH(NOLOCK)" hint to the select clauses to allow dirty reads ( i.e. it wont lock the table to read, but may result in slightly out of date dataset i.e. eventual consistency )

Good luck

Upvotes: 2

Related Questions