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.

• Opening a single SqlConnection and keeping it open while application is running and execute all query with that connection

• Each time I want to execute query, opening a new connection and after query execution, close the connection (does this solution suitable for so many scheduled task that runs every 1 second?)

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

How do ORMs like EF manage connections?

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.

CodePudding user response：

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

CodePudding user response：

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.  
    }