SQL Connection Open/Close Recommendation 1

[Auguy]

Is there a preferred method of when to open and close the SQL connection? Currently I open and close the connection whenever I hit the database (Select, Update, Delete, etc.). Would it be better to open it once when the program starts and close it when exiting the program? Just wondering what others are doing? Any problems in leaving it open for an entire work day?

Auguy
Northwest Ohio

 

[gmmastros]

My app connects each time I use the database. This strategy has worked well for me. Honestly, I prefer this method because temporary problems with the network may not affect you.

If you plan on doing this and you are using a SQL Server database, then you will want to make sure the database is not set to AutoClose. Basically, databases can be opened or closed. When a database is open, SQL Server will retain data in it's cache. The first time you request data from a table, it needs to go to the hard drive to get it. SQL Server will keep this data in memory so that subsequent requests will operate faster.

When the database is configured for autoclose, SQL Server will remove all the cached data from memory as soon as the last connection to that particular database is closed. When you are creating a connection every time you use the database, it ends up getting closed a lot.

What's worse.... When you use SQL Express and create a new database, the default setting is for AutoClose to be on. This kinda makes sense when you think about how Microsoft expects this database engine to be used. But... it really kills performance, too.

To see if your Microsoft SQL Server database is set to auto close:

Select  'Database set to Auto Close' As Problems
Where   DatabaseProperty(db_name(), 'IsAutoClose') = 1

Make sure you run that in a query window and that the database is set properly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

George, thanks for the autoclose tip.

Auguy
Northwest Ohio

 

[Andrzejek]

I open the connection to my DB (ORACLE) at start of my app, keep it open until user says: "OK, I am out of here" and exits the app. All users are kicked out at night while back ups are done. stored procedures run, etc. It works well for almost 10 years with a few hundred users, all in one state of US.

Have fun.

---- Andy

 

[Sorwen]

I use both ways. Generally it all depends how much work would be done between read/write back to the table. More than 5 minutes and I would open close it.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!