Optimize Your Applications

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.

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.

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.

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.