Tracing in asp.net - A real time example - Part 81

Suggested Videos
Part 78 - Sending emails in asp.net using SMTP server settings from web.config
Part 79 - Tracing in asp.net
Part 80 - Writing custom asp.net tracing messages

ASP.NET Page is very slow. What will you do to make it fast?
This is a very common interview question. There are several reasons for the page being slow. We need to identify the cause. 

We cannot debug an application on the production server, as we will usually, not have visual studio installed, and as the code is optimized for release builds. 



Step 1:
First find out which is slow, is it the application or the database : If the page is executing SQL queries or stored procedures, run those on the database and check how long do they take to run. Alternatively, SQL profiler, can be used, to inspect the queries and the time they take. If the queries are taking most of the time, then you know you have to tune the queries for better performance. To tune the queries, there are several ways and I have listed some of them below.
    a) Check if there are indexes to help the query
    b) Select only the required columns, avoid Select *.
    c) Check if there is a possibility to reduce the number of joins
    d) If possible use NO LOCK on your select statements
    e) Check if there are cursors and if you can replace them with joins



Step 2:
If the queries are running fast, then we know it is the application code that is causing the slowness. Isolate the page event that is causing the issue by turning tracing on. To turn tracing on, set Trace="true" in the page directive. Once you have tracing turned on you should see trace information at the bottom of the page or in trace.axd file. From the trace information, it should be clear to identify the piece of code that is taking the maximum time.

Stored Procedures used in the Demo
Create proc spGetEmployees
as
begin
select Id, Name, Gender, DeptName 
from tblEmployees
end

Create proc spGetEmployeesByGender
as
begin
select Gender, Count(Gender) as Total 
from tblEmployees
Group by Gender
end

Create proc spGetEmployeesByDepartment
as
begin
select DeptName, Count(DeptName) as Total 
from tblEmployees
Group by DeptName
end

ASPX page HTML:
<div style="font-family: Arial">
    <b>All employees</b>
    <asp:GridView ID="gvAllEmployees" runat="server">
    </asp:GridView>
    <br /><br />
    <b>Total Employees by Department</b>
    <asp:GridView ID="gvEmployeesByDepartment" runat="server">
    </asp:GridView>
    <br /><br />
    <b>Total Employees by Gender</b>
    <asp:GridView ID="gvEmployeesByGender" runat="server">
    </asp:GridView>
</div>

Code Behind:
protected void Page_Load(object sender, EventArgs e)
{
    Trace.Warn("GetAllEmployees() started");
    GetAllEmployees();
    Trace.Warn("GetAllEmployees() Complete");

    Trace.Warn("GetEmployeesByGender() started");
    GetEmployeesByGender();
    Trace.Warn("GetEmployeesByGender() Complete");

    Trace.Warn("GetEmployeesByDepartment() started");
    GetEmployeesByDepartment();
    Trace.Warn("GetEmployeesByDepartment() Complete");
}

private void GetAllEmployees()
{
    gvAllEmployees.DataSource = ExecuteStoredProcedure("spGetEmployees");
    gvAllEmployees.DataBind();
}

private void GetEmployeesByGender()
{
    gvEmployeesByGender.DataSource = ExecuteStoredProcedure("spGetEmployeesByGender");
    gvEmployeesByGender.DataBind();
}

private void GetEmployeesByDepartment()
{
    gvEmployeesByDepartment.DataSource = ExecuteStoredProcedure("spGetEmployeesByDepartment");
    gvEmployeesByDepartment.DataBind();
}

private DataSet ExecuteStoredProcedure(string spname)
{
    string CS = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
    SqlConnection con = new SqlConnection(CS);
    SqlDataAdapter da = new SqlDataAdapter(spname, con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataSet DS = new DataSet();
    da.Fill(DS);
    if (spname == "spGetEmployeesByGender")
    {
        System.Threading.Thread.Sleep(7000);
    }
    return DS;
}

Set Trace=true, in Page directive:
<%@ Page Language="C#" Trace="true" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="AdoDemo.WebForm3" %>

Many a times, the issue is not reproducible on the development environment. The issue happens only on the production server. In this case tracing is an invaluable mechanism to get to the root cause of the issue.