Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Optimize Your Applications

By Dinesh Priyankara,

The application you have written, either web-based or windows-based, need to be run fast without making end-user annoyed. I have seen many applications that have very low-level performance but can be easily optimized for some extent. For example, we need to load all shippers to drop-down box. You simply write a query that executes against your SQL Server database and loads data into drop-down box. Assume that this shipper’s data is referenced by couple of web forms or win forms. Should we load it over and over again? No, but unfortunately this is what most of us do and in a way, it makes the application run slowly. Though this is a simple thing, let’s see how to handle it.

First, let me go through web-based application that has very simple way to handle this type of case. Hey, I am going to write c#.net code. Do you mind? I hope that you do not. As you all know, web pages are created and destroyed each time when user browser makes a request. So, no data is saved. If couple of pages have drop-down box that holds employee names and ids, you have to query your database every time you need. Let you sever to destroy your page but data. This can be done by using Application state variable that is available and accessible to all your users. Look at the code given below.

private void Page_Load(object sender, System.EventArgs e)
{
	if (!IsPostBack)
	{
		DataTable dataTable = (DataTable) Application["Employees"];
		//DataTable dataTable = (DataTable) Session["Employees"];
		if (dataTable==null)
		{
			Response.Write("Data loaded from database");
			SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=Northwind;Uid=sa;Pwd=");
			sqlConnection.Open();
			
			SqlCommand sqlCommand = new SqlCommand("SELECT EmployeeID, LastName FROM Employees", sqlConnection);
			DataSet dataSet = new DataSet();
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
			
			sqlDataAdapter.Fill(dataSet, "Employees");
			DropDownListEmployees.DataSource = dataSet.Tables["Employees"];
			DropDownListEmployees.DataValueField = "EmployeeID";
			DropDownListEmployees.DataTextField = "LastName";
			DropDownListEmployees.DataBind();
			
			Application["Employees"] = dataSet.Tables[0];
			//Session["Employees"] = dataSet.Tables[0];
		}
		else
		{
			Response.Write("Data loaded from cache");
			DropDownListEmployees.DataSource = dataTable;
			DropDownListEmployees.DataValueField = "EmployeeID";
			DropDownListEmployees.DataTextField = "LastName";
			DropDownListEmployees.DataBind();
		}
	}
}
	

Let’s go through the code. This is page_load event in c#.net web page. As usual this code is written by checking IsPostBack property of the page that is false when the first time user visit the page. The control like drop-down list in .net automatically saves its state for round-trips, we do not want to load data again if user re-visits.

See the next line of code. The object named datatable is created and loaded data into it from Application variable called “Employees”. Remember the syntax. As you can store any type of data in Application variables including objects, you need to explicitly convert the data to correct type before use it. Obviously, this is null if the Application variable “Employees” not created and filled. If it is null, you load data from database and NOTE that you have to save it in Application variable before you exit the event.

Application["Employees"] = dataSet.Tables[0];

The above code will create a variable and store data what you want. That’s all. See the else part of code. If the Application variable is not null, you do not need to query the database again. Data is already loaded for you. Not only for this page. The “Employees” is available in the entire application’s lifetime: that means data can be accessed by any page, any user. But should we write same code in all web pages that we have used employee drop-down list? In this way, yes but there is another alternative way. Why do not we write this code in Application_Start event of global.asax page? Yes, load data into the Application variable in the event of Application_Start and access in your page like below.

if (!IsPostBack)
{
	DataTable dataTable = (DataTable) Application["Employees"];
	DropDownListEmployees.DataSource = dataTable;
	DropDownListEmployees.DataValueField = "EmployeeID";
	DropDownListEmployees.DataTextField = "LastName";
	DropDownListEmployees.DataBind();
}

With this way, the querying and loading part has to be written once and the accessing part become simple. But there is a process called “scavenging” that starts automatically when the memory of server becomes scarce, and it removes all seldom used and unimportant items from memory. So, keep it in mind too. Because Application variables are used server’s memory to hold these data, make sure that your sever has enough memory.

Though this is a very small thing, most of developers do not even bother to write like this and write their code in usual manner. Experiment and see whether this scenario really optimize your application. And this is not a correct way if you need to keep user-specific data like menu items. If you need to keep user-specific data, use Session variables instead of Application variables.

Can we apply same scenario to windows-based application? No we cannot. Let’s see about it in my next article. I highly appreciate all your comments about this and it will definitely give some guidance about my next article.

Total article views: 7839 | Views in the last 30 days: 2
 
Related Articles
FORUM

SSIS and MSMQ datatable

SSIS and MSMQ datatable

FORUM

Alter Database with variable

Alter Database with variable

FORUM

DataTable to Excel using C#

Exporting DataTable to Excel using C#

FORUM

Database Design for Blog application

Database Design for Blog application

ARTICLE

Is Your Database Application DeadLock and Timeout Resistent?

Is your application scalable under increased activity? Timothy Claason brings us a methodology for t...

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones