I have written some Help Desk software for the company I work for. There are about 15 people that currently use this software. Right now, when information is needed from the database I open a connection, retreive/send my data, then close the connection. Is this the optimal way to do this, or could I get away with opening a connection to the database when the application loads, and close the connection only when I leave the application. That way I never have to open a connection again, possibly speeding up the application?? Any advice would be appreciated.
Right now, when information is needed from the database I open a connection, retreive/send my data, then close the connection
I'd say it is "ok" if you only have 15 people using the system, but it is not a very good idea to do this. Opening connections requires system resources. instead, you can use a broker (have you heard about connection pooling?). If you are not using one yet, you simply create this broker with say, 20 connections, and you just get the connection whenever you need to do some database stuff and free the connection when you are done. This is a much, much better approach. www.javaexchange.com has a class that implements connection pooling, very easy to use and free. You might want to give it a try. hope it helps..
I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
With near-on 1000 posts, I'd hope Gregg has heard of Connection Pooling! A general Java development rule is to worry about getting it right first, then worry about performance later. I'd look at opening a connection for each query and closing it after. With 15 users you wouldn't expect the connection performance hit to be too bad, so just take it. Definitely write with pooling in mind though. You may find you need it and don't want to do a complete rewrite to achieve this.
Thanks guys! Yes, I have heard of connection pooling. However, the general consensus regarding connection pooling that I have read on this site is that under 200 users, it's really not a necessity. Along the same lines here, if I open 20 connections using the connection pooling method, and just retrieve one of those connections when I need it, then return it to the queu, are there any issues with leaving connections open for a long period of time like that, say 8 hours?