我们的基于ASP.NET的Web应用程序中存在一个性能问题，当我们早晨登录时，该问题仅可见一次。根据下面的日志，查询耗时超过1分钟20秒。下次用户登录应用程序并尝试访问同一页面时，没有发现任何问题。您能否让我知道如何优化此查询？有什么想法可以解决这个问题吗？
日志 -
"11/13/15","08:38:27","ExecuteSql - ---3---- ","8",""
"11/13/15","08:38:27","ExecuteSql - ---4---- : SQL : SELECT TOP 1 CONVERT(varchar(15), Period_End_Date, 107) as PDate FROM PBHISTORY..STATEMENT_OF_CHANGE
ORDER BY Period_End_Date DESC","8",""
"11/13/15","08:39:48","ExecuteSql - ---5---- ","8",""
SQL：
SELECT TOP 1
CONVERT(varchar(15), Period_End_Date, 107) as PDate
FROM
PBHISTORY..STATEMENT_OF_CHANGE
ORDER BY
Period_End_Date DESC
C＃ASP.NET-
public string GetDateRangeReportingDate(int reportId)
{
var report = GetReportInfoById(reportId);
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);
var data = ExecuteSql(sql);
while (data.Read())
{
return data["PDate"].ToString();
}
return null;
}
private SqlDataReader ExecuteSql(string sql)
{
SqlDataReader reader;
SqlConnection conn;
var commandTimeOut = ConfigurationManager.AppSettings["PBReportCommandTimeout"].ToString();
string connString = ConfigurationManager.ConnectionStrings["PBReportCS"].ConnectionString;
conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = int.Parse(commandTimeOut);
reader = cmd.ExecuteReader();
return reader;
}
我找到了上面提到的性能问题的解决方案。问题是在表中建立索引错误，我通过更改表的索引（从中获取生产记录的位置）来解决此问题。
另外，我通过使用SQL数据适配器和语句来修复了框架级别的实体类。应用程序在生产中运行超级快。谢谢你的帮助。
私有字符串ExecuteSqlNew（string sql）{字符串connectionString = ConfigurationManager.ConnectionStrings [“ PBReportCS”]。ConnectionString; 字符串commandTimeOut = ConfigurationManager.AppSettings [“ PBReportCommandTimeout”]。ToString（）; DataSet结果= new DataSet（）; 字符串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;
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句