Should I open() and close() my SQL database constantly or leave it open?

Question

I am creating an app that uses an SQL database for storing data. The way the app is designed it will be updated every 3 minutes or so with new data depending on user actions while the app is running.

In the tutorials I have seen, they recommend that you close the database after changing it (it is an "expensive" in terms of resources).

Is it better to leave it open for the duration of my App since it is being updated on a fairly frequent basis or should I run the close() method immediately after every change?

I guess my worry is that opening it and closing it constantly will draw more resources than it would to leave it open the entire time.

Answer 1

I tried to leave a connection open once -- I used it to stuff a repeater or something -- can't remember now.

Later in the program, I had another need to use the connection -- I think I had it so that when a user clicked a parent item in the repeater, a detail div would pop-up with more information for that item. This generated an error -- something to the effect of 'cannot open() on an open connection'.

I think the error might have been avoidable another way (like checking to see if the connection I was trying to open was already open), but as I thought about it, I realized I'd have to make that a standard practice throughout my app, and that seemed like too much work, so I just made it a standard practice to always close my connections after each use.

Connections stay in a connection pool -- I'm no whiz on that -- but if was curious about performance, I guess I'd keep that in mind, in terms of what it costs to open a connection multiple times -- whatever your situation is requiring anyway.

Another thought is that your DB admin may be able to force-close all open connections, or the db may close for some other reason. If you're not the dba, you might give a thought to risk/benefit of depending on something that you don't have long-term control over like keeping the connection open.