Best Approach to MS EntLib DAAB Database Object

[Abhishek Tripathi]

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

[Nicholas Paldino [.NET/C# MVP]]

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]
- mv*@spam.guard.caspershouse.com

"Abhishek Tripathi" <pl********@gmail.comwrote in message
news:ON**************@TK2MSFTNGP03.phx.gbl...

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 Tripathi]

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.

[Nicholas Paldino [.NET/C# MVP]]

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]
- mv*@spam.guard.caspershouse.com

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.