相关背景: 一个报表统计型的存储过程,随着数据量增加,查询时间越来越长,因为存储过程超过2K行,要优化可能需要更多的时间,因为急需要报表数据,只好先从 代码层处理超时问题。
以下是解决方案。
/// <summary>
/// 执行Sql返回DataSet
/// </summary>
/// <param name="strSql"></param>
/// <param name="ConnStr"></param>
/// <returns></returns>
public static DataSet GetDataSetByParameters(string strSql, List<SqlParameter> sqlParameters = null)
{
try
{
SqlConnection objConn = GetConnection(ConnStr);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandTimeout = 600;//unit:seconds
sqlCommand.Connection = objConn;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Clear();
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlCommand.CommandText = strSql;
if (sqlParameters != null)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
}
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds);
return ds;
}
}
catch
{
return null;
}
}
//数据库连接字符串
private static string _connStr="";
public static string ConnStr {
get{
if (string.IsNullOrEmpty(_connStr))
{
_connStr=System.Configuration.ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
}
return _connStr;
}
}
app.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="DbConnection" connectionString="Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***;Connection Timeout=180000" providerName="System.Data.SqlClient"/>
</connectionStrings>
<appSettings>
<!-- 1 means debug mode; 0 means PROD; -->
<add key="IsDebug" value="1"></add>
</appSettings>
</configuration>
调用层:
DataSet dsdData = DBA.GetDataSetByParameters("[dbo].[Daily_***_Activity_Stats_by_***_Monitored]"); // 这里是存储过程
(结束)