Best practice for handling connections / requests
Question about C # best practices. I have a class that iterates over 5000 objects and makes a call to the end of each of these objects:
Cycle progress:
for(Object x in objects) //5000
{
nonStaticObject.callThisMethod(x.id);
}
DAL level:
ReturnObject x = null;
using(SQLConnection...)
{
using(SQLCommand...)
{
// run something here
// if found, instantiate object x
}
}
I did some profiling due to the fact that I have a very high CPU built from my code (parallel for loops) and hotspots seem (and rightfully so) with all the calls I make to the database. And since these calls are very frequent, I have some questions.
In my mind, I think I should reuse the connection if possible. Is this best practice? I want to allow the code to handle the removal of references (using the using keyword), how do I set up an efficient connection with this strategy that can be used for all 5000 calls? Do I have to create a connection for every call to this method like now? (I read somewhere that ADO connection pool reuses connections automatically, so maybe that's already done in the background?).
Thanks for any help
source to share
Typically, with regard to database connections, the policy should be opened as late as possible and closed as early as possible. So let's say if you have one statement / query to execute, then open the connection before executing the query and close it after that.
But , in your case, you are executing 5000 requests in a loop, so there is no point in opening / closing the connection 5000 times. Instead, use one connection and fulfill all your requests.
Also, opening and closing a connection simply returns the connection to the .Net connection pool. If there is an already open connection, then opening the connection with Conn.Open();
will cause that open connection to be returned from the connection pool. See: SQL Server Pooling (ADO.NET) and Creating Database Connections - Do this once or for each query?
source to share