• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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 ??
1 Solution
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"

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now