Adapter.Fill takes long

Question

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;
}

Answer 1

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:

• SqlCommand Parameters Add vs. AddWithValue
• Bad habits to kick : mis-handling date
• The Seven Sins against TSQL Performance

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;
}