Database connection pool thr application

anishkannan

Hi... In my web application, we are using oracle database. my question is connection pool based on web application. Our application end user around 500. for these which connection is better i) create new connection for every request of user. ii) single connection pool via singleton object of the class iii) session based connection pool ( each user has separate connection pool) which principle is using for web application? can please give suggestion or valuable link to me --Thanks

Lost User

I think you would get a better response by posting this in the Database forum; there is nothing in your question about C#.

Unrequited desire is character building. OriginalGriff I'm sitting here giving you a standing ovation - Len Goodman

Lost User

anishkannan wrote:

i) create new connection for every request of user.

That's what I'm doing; no problems with connections that are still open/closed/busy. Works quite fast, since connection-objects are returned to the pool. Create a connection, create a command, execute.

anishkannan wrote:

ii) single connection pool via singleton object of the class

Huh?

anishkannan wrote:

iii) session based connection pool ( each user has separate connection pool)

I don't think that every user needs a pool of connections.

Bastard Programmer from Hell :suss:

Lost User

It already in the WebDevelopment[^] forum. Do we really need yet another copy of the question? :-\

Bastard Programmer from Hell :suss:

anishkannan

Thanks for reply... Your suggested option is the open statement execution then close; In Case, the single web request has multiple open and close; it have any problem? i face this : If access multiple user in many days, some times got exception as maximum pool reached in database side... Singleton : i mean that, single open at the time of first ; all statement execution then close

Pete OHanlon

If you're using the ODP.NET libraries, you could be hitting the maximum pool issue because you have unclosed connections - make sure you dispose of your connections as early as possible. Basically, the standard technique is to open the connection as late as possible and dispose of it as early as possible. We had a system with a 1000 users all hitting the Oracle server, and we never needed more than 10 connections in the pool to service them because of this simple mantra.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

anishkannan

We are using our own data access layer. which has

public DataSet ExecuteProcedure(string strProcedure, Hashtable hashTableParameters, string tableName, ref DataSet datasetToFill)
{
try
{
Open();// objConnection = new OracleConnection(strConnection);
// Procedure Execution
// data fill to dataset
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception e)
{

        }
        finally
        {
            Close(); // objConnection.Close();
        }

        return datasetToFill;
    }

Please tell me. if anything wrong in this ( i need correct in this code if has any mistaken ) we are using oracle sql developer client software. how check the connection pool in oracle db.

Lost User

anishkannan wrote:

In Case, the single web request has multiple open and close; it have any problem?

Not that I know of; I have an application that's opening and closing connections at a high rate, and it simply works as advertised.

anishkannan wrote:

Singleton : i mean that, single open at the time of first ; all statement execution then close

That would keep the connection alive for longer than it's required, hogging the amount of available connection-objects.

anishkannan wrote:

i face this : If access multiple user in many days, some times got exception as maximum pool reached in database side...

How long do you keep the connection "open"? If you keep the connection around too long, the pool will be empty quite fast.

Bastard Programmer from Hell :suss:

Lost User

using (objConnection = new OracleConnection(strConnection))
{
// do your stuff here
using (objCommand = objConnection.CreateCommand())
{
}
}
// no need to close or dispose.

Bastard Programmer from Hell :suss:

anishkannan

Okay thanks!!!

Lost User

My pleasure :)

Pete OHanlon

Well, I can point out a couple of things here.

anishkannan wrote:

catch (OracleException ex) { throw ex; }

Completely useless code. Never catch and throw without doing something with it.

anishkannan wrote:

catch (Exception e) { }

You're just consuming the exception here. You have no way of knowing what's going wrong in your code now.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Lost User

Put there after my suggestion; check the times.

Unrequited desire is character building. OriginalGriff I'm sitting here giving you a standing ovation - Len Goodman