Hi,
I am using a code similar to

Database db = DatabaseFactory.CreateDatabase("MyDB")

in every function of my DAL that interacts directly to database. But
recently I have started getting connection pool errors.

I think, it is because of incorrect usage pattern of Database Object of
DAAB. I want to know that what should be the best approach to it. Shall
I declare it as a static object in class and then use it across the DAL
functions ? My concern is that if during an operation DB object is
locked, then other thread pools would not be able to perform any
database activity. My above assumption could be incorrect, but what
should be the best approach(es) for using EntLib from performance pov.
--
"The human mind is a dangerous plaything, boys. When it's used for evil,
watch out! But when it's used for good, then things are much nicer."
— The Tick

Abhishek,

The Database instance shouldn't have to be stored statically. What is
most important is that when you call the methods on the Database which
return object instances which implement IDisposable, you dispose of those
immediately.

I doubt it is related to your Database, but rather, what you do with the
connections returned by calls to the Database object.


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)

"Abhishek Tripathi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I am using a code similar to
>
> Database db = DatabaseFactory.CreateDatabase("MyDB")
>
> in every function of my DAL that interacts directly to database. But
> recently I have started getting connection pool errors.
>
> I think, it is because of incorrect usage pattern of Database Object of
> DAAB. I want to know that what should be the best approach to it. Shall I
> declare it as a static object in class and then use it across the DAL
> functions ? My concern is that if during an operation DB object is locked,
> then other thread pools would not be able to perform any database
> activity. My above assumption could be incorrect, but what should be the
> best approach(es) for using EntLib from performance pov.
> --
> "The human mind is a dangerous plaything, boys. When it's used for evil,
> watch out! But when it's used for good, then things are much nicer."
> — The Tick

Yes, I have identified a few things myself like DataReader objects which
are carried upto business layer, which I would be probing for today.
If I am right about your suggestion regarding disposing dataobjects, you
intend to say that I execute Dispose(dataobject), which would attempt to
run GC to clear out the object from heap. Since the application has to
meet heavy load, how good it would be to call GC frequently ?
Also, what should be an ideal thread pool size for the hosted
application and, also for the database connections, if at anytime there
are 2000 clients hooked up on the server performing data tasks every 1
minute. If by any chance other technical details matter, then we are
using Windows Server 2003, and
SQL 2005 as backend.

Nicholas Paldino [.NET/C# MVP] wrote:
> Abhishek,
>
> The Database instance shouldn't have to be stored statically. What
> is most important is that when you call the methods on the Database
> which return object instances which implement IDisposable, you dispose
> of those immediately.
>
> I doubt it is related to your Database, but rather, what you do
> with the connections returned by calls to the Database object.
>
>

Abhishek,


> Yes, I have identified a few things myself like DataReader objects which
> are carried upto business layer, which I would be probing for today.
> If I am right about your suggestion regarding disposing dataobjects, you
> intend to say that I execute Dispose(dataobject), which would attempt to
> run GC to clear out the object from heap.

Yes, you should call Dispose on anything that implements IDisposable.

However, you should be aware that if the implementation of IDisposable
adheres to the spirit of the contract (as well as the guidelines for how to
implement IDisposable from MS), then it will ^not^ call GC. It just
releases the (usually) critical resources that the object represents. In
this case, it is the connection t the database.

> Since the application has to meet heavy load, how good it would be to call
> GC frequently ?

No, absolutely not. Generally, calling GC on your own is a bad thing.
Additionally, if your app is meeting heavy load, and that load is consistent
in its usage profile, then the GC is going to get into a good grove and
initiating a collection on your own would mess with that (ASP.NET is a good
example of an app that does this).

> Also, what should be an ideal thread pool size for the hosted application

That completely depends on what you are doing in the thread pool. If
you are performing operations of a long-running nature, then you shouldn't
be using the thread pool. However, if the operations are short, then the
thread pool is fine. The thread pool tunes itself, and you should probably
let it do that on its own (unless through profiling you see that this is a
bottleneck and you thnk you can do better with a more specific
implementation).

> and, also for the database connections, if at anytime there are 2000
> clients hooked up on the server performing data tasks every 1 minute.

Again, this depends on the tasks that you are performing. If you are
running tasks against the DB that are very quick, then you should just get
an open connection, perform the operation, and then close it.

SQL Server should be able to handle that load though.


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)

If by any chance other technical details matter, then we are
> using Windows Server 2003, and
> SQL 2005 as backend.
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>> Abhishek,
>>
>> The Database instance shouldn't have to be stored statically. What is
>> most important is that when you call the methods on the Database which
>> return object instances which implement IDisposable, you dispose of those
>> immediately.
>>
>> I doubt it is related to your Database, but rather, what you do with
>> the connections returned by calls to the Database object.
>>
>>

That was nice answer. Thanks !

Nicholas Paldino [.NET/C# MVP] wrote:
> Abhishek,
>
>
>> Yes, I have identified a few things myself like DataReader objects
>> which are carried upto business layer, which I would be probing for
>> today.
>> If I am right about your suggestion regarding disposing dataobjects,
>> you intend to say that I execute Dispose(dataobject), which would
>> attempt to run GC to clear out the object from heap.
>
> Yes, you should call Dispose on anything that implements IDisposable.
>
> However, you should be aware that if the implementation of
> IDisposable adheres to the spirit of the contract (as well as the
> guidelines for how to implement IDisposable from MS), then it will ^not^
> call GC. It just releases the (usually) critical resources that the
> object represents. In this case, it is the connection t the database.
>
>> Since the application has to meet heavy load, how good it would be to
>> call GC frequently ?
>
> No, absolutely not. Generally, calling GC on your own is a bad
> thing. Additionally, if your app is meeting heavy load, and that load is
> consistent in its usage profile, then the GC is going to get into a good
> grove and initiating a collection on your own would mess with that
> (ASP.NET is a good example of an app that does this).
>
>> Also, what should be an ideal thread pool size for the hosted application
>
> That completely depends on what you are doing in the thread pool. If
> you are performing operations of a long-running nature, then you
> shouldn't be using the thread pool. However, if the operations are
> short, then the thread pool is fine. The thread pool tunes itself, and
> you should probably let it do that on its own (unless through profiling
> you see that this is a bottleneck and you thnk you can do better with a
> more specific implementation).
>
>> and, also for the database connections, if at anytime there are 2000
>> clients hooked up on the server performing data tasks every 1 minute.
>
> Again, this depends on the tasks that you are performing. If you are
> running tasks against the DB that are very quick, then you should just
> get an open connection, perform the operation, and then close it.
>
> SQL Server should be able to handle that load though.
>
>

--
"Congratulations are in order for Tom Reid. He says he just found out he is
the winner of the 2021 Psychic of the Year award." — Seen on slashdot