3

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();
  • Why would you _want_ to close a connection if you know you will be needing it shortly after? – CodeCaster Feb 28 '13 at 15:01
  • Hence, the question... –  Feb 28 '13 at 15:02
  • You are asking us what is faster, I am asking you why you would even want to close it. _"I have two garbage bags I want to dispose of. Should I close the can's lid between dumping the two bags?"_. It depends on what happens in between, on what _you_ do. In general, the answer is: keep it open while you expect to be using it again soon. – CodeCaster Feb 28 '13 at 15:04
  • "Performance" wasn't the best word here. Perhaps I am looking for "best practice." By your comments, "best practice" is to keep the connection open for as long as I need it and close it when I no longer need it. –  Feb 28 '13 at 15:06
  • Correct, but that is, in the scope of a unit of work. If you know that in one method you'll be using a connection multiple times, keep it open. If you don't know what happens when you leave the method, close it (or use `using`), to allow for connection pooling to do its job. – CodeCaster Feb 28 '13 at 15:08
  • Perhaps I call method `X` while my `SqlConnection` is open and method `X` opens the same `SqlConnection`, then I run into an issue. Would a `using` encapsulate this case? If I have no interaction outside the method, keeping the `SqlConnection` open from the first time I need it and close after the last time I need it is efficient. <-- my summary of your comments –  Feb 28 '13 at 15:15
  • 1
    I think it is an interesting question. MikeC below recommends a fresh connection 'per method'. This approach is quite common in the literature and I can see this is a requirement for stateless software. On the other hand I am unsure how much of a sin it is to maintain an open connection for the lifetime of, say, a GUI session. For your specific case 1 connection is adequate. – Hugh Jones Feb 28 '13 at 15:17

3 Answers3

7

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)

Steve's a D
  • 3,801
  • 10
  • 39
  • 60
  • "Way more efficient" _[Original research]_ _[Citation needed]_ – CodeCaster Feb 28 '13 at 15:01
  • Citation - Apress PRO asp.net also suggests this – Steve's a D Feb 28 '13 at 15:05
  • In what context? You say _"open them and close them as quickly as possible"_, which simply isn't always true. If I want to execute N queries where N > 1, do I have to call `Open` before and `Close` after each query? – CodeCaster Feb 28 '13 at 15:09
  • 1
    As quickly as possible means that you should keep it open only long enough to get the data that you need. If you have N queries, you keep it open long enough just to get the N queries. If later on in the application you need to get more data, you open another connection and rinse and repeat – Steve's a D Feb 28 '13 at 15:11
  • I get your point, but look at that question: _"for the full page life cycle"_. This question is about one method. – CodeCaster Feb 28 '13 at 15:12
  • 1
    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. – Steve's a D Feb 28 '13 at 15:15
  • @Steve I ask about a given method, but your second citation is intriguing. Thanks. –  Feb 28 '13 at 15:15
  • 1
    @Steve I fully agree with your last comment, that's what I've been trying to say. :-) – CodeCaster Feb 28 '13 at 15:16
  • @Steve - I think it is significant that your citation is from an ASP source. I am sure that a major factor in this issue is whether the code is server-side. If if is, then it needs to be stateless, and probably is supporting a large number of user requests. So - how much of a sin is it if one is developing a desktop GUI app to open a connection when the app starts, and to close it when the app closes ? – Hugh Jones Feb 28 '13 at 16:04
  • @Steve. Client-side it clearly cannot be a problem, unless perhaps a user decides to open 500+ instances of the app. Also client-side it is just plain daft to keep opening and closing [0..1] connections ... Server-side ??? Does it depend on the rdbms? I have been pondering this for a while and am still uncertain – Hugh Jones Feb 28 '13 at 17:18
3

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.

Mike C.
  • 3,024
  • 2
  • 21
  • 18
0

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.

Jakub Konecki
  • 45,581
  • 7
  • 87
  • 126
  • Maybe "performance" wasn't the best word. "Best practice" is what I'm going for. –  Feb 28 '13 at 15:03
  • 1
    I don't agree with part about horses. Sometimes is better to ask someone experienced. Someone who already know which horse is faster under various conditions. In that example - one horse may be faster than other on long distance, other faster on short distance, one can run faster in rain, other when is good weather. It's hard to test all conditions. – Kamil Feb 28 '13 at 15:08
  • 1
    The issues caused by not doing this properly are very hard to measure. You have the memory used on the client machine, the memory consumed on the server, some means of measuring the throughput of the database connections, time spent idling, on both the client and the server, you of course have execution time, and then there are issues with connection limits being reached, etc. All in all, there's so much more going on here than just "which one takes less time to execute" that actually "racing the horses" would be *very* hard. – Servy Feb 28 '13 at 18:15