April 22, 2009 at 1:20 pm
I have a .net application process which polls a database every couple of seconds. I'm wondering if it'd make more sense to leave the connection open until its time to close the app, or open/close each time?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 1:27 pm
I would leave it open. Don't know complications with .Net application but going by standards of SQL Server, Auto-Close is treated as a bad performance option on an OLTP database.
But, again it depends.
SQL DBA.
April 22, 2009 at 1:28 pm
BP is to close the connection.
If you have connection pooling enabled, windows will keep the ( logically closed ) connection open (I think by default 60 seconds).
So if you need the connection again within 60 seconds, the connection will not need that much time to get re-established.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 22, 2009 at 1:30 pm
ahh conflicting answers.. which is how i've been
Yes, it does not take much to reestablish the connection, however, if there is a performance boost to not closing it in the first place, then that seems more logical. However, best practice would be to close it as soon as I'm done.
arrghh
🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 1:40 pm
BP is definitely to close the connection. Connection pooling will handle retrieving the connection should your application need it again. Leaving an unused connection open can cause the pool to run out if there are lots of people connecting (i believe the max is 99 by default). Additionally, when the application closes you should explicitly set the connection to null. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 1:46 pm
well the other thought would be that if my connection were to blip.. i'd have to handle a reconnect in my code anyway..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 1:56 pm
It depends on how you have your database access structured inside your app.
If this is a web application you want to utilize the connection pooling and to close the connection after each call.
On WinForms app it can go either way. If you have one connection object defined globally you can keep the connection open all the time. If your app is not multithreaded then probably all you need is one connection anyway and connection pooling is not needed.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 2:00 pm
in this case, its a winForms app. I have 2 database objects, one to connect to one particular database, and another which can connect to various different databases.
I think I'll approach this as open/close as much as possible, though I'll be interested if anyone can give me a definitive answer of what approach should be. It seems to be much more personal preference than anything.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 2:05 pm
I would have to agree with JacekO about the windows app. Can you tell I write web apps all day? It is not as critical in a Windows app but if this is an enterprise level app with lot of users connecting you may still run into issues. I have seen cases where connections were not closed in a windows app and there were more people than connections. This forced some people to have to wait for a connection. If the number of concurrent users is limited it probably doesn't make much difference either way. Just make sure to destroy the connection when the app closes. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:08 pm
Yep I'm with ya.. this should really only ever have 1 user, however, best laid plans and all that.. im sure I'll say that now and then all of a sudden theres a dozen or so connecting to my app.. seen it happen before.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 2:09 pm
I do not think this is a 'more personal approach' issue. It depends on the partcular case you want to solve. In most cases using the connection pooling is desirable but it does not have to be.
In some cases you may want to keep the connections open all the time and in a WinForm app there is no apparent penalty for doing so.
I think in your case, if you connect to different databases you will want to use the connection pooling because I think it will allow to create an independent pool for each database and lower the penalty of reopening the connection for each database.
Sometimes it is hard to give the definitive answer because the question asked does not reveal the 'whole truth'. For example in your original post you did not mention anything about the multiple databases you connect to.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 2:19 pm
That is a good point JacekO..
To clarify, I was thinking about this question in relation to an individual app, however, I was interested to know just from a theoretical thing.
The multiple connection issue I was referring to..
I have a single database that will be polled continually, this database will never change and could easily have an open connection and stay open.
I also have another database object which will connect to different databases and poll as necessary, probably every 5 seconds or so for an indeterminate amount of time.. it could be for as short as 1 poll period, or it could be as long as an hour or more if the user wanders off to make a cup of tea. Then the user can select a different database and it will need to connect to there and poll for as long as the user deems necessary.
So two different scenarios within the same application, however, at the end of the day, the same result. I poll a database (for however long) on a set interval. When/If the database is changed, then I'd close and reopen the connection if it were a continually open connection, or I'd just change the connection string for the next open/close if I went down the route of closing the connection when I'm done with it.
I was really looking for a definitive reason one way or the other, but it seems to be a little more murky than that.. glad I asked though.. it certainly brings different issues to light, and as always, the more I read about it, the better off I'll be. 🙂
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 22, 2009 at 2:23 pm
torpkev (4/22/2009)
now and then all of a sudden theres a dozen or so connecting to my app
I do not know if this comment references the application in question but one reason for this might be the fact that you are using connection pooling and connect to multiple databases. With the connection pooling you will have as many pools as there is different connection strings per app. And the funny part is closing those connections will not disconnect you from the database becasue the connection pool will keep the physical connection open for future use.
I would suggest you read more on the connection pooling to find out how your particular situation can benefit or be harmed by them.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 2:26 pm
JacekO (4/22/2009)
torpkev (4/22/2009)
now and then all of a sudden theres a dozen or so connecting to my appI do not know if this comment references the application in question but one reason for this might be the fact that you are using connection pooling and connect to multiple databases. With the connection pooling you will have as many pools as there is different connection strings per app. And the funny part is closing those connections will not disconnect you from the database becasue the connection pool will keep the physical connection open for future use.
I would suggest you read more on the connection pooling to find out how your particular situation can benefit or be harmed by them.
I meant more that I'll probably end up with multiple people using the application at once, not so much the number of connections jumping up unexpectedly.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply