Is it possible - DATA ACCESS LAYER

  • Thread starter Thread starter David Browne
  • Start date Start date
D

David Browne

Bernardo Heynemann said:
I have multiple users accessing my database at all times.
As i am using today, i have one connection that serves all users (ASP 3.0
Application object).
This works fine since i´m using an oracle database server.
The problem is when there are too many concurrent data requests (selects)
some users get timeouts, since oracle uses a queue for each connection.

Then the first 10 users get their request, but the 11th one gets a timeout
(example).

I want to do a Threaded Data Access Layer in .NET that works at ASP 3.0, VB
6.0 or .NET.

It should work this way:
-When the first user requests data, it creates a thread with a connection.
-The N (parameter) following users go to this thread also.
-When the (n+1) user requests data he should be redirected to the second
thread with another connection.
-If there´s an open spot at any of the previous threads the user should be
redirected to that spot in spite of creating a new thread.

Any hints, sugestions, code would be a LOT useful.
Click to expand...

Ok. This problem has been solved. The solution is not perfect, but 99.99%
of the people out there are using it. So you shouldn't write something from
scratch.

The solution is Connection Pooling. Both MTS (COM+) and ADO.NET implement
connection pooling, and it happens 100% transparently to you. You will have
slightly more open connections with Connection Pooling than with your
proposed scheme, but on the other hand you user sessions will never have to
wait for another session to finish its databse activity.

Plus you shouldn't interleave unrelated activity on a single session. It's
impossible to tell whos doing what, database locking doesn't work, and you
can't use package variables or global temporary tables.

To start using connection pooling in ADO or ADO.NET just create a new
connection, open it, use it and close it each time you need to talk to the
databse. Behind the scenes, each thread will check out an open connection,
use it, and return it to the pool.

David
 
I have multiple users accessing my database at all times.
As i am using today, i have one connection that serves all users (ASP 3.0
Application object).
This works fine since i´m using an oracle database server.
The problem is when there are too many concurrent data requests (selects)
some users get timeouts, since oracle uses a queue for each connection.

Then the first 10 users get their request, but the 11th one gets a timeout
(example).

I want to do a Threaded Data Access Layer in .NET that works at ASP 3.0, VB
6.0 or .NET.

It should work this way:
-When the first user requests data, it creates a thread with a connection.
-The N (parameter) following users go to this thread also.
-When the (n+1) user requests data he should be redirected to the second
thread with another connection.
-If there´s an open spot at any of the previous threads the user should be
redirected to that spot in spite of creating a new thread.

Any hints, sugestions, code would be a LOT useful.

Thanx to all,
Bernardo Heynemann
Developer @ Banco BVA S/A - Rio de Janeiro - Brazil
 
Ok. This problem has been solved. The solution is not perfect, but
99.99%
of the people out there are using it. So you shouldn't write something from
scratch.

The solution is Connection Pooling. Both MTS (COM+) and ADO.NET implement
connection pooling, and it happens 100% transparently to you. You will have
slightly more open connections with Connection Pooling than with your
proposed scheme, but on the other hand you user sessions will never have to
wait for another session to finish its databse activity.

Plus you shouldn't interleave unrelated activity on a single session. It's
impossible to tell whos doing what, database locking doesn't work, and you
can't use package variables or global temporary tables.

To start using connection pooling in ADO or ADO.NET just create a new
connection, open it, use it and close it each time you need to talk to the
databse. Behind the scenes, each thread will check out an open connection,
use it, and return it to the pool.

David
Click to expand...

Thanx for your help.
But I had it like that at first and i was getting +10 secs average / request
(since the connection had to be reopen every single time).

I want to know if this scenario is possible:
-A Thread Controller class that manages the threads and open spots at each
thread.
-A ThreadConnection class that derives from thread and has a parameterized
number of open spots and one connection to the database.
-A connection to the database.
-A user class that has an id, a SQL query and the return results and
finalize methods (something like that).

So let´s say that i have the following:
User 1 requested at Time x query q.
User 2 requested at Time x+10 query w.
User 3 requested at Time x+15 query y.

My timeout parameter at the threadConnection class would be let´s say 30s.

Then if Query Q of user 1 exceeds 20 seconds to execute user 2´s query
should be redirected to a new thread.
But if this new thread has a high probability of giving user 2 a new timeout
(Exceed the 30s) again, then the user should be redirected to thread 3 (if
it doesn´t exist it should be created).

Thanx for any help.
Bernardo Heynemann
Developer @ Banco BVA S/A - Rio de Janeiro - Brazil
 
Bernardo Heynemann said:
Thanx for your help.
But I had it like that at first and i was getting +10 secs average / request
(since the connection had to be reopen every single time).
Click to expand...


The whole point of a connection pool is to avoid actually opening the
connection each time.

The connections are held open in a global collection by the driver. Even
though it looks like opening a new connection, you are actually reusing an
existing connection.

I want to know if this scenario is possible:
Click to expand...


Many things are possible, especially in .NET. But it's not worth worrying
about. Connection Pooling works. And it solves your problem.

David
 
Back
Top