I have a program that pulls data for the efficiency of operators in a factory. The current process for doing this is to start by pulling the operators for the day we are looking and store it in a list. We then iterate through that list and pull the necessary info by querying our DB. The run time for this is currently fairly long and I am trying to optimize it but I am unsure of the best approach. Here are some example of the process:

OpInfo is a list of strings each containing info for one of the operators. The info contained is Last name, first name, Op ID, shift and Daily Efficiency.

foreach (List<string> OpSet in ListOpInfo)

Inside this loop we will call functions that will pull necessary info using the data in the “OpSet” variable.

The first function called pulls a value called total routing and looks like this:

 private double GetTotalRouting(string opid)
        {
            string SQL = "SELECT SUM(Routing) " +
                          "FROM Efficiency2 " +
                          "WHERE Operator_No = '" + opid + "' AND " +
                          "PartNumber IS NOT NULL               AND " +
                          "Timestamp BETWEEN '" + startDate + "' AND '" + startDate.AddDays(1) + "';";
            string routing = "0.0";

            if (Open_Database.OpenRITZ())
            {
                SqlCommand cmd = new SqlCommand(SQL, Open_Database.ritzConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        routing = rdr[0].ToString();
                    }
                }
                rdr.Close();
                Open_Database.ritzConnection.Close();
            }
            if (routing == "")
                routing = "0.0";
            return Convert.ToDouble(routing);
        }

Then we call GetProduction:

private string GetProduction(string opid)
        {
            
            List<string> prod = new List<string>();
            startDate = dateTimePicker1.Value.Date;

            string SQL = "SELECT DISTINCT PartNumber " +
                         "FROM Efficiency2 " +
                         "WHERE Operator_No = '" + opid + "'   AND " +
                         "PartNumber IS NOT NULL                 AND " +
                         "TimeStamp BETWEEN '" + startDate + "' AND '" + startDate.AddDays(1) + "';";

            if (Open_Database.OpenRITZ())
            {
                SqlCommand cmd = new SqlCommand(SQL, Open_Database.ritzConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        if (rdr["PartNumber"] != null)
                        {
                            prod.Add(rdr["PartNumber"].ToString());
                        }
                    }
                }
                rdr.Close();

                int sum = 0;
                for (int j = 0; j < prod.Count; j++)
                {
                    SQL = "SELECT Count(PartNumber) " +
                          "FROM Efficiency2 " +
                          "WHERE Operator_No = '" + opid + "' AND " +
                          "PartNumber = '" + prod[j] + "'       AND " +
                          "TimeStamp BETWEEN '" + startDate + "' AND '" + startDate.AddDays(1) + "';";

                    cmd.CommandText = SQL;

                    var firstcolumn = cmd.ExecuteScalar();

                    sum += Int32.Parse(firstcolumn.ToString());
                }
                Open_Database.ritzConnection.Close();
                return sum.ToString();
            }
            else return "0";
        }

And finally we call GetDownTime:

 private double GetDownTimes(string opid)
        {
            List<double> times = new List<double>();
            List<List<string>> downTimes = new List<List<string>>();
            string op_Num = "";

            string SQL = "SELECT DT_Reason,TimeStamp, Downtime,ReviewedBy, Category, AuthorizedDownTime, DownTime, LEAD(DownTime,1) OVER (ORDER BY TimeStamp) as dt " +
                  "FROM Efficiency2 " +
                  "WHERE Operator_No = '" + opid + "' AND TimeStamp BETWEEN '" + startDate + "' AND '" + startDate.AddDays(1) + "';";

            SQL = "SELECT SUM(DOWNTIME-AUTHORIZEDDOWNTIME) FROM EFFICIENCY2 WHERE Operator_No = '" + opid + "' AND TimeStamp BETWEEN '" + startDate + "' AND '" + startDate.AddDays(1) + "';";

            if (Open_Database.OpenRITZ())
            {
                double sum = 0.0;
                SqlCommand cmd = new SqlCommand(SQL, Open_Database.ritzConnection);
                var rdr = cmd.ExecuteScalar();
                sum = Math.Round(Convert.ToDouble(rdr), 2);

                Open_Database.ritzConnection.Close();
                return sum;
            }
            return 0.0;
        }