ASP.Net WebApp Performance Issue

Question

I've been working in a ASP.Net WebApp which takes long time to load a particular ASPX Page. After first time the page is loaded in browser, next time onward this issue is not replaceable in Production.

Update

I added some log and looks like below query is taking 1 minute 20 sec to execute. Can you please help me to optimize it ? What's actually wrong in the query that it takes so long first time ?

Log :

"11/12/15","22:24:24","ExecuteSql - ---4---- ","9",""

"11/12/15","22:25:44","ExecuteSql - ---5---- ","9",""

Query:

SELECT TOP 1 CONVERT(varchar(15), Period_End_Date, 107) as PDate FROM PBHISTORY..STATEMENT_OF_CHANGE ORDER BY Period_End_Date DESC","7",""

C# Code:

public string GetDateRangeReportingDate(int reportId) { LogActivityVerbose("GetDateRangeReportingDate - before GetReportInfoById "); var report = GetReportInfoById(reportId); LogActivityVerbose("GetDateRangeReportingDate - after GetReportInfoById ");

        string sql = string.Format(@"SELECT TOP 1 CONVERT(varchar(15), Period_End_Date, 107) as PDate FROM {0}..{1}
                                           ORDER BY Period_End_Date DESC", _historyDatabase, report.SourceTableName);

        LogActivityVerbose("GetDateRangeReportingDate - before ExecuteSql ");
        var data = ExecuteSql(sql);
        LogActivityVerbose("GetDateRangeReportingDate - after ExecuteSql ");
        while (data.Read())
        {
            return data["PDate"].ToString();
        }
        return null;
    }

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Are you deploying a debug build vs release build (debug build will not contain compiler optimizations and will load up the debug symbols)?
Are your app pools recycling too often?
Are you loading lots of data on page start up in some sort of initialization?
Also keep in mind on the initial load of a new ASP .net precompiled assembly will be Jitted on start so that does take some time as well.

More possibilities:

Web site takes unusually long time to start the first time it is accessed, (Up to 68 seconds in total)

and

Fixing slow initial load for IIS

I would check these things first.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 11 '15 at 21:51

dynamiclynk

2,275
27
31

1

Thanks very much. I put some log and looks like this issue has something to do with query based on Log. Below Method takes more than a minute to perform. Can you suggest something to optimize it ? – user3430726 Nov 12 '15 at 22:41

score 0 · Accepted Answer · answered Nov 14 '15 at 04:36

I found solution of my performance issue I mentioned above. Issue was wrong indexing in table and I fixed this issue by changing the indexing of table where from we are fetching records in production.

Also, I fixed Framework level entity class by using SQL Data Adapter and using statements. App runs super fast in production. Thanks for your help.

private string ExecuteSqlNew(string sql) { string connectionString = ConfigurationManager.ConnectionStrings["PBReportCS"].ConnectionString; string commandTimeOut = ConfigurationManager.AppSettings["PBReportCommandTimeout"].ToString(); DataSet result = new DataSet(); string pDate = "";

        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    cmd.CommandTimeout = int.Parse(commandTimeOut);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(result);
                    adapter.Dispose();
                    conn.Close();
                    pDate = result.Tables[0].Rows[0]["PDate"].ToString();
                }
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        return pDate;

}

ASP.Net WebApp Performance Issue

2 Answers2