Adapter.Fill需要很长时间 [英] Adapter.Fill takes long
问题描述
我已经以编程方式创建了 RadGrid ，并使用 NeedDataSource -> GetDataTable 进行了绑定.
I've created a RadGrid Programmatically and binding it using NeedDataSource -> GetDataTable.
在 GetDataTable 内，我正在调用connstring并使用适配器填充网格(请参见下面的代码).问题是，在我的SQL Server中，查询需要0秒钟才能运行，但是在ASP.NET调试模式下，查询大约需要3到5秒钟，在我的页面上有很多RadGrid的情况下，这导致了我页面加载缓慢.
Within the GetDataTable, I'm calling my connstring and fill the grid with an adapter (see code below). Problem is that, in my SQL Server, the query takes 0 sec to run, but in the ASP.NET debug mode, it's taking about 3~5s, in my case of having a lot of RadGrids on the page, this is causing my page to load slowly.
这是 adapter.Fill 的处理速度一般问题，还是我的设置做错了什么?(即conn.open/close或其他命令)?
Is this processing speed of adapter.Fill a general issue or have I done something wrong with the setting? (ie, orders of conn.open/close or any others)?
public DataTable GetDataTable(int Year, int month, string datatype)
{
String ConnString = ConfigurationManager.ConnectionStrings["IHG_MSTConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("[Yield_Planner_With_Strategy]", conn);
adapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adapter.SelectCommand.Parameters.AddWithValue("@Holidex_Code", RadComboBox_Hotels.SelectedValue);
adapter.SelectCommand.Parameters.AddWithValue("@Event_Year", Year);
adapter.SelectCommand.Parameters.AddWithValue("@Event_Month", month);
adapter.SelectCommand.Parameters.AddWithValue("@DataType", datatype);
adapter.SelectCommand.Parameters.AddWithValue("@MktSeg", Fruitful.Get_Checked_Values_As_CSV(RadComboBox_MktSeg));
string exportdate = DateTime.Now.ToString("yyyy/MM/dd");
if (RadComboBox_ExportTimeStamp.Text != "" && RadComboBox_ExportTimeStamp.Text != "Create New Strategy")
{ exportdate = Convert.ToDateTime(RadComboBox_ExportTimeStamp.Text).ToString("yyyy/MM/dd"); }
adapter.SelectCommand.Parameters.AddWithValue("@ExportTimeStamp", exportdate);
DataTable myDataTable = new DataTable();
conn.Open();
try
{
adapter.Fill(myDataTable);
}
finally
{
conn.Close();
}
return myDataTable;
}
推荐答案
为什么为 ExportTimeStamp 参数使用字符串?用 DateTime (如果它是 date 或 datetime 列).
Why do you use a string for the ExportTimeStamp parameter? Use
DateTime if it's a date or datetime column.
我还将替换您对 添加 .当您调用 AddWithValue 时，必须猜测参数的类型.如果猜错了，优化器将无法选择正确的索引并退回到表扫描，这证明了数据库性能的核心.
I'd also replace all of your calls to AddWithValue with Add. When you call AddWithValue it has to guess what the type of your parameter is. If it guesses wrong the optimizer cannot select the correct index and falls back to a table scan, and that speaks to the core of database performance.
AddWithVaue 可能会导致多个查询计划.由于.NET不知道数据库列的大小，因此它将使用变量的大小.因此，如果您有一个参数化查询并传入两个长度为10的字符串，另一个为长度20的字符串，则会得到两个计划: @text nvarchar(10)和 @text nvarchar(20).当它可能是 varchar 时，它还将假定您的字段是 nvarchar ，并且您将获得隐式转换.
AddWithVaue may result in multiple query plans. Since .NET doesn't know what the size of the database column is, it will use the size of the variable. so if you have a parameterized query and pass two strings in, one of length 10, the other of length 20, you will get two plans: @text nvarchar(10) and @text nvarchar(20). It will also assume that your field is nvarchar when it may be varchar and you will get an implicit conversion.
因此，始终将正确的类型传递给 AddWithValue 或(更好)使用
So always either pass the correct type to AddWithValue or (better) use SqlParameterCollection.Add with the correct type and size. It'll also validate the parameter before it gets sent to the database.
相关:
此外，使用 using 语句可确保连接完成后立即关闭连接-即使发生错误也是如此.
Also, use the using-statement to ensure that the connection gets closed as soon as you're finished with it - even in case of an error.
这里是一个例子:
public DataTable GetDataTable(int Year, int month, string datatype)
{
DataTable myDataTable = new DataTable();
String ConnString = ConfigurationManager.ConnectionStrings["IHG_MSTConnectionString"].ConnectionString;
using(SqlConnection conn = new SqlConnection(ConnString))
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
var cmd = new SqlCommand("[Yield_Planner_With_Strategy]", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@Holidex_Code", SqlDbType.Int).Value = int.Parse(RadComboBox_Hotels.SelectedValue);
cmd.Parameters.Add("@Event_Year", SqlDbType.Int).Value = Year;
cmd.Parameters.Add("@Event_Month", SqlDbType.Int).Value = month;
cmd.Parameters.Add("@DataType", SqlDbType.VarChar).Value = datatype;
cmd.Parameters.Add("@MktSeg", SqlDbType.NVarChar).Value = Fruitful.Get_Checked_Values_As_CSV(RadComboBox_MktSeg);
DateTime exportdate = DateTime.Now;
if (RadComboBox_ExportTimeStamp.Text != "" && RadComboBox_ExportTimeStamp.Text != "Create New Strategy")
{
exportdate = DateTime.Parse(RadComboBox_ExportTimeStamp.Text);
}
cmd.Parameters.Add("@ExportTimeStamp", SqlDbType.DateTime).Value = exportdate;
adapter.SelectCommand = cmd;
// you don't need to open it with Fill
adapter.Fill(myDataTable);
}
return myDataTable;
}
这篇关于Adapter.Fill需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
