leebarnard

Is it better to open an ADO connection object at the start of the application and keep it open throught the life of the app or is it better to open when needed and close when done?

Are there performance issues with either method?

Bart1123

I have read in many instances, in the forum, that the best practice is to open the ADO connection, get the information you need and then close it. From personal expierience I agree with this method. I am unaware of any performance issues with either method, I have inherited several apps where this practice is not used and have not seen any difference in app speed. However opening a connection only as long as you need it makes the code easier to follow in my opinion.

just my CHF.02524

Bart

loquin

It really depends upon the situation.

If your requirements stress response time, including connection time, over all else, then open the connection and leave it open for the life of your app.

If you are limited in the number of connections, either through server licensing issues, throughput throttling (as with MSDE,) multiple user limits (as with Access) or server resource limitations (any server based database, as each connection requires memory and/or processing) then you evaluate the life of your connections in your design. This is the case in most business situations, btw.

Gary

I asked a similar question some time ago. Check it out here.

Unless I need to keep the connection open, like to update or delete multiple items, I never open a connection. (See the example in the above post.)

--Gary

leebarnard

I have dont it both ways and it of-course works I was just never sure what was the "proper" way of doing it. Frankly I assumed that opening and closing a connection every time i needed to query would cause unnecessary overhead. I believe you guys are telling me that is not necessarily the case but it is more appropriate to open when it you need it and close it when done.

HardCode

Also, licensing will determine the way for you to do it. Usually, a license is for X number of concurrent connections. If you have many users and few connections, then opening the connection, doing your thing, then closing it, will allow you to have many more people "virtually simultaneously" access the DB. All within that type of license agreement.

Think of having Microsoft SQL Server as a back end. You can run a web site with a 25 user license, but you don't need 8 gazillion licenses because a connection is only made for the loading of a page. Then it closes and is available for another user.