Most Efficient Way of Connecting to the Database 1

[phatening]

Hi All,

I'm creating a web application. And I was wondering if anybody knows if there are any major differences between the 2 ways of connecting to a database:
1) connecting everytime there's a call to the database and closing the connection after every call.
2) connecting at the start of a session (when a user logs in) and closing the connection when the session times out or is ended.

I notice that there is less coding in the 2nd option, but does it substantially increase the bandwidth used? Could there be a problem if there are too many connections open?

Thanks,
Spence

 

[jemminger]

google connection pooling.

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[theniteowl]

It has a lot to do with your application really.
You are dealing not only with your web server but also the database server (if they are not one and the same) and resources of both.

If you open a large recordset and keep it open then you may tie up resources on the DB end (depends on what you do and what your DB server is).
If you are using server side code like ASP or PHP to manipulate the data then you use server side resources to hold the data as you work with it or while you are sitting waiting for something to happen.

I personally try to hit the database as infrequently as possible, just grab the data I need then close the connection. You are much less likely to run into conflicts with the data this way as well.

If you are doing lots of read/writes to the database then it makes more sense to keep the connection live. If though data is pulled down and then nothing else done for a fair period of time then just grab and close then reconnect when you need to do more work.

Not much of an answer huh?
There are just too many variables and different DB servers handle different types of data access differently and have their own methods for dealing with performance issues.
In that same respect, server side scripting has it's own benefits and pitfalls for performance so it's really about learning the strengths and weaknesses of your particular configuration and of course keeping in mind where you might be going in the future as well in case it has an effect on your current design.


Paranoid? ME?? WHO WANTS TO KNOW????

 

[phatening]

Well its a mysql db server and I use JSP. I'll probably just close my connections and reopen them when needed. Your right, I'll run into less problems this way.
Thanks theniteowl.