I've created a RadGrid
Programmatically and binding it using NeedDataSource
-> GetDataTable
.
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.
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;
}
Why do you use a string for the ExportTimeStamp
parameter? Use
DateTime
if it's a date
or datetime
column.
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
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.
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.
Related:
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.
Here is an example:
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;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With