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

Why do you use a string for the `ExportTimeStamp` parameter? Use `DateTime` if it's a `date` or `datetime` column. — Tim Schmelter, Aug 07 '14 at 15:28
What happens if you add `WITH RECOMPILE` option to your stored procedure? — Yuriy Galanter, Aug 07 '14 at 15:30
I'd start by changing all of your calls to `AddWithValue` to use `Add` instead. When you call `AddWithValue`, .Net has to guess what the type of your parameter is. If it guesses wrong (and it can), suddenly your query might no longer match with an index, and that speaks to the core of database performance. — Tim Schmelter, Aug 07 '14 at 15:33
@ChunfaatJonathanNgai: does that solve your performance problem? — Tim Schmelter, Aug 07 '14 at 15:37
@Yuriy and Tim bear with me a sec, testing out the method. and To Bun: 28~31 rows each grid as showing the day of month, and I have 24 grids on the page. It's sort of like a calendar design. — Chunfaat Jonathan Ngai, Aug 07 '14 at 15:46
thanks tim and yuriy, I've used suggestion of both and is now working, the problem occurs in the data type conversion between c# and sql. Appreciated! — Chunfaat Jonathan Ngai, Aug 07 '14 at 15:51
how do I select an answer or close the thread if appropriate? — Chunfaat Jonathan Ngai, Aug 07 '14 at 15:52
@TimSchmelter should post this as an answer so u can accept it — Yuriy Galanter, Aug 07 '14 at 16:09
@ChunfaatJonathanNgai You probably want to load the data of all the 24 grids at once, if possible. Just the overhead of the 24 separate DB requests can add up quickly. — Luaan, Aug 08 '14 at 07:29

score 5 · Accepted Answer · edited Jul 29 '20 at 18:30

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.

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

Crazy that the `AddWithValue` adds so much to the execution time. Changed my `Fill` from about 20 seconds to 1 second. — anothershrubery, May 18 '16 at 12:33

Adapter.Fill takes long

1 Answers1

Linked