I’ve been working on a web application in ASP.net. My application has several pages and all of them need to display tables that are populated by a database. Right now what I’m doing is, on each page, I’m opening a database connection, executing the query specific to that page, and closing the db connection. So this happens each time the user clicks a link to go to a new page or clicks a form control like the grid page.
I was wondering if this was a disaster from the performance point of view. Is there any better way to do this?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Almost universally, database connections should be handled as follows: Open as late as possible, and close as soon as possible. Open and close for multiple queries/updates… don’t think leaving it open saves you anything. Because connection pooling generally does a very good job for you of managing the connections.
It is perfectly fine to have a couple/few connections opened/closed in the production of a single page. Trying to keep a single connection open between page views would be quite bad… don’t do that under any circumstances.
Basically, with connection pooling (enabled by default for almost all providers), “closing” a connection actually just releases it back to the pool to be reused. Trying to keep it open yourself will tie up valuable connections.
Method 2
That is exactly how you want it to be. A database connection should only be opened when necessary and closed immediately after use.
What you may want to look at, especially if performance is a big issue for you, is caching. You may want to cache the entire page, or just parts of a page, or just the data that you would like displayed on your pages. You will save a lot of database trips this way, but you would have to now consider other things like when to update your cache, caching for different users, etc.
Method 3
From MSDN – Best Practices in ADO.Net
High performance applications keep connections to the data source in
use for a minimal amount of time, as well as take advantage of
performance enhancing technology such as connection pooling.
What you are doing is perfectly fine, opening the connection to execute the query and then closing it afterward. If you hold the connection for a longer period of time, and there are multiple people accessing your application, then there are chances that you might run out the connection limit usually set on a database.
Method 4
Tieing up DB connections to backend code is a bad practice.As you are learning, I suggest you to use webservices to interact with UI rather than linking your Data Interactions to UI.
Like UI(Aspx Page ) >> BLL (Business Logic Layer)>> DAL (Data access Layer)
Also try using the ‘using’ keyword in DAL and dispose connections and all after DB interaction
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0