Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Tracing in - A real time example - Part 81

Suggested Videos
Part 78 - Sending emails in using SMTP server settings from web.config
Part 79 - Tracing in
Part 80 - Writing custom 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
select Id, Name, Gender, DeptName 
from tblEmployees

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

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

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

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

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

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

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

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

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

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();
    if (spname == "spGetEmployeesByGender")
    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.

1 comment:

  1. Hi Venkat,

    Thanks for the useful information.

    Is Tracing works in MVC to identify performance issues?
    Or Is there any other techniques in MVC to identify performance issues(which part of the code taking more time)?


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.