General discussion


How to Manage Multiple Users through single Connection

By Silly ·
I have heard that when ever a database user executes a query it opens a new connection for that.
It means that as many no of users are, that many connections will be.

can any body help me control/Manage all the users through single connection.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

A new connection ?

by Tony Hopkinson In reply to How to Manage Multiple Us ...

depends. open a connection, make it persistent and then pass all db action s through it and it won't.
each application will have it's own connection, or many if you wish.
The way to do what you want it to write a service that sits in betweeen sql server and your clients. Clients connect to your service, its connects once to sql server.

Are you up on threading and TCP/IP services ?

You'll learn a lot doing this , presumably you are not talking VB6 as it's support is threading is well awful.

Collapse -


by Silly In reply to A new connection ?

can u help me handle it through stored procedure
or any thing in vb6?

Collapse -

Yiou need a connection to run a stored procedure

by Tony Hopkinson In reply to Re:

I had one very quick go at threading in VB, all articles I found recomended against it and they were right.

Stored proces won't help you, you need a connection to run them.

Important question why do you want to limit the number of connections ? What problem are you trying to solve?

Collapse -

Re.........Single Connection

by Silly In reply to Yiou need a connection to ...

1) i want to create a DCOM in VB that will handle
multiple users
For Example:
a connection will open only when the very first user connects to a database and close only when the last user disconnects of it.

2) To reduce connection Overheads from my server.

Collapse -

Hmm the overhead of a connection

by Tony Hopkinson In reply to Re.........Single Connect ...

is opening it, after that it's fairly minimal. Unless you are trying to get round a per seat limit on the DBMS.

Depends on what your clients are doing. But this is a lot of wheel re-inventing.

I would not even attempt this in VB, you are just asking for it. Without threading your solution would be more expensive than the problem and as I've stated threading support in VB is effectively non existant. This would be a real kick *** project in C#, Delphi or C++.

VB, non starter. Totally, look up threading in VB6 on google.

Collapse -

Mult users, one connection

by tjc_tek In reply to A new connection ?

create a web service and distribute all data over http to the web service. The web service will generate one pooled connection.

Collapse -

It's a way of doing it

by Tony Hopkinson In reply to Mult users, one connectio ...

This assumes while there are many users there is only one role.
the web service closes the connection and opens it with a different role user.
you are going to implement user management through the web service and then map to one or more db users.
you are going to deal with 'permissions' through application logic and remove a very useful devloper crutch that DBs implement which would preclude certain classes of coding error.

If the first is viable, go for it, it works beautifully.

Collapse -

Look Up Connection Pooling

by admin In reply to How to Manage Multiple Us ...

A lot (not all, but a lot) of the overhead of creating connections occurs on the server-side. Look up connection pooling, as it might eliminate some of the overhead. Also you might look at maintaining a set number of persistent connections and reusing them client-side. The client-server communication/handshaking and memory allocation/deallocation operations in opening and closing connections account for a lot of the connection overhead on the client side. I.e., this pattern is fairly slow (pseudo-code algorithm for demo purposes):

Loop 10 Times
:pen Connection to SQL Server
::::Insert a row in a table
::Close Connection to SQL Server
End Loop

The following is much faster than the above example, because the connection overhead is only encountered once, instead of ten times:

Open Connection to SQL Server
::Loop 10 Times
::::Insert a row in a table
::End Loop
Close Connection to SQL Server

You could extrapolate this pattern to a web service, DCOM object or whatever else that requires connection to a SQL database; i.e.:

User connects to web service
:pen connection to SQL Server
::::Insert a row in a table
::Close connection to SQL Server
User disconnects from web service

The above might run a lot more slowly than opening a connection to the SQL Server one time and using that same connection for all separate web service requests.

Related Discussions

Related Forums