how to optimize a "foreach" event from a gridview in asp.net webform?

Question

I have an Event that starts when a TextBox text is changed in c#.net. The Event goes over a GridView rows. The GridView is created with information from a DB. This GridView has eight TemplateFields, seven with TextBox and one with a DropDownList control.

The problem is that this Event is taking between 26 and 27 seconds.

Foreach row, it should:

• Check if the [4] column content is equal to "BATCH" and, if true, paint the entire row with a different color.
• Extract the ID from the [0] column.
• Use this ID and the Date from a TextBox for a Query that will look if the record already exists in the DB.
• If the record exists, it should print it into the TextBox and DropDownList from the TemplateField.
• If some of the records in the database are empty, there's a few TextBox that shouldn't print it.

Additional Information:

• The GridView is created with data from the DB, using a QUERY inside a SqlDataSource. This happens in the same event. This QUERY contains some INNER JOIN, the data is not stored in the same DB Table:

  SELECT Dealer.IDDealer, Batch.IDBatch, Lpars.Nombre, Dealer.DealerCodigo, Batch.Nombre AS Expr1, Batch.CTStart AS Expr2 FROM Lpars INNER JOIN Dealer ON Lpars.IDLpar = Dealer.IDLpar INNER JOIN Batch ON Dealer.IDDealer = Batch.IDDealer INNER JOIN [1Monday] ON Batch.IDBatch = [1Monday].IDBatch WHERE (Batch.Status = 'Enabled') ORDER BY Batch.CTStart

• The response time issue is not because the SQL QUERY at the top. I tried it separatelly and the SQL Server response time for this query is less than 2 seconds.

• There is some DATETIME data that I extract from the DB. So, before I print it into the TextBox, I need to change the DATETIME format to my needs. That's why I store the extracted data into variables before print it.

Here is my code:

-

 //WHEN THE TEXT FROM TEXTBOX CHANGES:
        protected void TextDate_TextChanged(object sender, EventArgs e)
        {
            //THE GRIDVIEW IS CREATED:
            GridView1.DataSourceID = DatosLunes.ID;
            GridView1.DataBind();

            //A) I CREATE VARIABLES TO CHARGE THE DATA THAT CAMES FROM THE DATABASE WHEN PROCEED WITH THE QUERY
            string VarsDateGV;
            string VarsStartGV;
            string VarsScchkGV;
            string VarsEndGV;
            string VarsDurationGV;
            string VarsBeforeGV;
            string VarsAfterGV;

            //B) FOREACH ROW, THE PROCESS START TO:
            foreach (GridViewRow row in GridView1.Rows)
            {
                //B.1) IDENTIFY EACH CONTROL INTO ROW COLUMNS:
                TextBox DateGV = row.FindControl("DateGV") as TextBox;
                TextBox StartGV = row.FindControl("StartGV") as TextBox;
                TextBox ScchkGV = row.FindControl("ScchkGV") as TextBox;
                TextBox EndGV = row.FindControl("EndGV") as TextBox;
                TextBox DurationGV = row.FindControl("DurationGV") as TextBox;
                HiddenField DedicatedGV = row.FindControl("DedicatedGV") as HiddenField;
                HiddenField NotDedicatedGV = row.FindControl("NotDedicatedGV") as HiddenField;
                DropDownList DropDownGV = row.FindControl("DropDownGV") as DropDownList;
                TextBox BeforeGV = row.FindControl("BeforeGV") as TextBox;
                TextBox AfterGV = row.FindControl("AfterGV") as TextBox;
                DateTime FechaCT1 = DateTime.Parse(TextDate.Text, CultureInfo.InvariantCulture);

                //B.2) IF THE [4] COLUMN STRING IS EQUAL TO "BATCH", THE ROW IS PAINTED
                if (row.RowType == DataControlRowType.DataRow)
                {
                    string NombreBatch = row.Cells[4].Text;

                    if (NombreBatch == "BATCH")
                    {
                        row.BackColor = System.Drawing.Color.NavajoWhite;
                    }
                }

                //B.3) THE QUERY STARTS
                if (row.RowType == DataControlRowType.DataRow)
                {
                    // B.3.1) EXTRACTS THE ROW ID FROM [0] COLUMN
                    string IDBatch = row.Cells[0].Text;

                    //B.3.2) USE A DATATABLE TO CHARGE DATA FROM THE QUERY "TRAEFILAHO"
                    CADCATOPS.DSCATOPS.BatchDatos1DataTable Fila = CADCATOPS.CADBatchHandoff.TraeFilaHO(Convert.ToInt32(IDBatch), Convert.ToString(FechaCT1));

                    //B.3.3) FOREACH ROW IN THE DATATABLE, THE DB INFORMATION IS SAVED INTO THE VARIABLES CREATED BEFORE (IN THE "A" ITEM).
                    foreach (DataRow row1 in Fila.Rows)
                    {
                        VarsDateGV = row1["FechaBatch"].ToString();
                        VarsStartGV = row1["Inicio"].ToString();
                        VarsScchkGV = row1["FinDedicado"].ToString();
                        VarsEndGV = row1["FinNoDedicado"].ToString();
                        VarsDurationGV = row1["DuracionBatch"].ToString();
                        DropDownGV.Text = row1["Estado"].ToString();
                        VarsBeforeGV = row1["DuracionBefore"].ToString();
                        VarsAfterGV = row1["DuracionAfter"].ToString();

                        /********* FROM NOW ON:
                         B.3.3.1) I VALIDATE IF THE DATETIME DATA EXTRACTED FROM THE DB EXISTS FOR A FEW ITEMS. IF EXISTS, THE FORMAT IS CHANGED FOR MY NEEDS, AND PRINTED.

                         MAYBE YOU ARE ASKING WHY I VALIDATE IT FOR SEPARATED AND NOT ALL TOGETHER, THIS IS BECAUSE I NEED TO CHECK IT SEPARATELLY. 
                         IF "THIS" DATA DOESN'T EXISTS, DON'T BRING IT TO THE GRIDVIEW, BUT IF "THIS OTHER" DATA EXISTS, I NEED TO SHOW IT.
                         *********/
                        if (VarsDateGV != "")
                        {
                            DateTime VardDateGV = DateTime.Parse(VarsDateGV, CultureInfo.InvariantCulture);
                            DateTime VardStartGV = DateTime.Parse(VarsStartGV);
                            DateGV.Text = VardDateGV.ToString("MM/dd/yyyy");
                            StartGV.Text = VardStartGV.ToString("HH:mm");
                        }

                        if (VarsEndGV != "")
                        {
                            DateTime VardEndGV = DateTime.Parse(VarsEndGV);
                            DateTime VardDurationGV = DateTime.Parse(VarsDurationGV);
                            EndGV.Text = VardEndGV.ToString("HH:mm");
                            DurationGV.Text = VardDurationGV.ToString("HH:mm");
                        }

                        if (VarsScchkGV != "")
                        {
                            DateTime VardScchkGV = DateTime.Parse(VarsScchkGV);
                            ScchkGV.Text = VardScchkGV.ToString("HH:mm");
                        }

                        if (VarsBeforeGV != "")
                        {
                            DateTime VardBeforeGV = DateTime.Parse(VarsBeforeGV);
                            BeforeGV.Text = VardBeforeGV.ToString("HH:mm");
                        }

                        if (VarsAfterGV != "")
                        {
                            DateTime VardAfterGV = DateTime.Parse(VarsAfterGV);
                            AfterGV.Text = VardAfterGV.ToString("HH:mm");
                        }
                    }
                }
            } //FOREACH LOOP IS COMPLETED.
        }

Do you have any reccomendation to optimize this event?

UPDATE: ConnorsFan helps me to detect the issue (Thank you).

The issue is the query, because it runs 50 times (or the GridView lenght). I tried avoiding it and the response was less than 4 seconds. The problem is that I need it to work with the query. Is there a way to optimize the code for it?

Answer 1

Then the total number of iterations is around 100 iterations. That shouldn't take very long at all.

Can you use the Stopwatch class on the beginning and end of the event handler and get a total miliseconds count for the execution of the function - at present there doesn't seem to be any reason to expect the code to be slow. We need to separate the execution speed of the code to the time taken to refresh the UI.

Alternatively follow the advice here How to suspend a DataGridView while updating its columns to suspend the GridView painting and reformatting during the execution of your changes, then resume the layout after you have finished.

Answer 2

I assume that TextDate is outside of the GridView. As I understand it, your inner query returns a single record. You could modify it to return all the records that you will need inside the loop (including the IDBatch field), and run it before entering the loop. Inside the loop, you would find the specific record with the primary key (as indicated here).

protected void TextDate_TextChanged(object sender, EventArgs e)
{
    ...

    CADCATOPS.DSCATOPS.BatchDatos1DataTable AllFila = CADCATOPS.CADBatchHandoff.AllTraeFilaHO(Convert.ToString(FechaCT1));
    AllFila.PrimaryKey = new DataColumn[] { AllFila.Columns["IDBatch"] };

    foreach (GridViewRow row in GridView1.Rows)
    {
        ....
        if (row.RowType == DataControlRowType.DataRow)
        {
            string IDBatch = row.Cells[0].Text;
            DataRow foundRow = AllFila.Rows.Find(IDBatch);
            ...
        }
    }
}

You could make it faster if the records in the DataTable returned by the query are sorted the same way as in the GridView. The row indexes would match, and you wouldn't need to find the row in the DataTable.

UPDATE

If the TextBox data can be obtained directly from the data source that populates the GridView, you can eliminate the foreach loop (and all the related processing). You can use the second parameter of Eval to format the data:

<asp:TextBox ID="DateGV" runat="server" Text='<%# Eval("FechaBatch", "{0:MM/dd/yyyy}") %>' ... />
<asp:TextBox ID="StartGV" runat="server" Text='<%# Eval("Inicio", "{0:HH:mm}") %>' ... />