Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Title: DataBase Connection Efficiency (C#), Connecting to MS_SQL


  • Please log in to reply

#1
GinaB

GinaB

    New Member

  • Member
  • Pip
  • 7 posts
This will probably start a large debate, but here goes:

When creating a web site that will (potentially) have MANY users hitting it, what would the most efficient way to connect to the database? (In my mind, MANY will be about 200-400 users during the "busy" time.)

I have always created 1 connection for the user, and keep that connection open for the entire session in any of the applications that I have worked on.

Other people claim that it is better for each page to open the connection, use it, and then close it when they are done.

Is there a way to create 1 connection object that all users can use? Would this be efficient?

I will be writing this in C# (.Net 2005), and I will be hitting a MS SQL database.

As an aside, should I check for unused connections within the web app itself, and attempt to close them, or will IIS and SQL Server clean up for me? :whistling:
  • 0

Advertisements


#2
admin

admin

    Founder Geek

  • Administrator
  • 24,575 posts
I'm not a coder, but I do admin some servers. :whistling:

Database aps that I'm familiar with open one connection per user. The server config will determine the session duration, and close unneeded connections.

200-400 concurrent connection is quite a lot. Every application is different, but you'll need to limit and optimize your queries, and probably still need some good hardware (i.e dual-Xeon/Opteron).
  • 0

#3
darth_ash

darth_ash

    Member 1K

  • Member
  • PipPipPipPip
  • 1,382 posts
Hi GinaB,
Here is what I do:
If there are any tables in database that are going to remian static\constant\read-only, i.e. there won't be any changes on them. e.g. Parent tables like which contain a list of States, Department table of employee database; then I load this tables in a dataset in the Application_Load event in the Global.asax file, and copy then in an Application-level variable. This way these read-only tables remain global for the entire application. But in case you make changes to these tables, you will need to restart the application again.

For rest of the tables it depends on the page; If the page needs to insert some data, then I open-close a connection only between sqlCmd.ExecuteNonQuery() (where sqlCmd is of SqlCommand type), its more efficient that way, also always try to use stored-procedures for DML commands. If the page requires reading data, then I use a Dataset for large amount of data and DataReader for samll amount of data that will only be used once.

Since you using .NET 2.0, You could use Asynchronous-Execute feature for DataReader, which will work even with MS-SQL Sever 2000. If you MS-SQL Server 2005, there are whole lot of cool new features to improve performance.
  • 0

#4
GinaB

GinaB

    New Member

  • Topic Starter
  • Member
  • Pip
  • 7 posts
Thank you both... :whistling:
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP