By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 711 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

How Many SQL Connections Should I Use?

P: n/a
Hello,

I was wondering what the best practices are as far as how many
connections one should use for a data-driven app. Here's my situation:
I have a business object that I load data into, this object also has 3
collections which I also load data into (via their own method). Right
now each method has its own connection that uses a datareader to add
the items to the collection. I can't use the same connection between
the parent object and my 3 collections because datareaders require
their own connection. Is it ok to open 4 seperate connections just to
load one object of data?

What is the best practice? Anybody have any links where this situation
is discussed? Any help would be appreciated. Thanks!

Mar 15 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Thus wrote db.guru,
Hello,

I was wondering what the best practices are as far as how many
connections one should use for a data-driven app. Here's my
situation:
I have a business object that I load data into, this object also has
3
collections which I also load data into (via their own method). Right
now each method has its own connection that uses a datareader to add
the items to the collection. I can't use the same connection between
the parent object and my 3 collections because datareaders require
their own connection.
Depending on what Database you use, using multiple readers over a single
connection is possible. SQL Server 2005 does it ("MARS"), as does DB2.
Is it ok to open 4 seperate connections just to
load one object of data?


I assume thise 4 data retrieval methods aren't executed in parallel? Do keep
all your connections open?

Don't. Simply get a connection, access the database, and close the connection.
Your ADO.NET provider will (hopefully) implement a connection pool under
the hood, so you don't have to pay the penalty for reestablishing a physical
connection each time.

Cheers,
--
Joerg Jooss
ne********@joergjooss.de
Mar 15 '06 #2

P: n/a
Use as few as possible, and close them as quickly as possible.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer

Presuming that God is "only an idea" -
Ideas exist.
Therefore, God exists.

"db.guru" <aa*********@gmail.com> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com...
Hello,

I was wondering what the best practices are as far as how many
connections one should use for a data-driven app. Here's my situation:
I have a business object that I load data into, this object also has 3
collections which I also load data into (via their own method). Right
now each method has its own connection that uses a datareader to add
the items to the collection. I can't use the same connection between
the parent object and my 3 collections because datareaders require
their own connection. Is it ok to open 4 seperate connections just to
load one object of data?

What is the best practice? Anybody have any links where this situation
is discussed? Any help would be appreciated. Thanks!

Mar 16 '06 #3

P: n/a
"db.guru" <aa*********@gmail.com> wrote:
[Snip all content, as the subject says it all]

You should not know, or activly manage, the number of SQL Connections you
use.

Let ADO.Net connection pooling deal with this for you.
1 - Open Connection.
2 - Use and Abuse.
3 - Dispose Connection.

Let the pool deal with "do I really make a new connection?" and "How many
active connections do I have?". If you really, really want to get tricky you
can use the new MARS features, but your scenario probably doesn't really
demand it.

--
Chris Mullins
Mar 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.