ADO Global Connection

We have vb and sql server in our project. we are using ado for the database connection. we are having a middle tier dll component which takes care of the sql operations. in our sub main we are creating this connection object and we will be having a global connection throughout... is this efficient.. what problems are we likely to face.. the sql server is giving errors like insufficient memory to process query.. is this due to the global connection ??
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yes, the memory problems are expected. see you may have one global connection, but if many clients are trying to use that single connection, this may increase the queue for request processing, and hence the memory problem once the limit is exceeded.

solution one: use the least possible no. of connections per client. if sequential processing of data is involved, use only one connection per client. in other cases you would have to decide an optimum number depending on the complexity of the application.

solution two: use MTS. this gives you connection pooling and object pooling, which optimizes the available no. of free connections.

hope the above info is helpful.

shyamalaAuthor Commented:
thanx vin32 but i think u got the problem wrong, we dont one single connection for the whole project, every client machine which runs this exe will have one connection for itself. so if 10 machines are accessing the server, then we will have 10 open connections, what do u think of this case ??

There are two solutions for this

1.As vin32 mentioned using MTS

2. The other one is if you are using
ADO, open a connection and close the connection as soon as you are done with it.

And open a global connection always available for the application. This way the "connection pooling" can use the already available connection all the time instead of creating a new connection.
if you open a connection close it explicitly. And open another connection when you need it. Even if you close and open several times there will not be any memory leakage because "connection pooling" will take care of it. If you have any more questions you can look in to the article titled "Pooling in the Microsoft Data Access Components"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.