Adapter.Fill takes a lot of time

Question

I created RadGrid programmatically and bind it using NeedDataSource GetDataTable .

Inside GetDataTable I call my connection and fill the grid with an adapter (see code below). The problem is that on my SQL Server the query takes 0 seconds to start, but in ASP.NET debugging mode it takes about 3 ~ 5 seconds, in my case there is a lot of RadGrids on the page, this causes my page to load slowly.

Is this adapter.Fill processing adapter.Fill common problem, or have I done something wrong with the setup? (i.e. conn.open/close orders 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; } 

Answer 1

Why are you using a string for the ExportTimeStamp parameter? using DateTime if it is a date or DateTime .

I would also replace all of your calls with AddWithValue with Add . When you call AddWithValue , it should guess what type of your parameter is. If it is mistaken, the optimizer cannot select the correct index and returns to scanning the table, and this speaks of the database performance core.

AddWithVaue can result in several requests. Since .NET does not know what the size of the database column is, it will use the size of the variable. therefore, if you have a parameterized query and pass two lines in one of lengths 10, the other 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 can be varchar and you will get an implicit conversion.

Therefore, always either pass the correct type to AddWithValue , or (better) use SqlParameterCollection.Add with the correct type and size. It will also check the parameter before sending it to the database.

Connected:

• SqlCommand Add and AddWithValue Parameters
• Bad delete habits: incorrect date handling
• Seven Sins Against TSQL Performance

Also, use using -statement to make sure the connection closes as soon as you finish with it, even in the event 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; }