Question Global Connection Object

Tom

Well-known member
Joined
Aug 23, 2005
Messages
746
Programming Experience
5-10
Two part question,

Should a single global connection object in a module be used for the life of the application rather then a ton of local connections?

And if so, should the connection.state remain open or constantly be re-opened & closed after each query?

I'm running an app that will have very few users connected to this specific database. So room for connection pooling shouldnt be an issue.

Speed is priority in the program, the individual queries will be small; return a few records; update the record(s) and print a report.

Although each query is rather of small; this will be done tens of thousands per day. So returning the record(s), updating and sending to the printer as fast as possibile is priority.

I know that it is recommended to use local connection obects and to open & close the objects as fast as possibile. But this does seem to conflict with the time & resources it takes to keep re-creating objects and establishing new connections.

I think it would be faster to have one global connection that remains open even though it seems not to be the recommended way.

Looking for a detailed response to the pro's and cons of both ways.
 
Ask in a new thread if you wonder about multi-threading, it is not related to the topic of this forum thread, it is a general purpose technique for doing work that don't belong in UI thread. I would as always recommend doing research before asking, tutorials are abound.
 
Two part question,

Should a single global connection object in a module be used for the life of the application rather then a ton of local connections?
No. You'll screw yourself up completely in a multithreaded situation of you do this

And if so, should the connection.state remain open or constantly be re-opened & closed after each query?
Opened and closed. Open and close don't work like you think they do - they acquire and release database connections from a pool. This mechanism should be used

Actually the mechanism that should be used is .Net2.0+ TableAdapters and care be taken to create a new TableAdapter on every thread that needs to do a database op. i.e. if you have a method that is accessed by multiple threads, it should not reuse a TableAdapter or you will occasionally get spurious exceptions like "Could not open connection: the current connection's state is Connecting"


I'm running an app that will have very few users connected to this specific database. So room for connection pooling shouldnt be an issue.
Pooling enhances performance and should be used. It is not about server resources, it is about local resources; user count is not irrelevant but largely immaterial here

Speed is priority in the program, the individual queries will be small; return a few records; update the record(s) and print a report.
Then the focus should be on writing good SQLs (because thats the part of the operation that takes seconds or minutes) not messing around trying to shave a few milliseconds (if that) off creating connection objects

Although each query is rather of small; this will be done tens of thousands per day. So returning the record(s), updating and sending to the printer as fast as possibile is priority.
Still, its the query that will take the time, not the creation of the connection. Each client machine will have more than enough CPU resources to create a connection; it takes thousandths of a second, and there are 3600 seconds in an hour.. so tens of thousands is actually quite a low number from this perspective. If your db, however, has 25 users doing 100,000 queries a day your focus should be on making those 2.5million sqls as fast as possible, because SQLs can take seconds, and there are only 84600 of those in a day..

I know that it is recommended to use local connection obects and to open & close the objects as fast as possibile. But this does seem to conflict with the time & resources it takes to keep re-creating objects and establishing new connections.
Do you have any idea how fast a computer does things? Youre looking at the connection creation code and assuming it takes a long time, but I can guarantee you that right now it's the least of your worries. Fix something that's broken! :)

I think it would be faster to have one global connection that remains open even though it seems not to be the recommended way.
Youre fussing over something youre not even sure you need to worry about. The first step to optimizing an application is to identify the slow spot. Fixing it comes after.

Looking for a detailed response to the pro's and cons of both ways.
Hope this has been detailed enough ;)
 
Thanks CJard for providing more detail; even if a little late. As the thread ends you can see I have already reversed my opinion and can see now that creating a single global connection (even while opening & closing it with each query) is not the most optimal way.
 
Back
Top