CODEWITHSUNDEEP
c#databaseperformanceweb-applications
user5398447
user5398447

Reputation:

Best practice regarding opening/closing multiple database connections

I have a method in a web-application with a decent amount of code. In almost opposite ends of the method, I have database interaction.

Is it best practice to open/close its connection multiple times or open its connection when first needed/close it when last needed?

Multiple Times

connection.Open();
//execute db interaction
connection.Close();

//execute business logic

connection.Open();
//execute db interaction
connection.Close();

//execute business logic

connection.Open();
//execute db interaction
connection.Close();

//etc...

Open First/Close Last

connection.Open();
//execute db interaction

//execute business logic

//etc...

//execute db interaction
connection.Close();

Upvotes: 3

Views: 2749

Answers (3)

Steve's a D
Steve's a D

Reputation: 3821

The ideal way to go about this would be to grab all of your data in the initial connection. However, if you have a large method with code thats very time consuming, keeping the connection open for the entire duration of the method call is costly.

Because of this it is way more efficient to open and close twice (for the database). The idea behind databases is that you want to open them and close them as quickly as possible so you don't eat up resouces that other users use as well.

I learned this the hard way when I was young and crashed a bunch of servers. Pretty sure you use pooled connections anyway (not sure if you have to manually set that up or not)

Upvotes: 7

Jakub Konecki
Jakub Konecki

Reputation: 46008

http://ericlippert.com/2012/12/17/performance-rant/

If you have two horses and you want to know which of the two is the faster then race your horses. Don't write short descriptions of the horses, post them on the Internet, and ask random strangers to guess which is faster! Even if by sheer chance you got an accurate answer, how would you have any confidence in its accuracy? You can easily and accurately discover which of two programs is faster by running both yourself and measuring them with a stopwatch.

Always close connections as soon as you are done with them, so they underlying database connection can go back into the pool and be available for other callers. Connection pooling is pretty well optimised, so there's no noticeable penalty for doing so. The advice is basically the same as for transactions - keep them short and close when you're done.

Upvotes: 0

Mike C.
Mike C.

Reputation: 3114

I use one connection per method:

public void MyMethod(){
  using(SqlConnection conn = new SqlConnection()){

    ..all of your code

  }
}

Someone more knowledgeable may provide a better answer.

Upvotes: 3

Related Questions