OPINION time... on Sql Connections

[Natty Gur]

Hi,

Let’s say that you have really busy server that serve 500 request per
second. If you keep connection for every user you will end up with 500
open connections to the DB. Holding connections to the DB result in
operation and data allocation on the DB that will cause the DB to
produce poor performance.

The best practice is to hold the connection as short as possible
particular when the CLR maintain connection pooling for you.

Natty Gur, CTO
Dao2Com Ltd.
28th Baruch Hirsch st. Bnei-Brak
Israel , 51114

Phone Numbers:
Office: +972-(0)3-5786668
Fax: +972-(0)3-5703475
Mobile: +972-(0)58-888377

Know the overall picture


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Yan-Hong Huang[MSFT]]

Hello David,

Your solution is not scaleable. If there are over 1000 users visiting the
page, then there will be 1000 dababase connections simultaneously, which is
unacceptable. Database connections represent a critical, expensive, and
limited resource, particularly in a multi-tier Web application. It is
imperative that you manage your connections correctly because your approach
can significantly affect the overall scalability of your application.

For DB connections in web application, we need to open database connections
late, and close them early. The scalability of an application is
controlled by database connection pooling, because a limited number of
database connections can service a much larger number of clients. Database
connection pooling enables an application to reuse an existing connection
from a pool, instead of repeatedly establishing a new connection with the
database. This technique also improves performance, because the significant
time required to establish a new connection can be avoided.

I suggest you refer to MSDN topic ".NET Data Access Architecture Guide" for
details.
[url]http://msdn.microsoft.com/architecture/application/default.aspx?pull=/librar[/url]
y/en-us/dnbda/html/daag.asp

Hope it helps.

Best regards,
yhhuang
VS.NET, Visual C++
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Got .Net? [url]http://www.gotdotnet.com[/url]
--------------------
!From: "David Waz..." <dlw@pickpro.com>
!Newsgroups: microsoft.public.dotnet.framework.aspnet
!Subject: OPINION time... on Sql Connections
!Lines: 16
!X-Priority: 3
!X-MSMail-Priority: Normal
!X-Newsreader: Microsoft Outlook Express 6.00.3790.0
!X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.3790.0
!Message-ID: <PclNa.871$6i6.414@newssvr23.news.prodigy.com>
!NNTP-Posting-Host: 65.23.4.76
!X-Complaints-To: [email]abuse@prodigy.net[/email]
!X-Trace: newssvr23.news.prodigy.com 1057349551 ST000 65.23.4.76 (Fri, 04
Jul 2003 16:12:31 EDT)
!NNTP-Posting-Date: Fri, 04 Jul 2003 16:12:31 EDT
!Organization: Prodigy Internet [url]http://www.prodigy.com[/url]
!X-UserInfo1:
FKPO@MC@@S@[R]LY]JM\_TLA^R_FBL@MAHU^_BAMEH]TCDYG^WH[AACY@TZZXQ[KS^ESKCJLOF_J
_NGAWNTG^_XGTNTAHULK[X[NRTC@G\P^PLT_OCBRHUO@@TBQZDZMHD[YZ@NLXQXIWMOSXT_KOLK^
^CXFF\WHMI^C@EGA_[FXAQ@E^TGNMUXGYNS[QQVL
!Date: Fri, 04 Jul 2003 20:12:31 GMT
!Path:
cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed .icl.net!newsfeed.fjserv.n
et!newsfeed.frii.net!newsfeed.frii.net!news-out.nuthinbutnews.com!propagator
2-sterling!news-in-sterling.newsfeed.com!pd2nf1so.cg.shawcable.net!re sidenti
al.shaw.ca!prodigy.com!prodigy.com!newsmst01.news. prodigy.com!prodigy.com!po
stmaster.news.prodigy.com!newssvr23.news.prodigy.c om.POSTED!df277b9c!not-for
-mail
!Xref: cpmsftngxa09.phx.gbl microsoft.public.dotnet.framework.aspnet:32515
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!
!I've read some postings about when/how/where to open/close Db connections.
!
!My approach has been to use the Global.asax class, open at the start of a
!request, place the connection in Session, use it throughout my app and then
!close the connection at the end of the request again in the Global.asax
!class, destroy it, and remove it from Session.
!
!I see this as scaleable, easy for the developers (they don't have to think
!about the connection) and reliable, as the request will (almost) always end
!and close the connection.
!
!Anyone see problems with this approach?
!
!... david ...
!
!
!