Working code needs to be refactored to increase speed

Question

I have some working code but it takes a long time to return the results to a textbox. The output could take up to 20 seconds to output, and I don't have much data to sift through.

I get the correct output but it is not working good.

    Global variable:string newLine = Environment.NewLine;

    public List<string> AppointmentTypesForReport(int month)
    {
        List<string> appointments = new List<string>();

        string CS = ConfigurationManager.ConnectionStrings["U04i5a"].ConnectionString;
        using (MySqlConnection con = new MySqlConnection(CS))
        {
            try
            {
                con.Open();
                MySqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT type FROM appointment WHERE MONTH(start) = @month";
                cmd.Parameters.AddWithValue("@month", month);
                cmd.ExecuteNonQuery();

                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        appointments.Add(reader["type"].ToString());
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        return appointments;
    }

    private void TypesByMonthRadioButton_CheckedChanged(object sender, EventArgs e)
    {
        ReportsTextBox.Clear();
        //resultTextBox.Text = string.Empty;

        var typesOutput = "";

        for (int m = 1; m <= 6; m++)
        {
            List<string> list = AppointmentTypesForReport(m);

            var j = from t in list
                    group t by t into g
                    let totalNumber = g.Count()
                    orderby totalNumber descending
                    select new { Returned = g.Key, Tally = totalNumber };

            typesOutput += newLine + DateTimeFormatInfo.CurrentInfo.GetMonthName(m) + newLine;

            //convert to lambda
            foreach (var t in j)
            {
                typesOutput += "Type of appointment scheduled: " + t.Returned + "   " + "-Appointments of this type: " + t.Tally + newLine;
            }
        }
        ReportsTextBox.Text = typesOutput;
    }

When the code runs, the following is the output in the text box.

January Type of appointment scheduled: xyz -Appointments of this type: 2

February

March

April

May Type of appointment scheduled: General Doctor -Appointments of this type: 1

June Type of appointment scheduled: General Doctor -Appointments of this type: 1

You can consider asking on https://codereview.stackexchange.com/, but please take a look at their Help section first - https://codereview.meta.stackexchange.com/questions/2436/how-to-get-the-best-value-out-of-code-review-asking-questions — Vadim Kotov, May 30 '19 at 15:39
Use a SQL profiler. `MONTH(start)` is probably not sargable and requires a full table scan; that is about as slow a query as can be made. I don't know if aggregate functions can be made sargable on MySQL; you will have to do further investigation. — Dour High Arch, May 30 '19 at 15:54
[What makes a SQL statement SARGable?](https://stackoverflow.com/q/799584/7444103) — Jimi, May 30 '19 at 15:58

score 0 · Accepted Answer · answered May 30 '19 at 15:50

At least you should use StringBuilder to build result string:

 var typesOutput = new StringBuilder();
 ...
 //typesOutput += newLine + DateTimeFormatInfo.CurrentInfo.GetMonthName(m) + newLine;
 typesOutput.AppendLine();
 typesOutput.AppendLine(DateTimeFormatInfo.CurrentInfo.GetMonthName(m));
 ...
 //typesOutput += "Type of appointment scheduled: " + t.Returned + "   " + "-Appointments of this type: " + t.Tally + newLine;
 typesOutput.AppendFormat("Type of appointment scheduled: {0} - Appointments of this type:  {1}\n", t.Returned, t.Tally);
 ...
 ReportsTextBox.Text = typesOutput.ToString();

I used your suggestion and it is working better now. Before it was a little sluggish to output the results. — Joshua Johnson, May 30 '19 at 17:16

Working code needs to be refactored to increase speed

1 Answers1