Querying employee schedules in a month I have this code, this code querying employee schedules in a month, in a work unit. If the List&lt;Employee&gt; count are few, the code works great, but if the List&lt;Employee&gt; count are many, the code runs slowly.  How I can optimize this code?

The DataRow is datagrid row containing employee id, name, etc..

await Task.Run(() =&gt;
{
    List&lt;Employee&gt; employees = DatabaseHelper.GetEmployees(unitId, false, false);
    for (int i = 0; i &lt; employees.Count; i++)
    {
        int no = i + 1;
        DataRow row = DataTableJadwal.Rows.Add(no + ".", employees[i].Nama, employees[i].ID);
        DatabaseHelper.GetUserSchedules(ref row, month, year);
    }

    Thread.Sleep(300);
});

public static void GetUserSchedules(ref DataRow row, int month, int year)
{
    using (SqlConnection sqlConn = new SqlConnection(string.Format(constring, UserID, Password, Server, Database)))
    {
        using (SqlCommand sqlComm = new SqlCommand(
            "SELECT Date, Shift FROM ScheduleList WHERE (ID = @ID) AND (MONTH(Date) = @Month) AND (YEAR(Date) = @Year)"
            , sqlConn))
        {
            try
            {
                sqlComm.Parameters.AddWithValue("@ID", row["ID"]);
                sqlComm.Parameters.AddWithValue("@Month", month);
                sqlComm.Parameters.AddWithValue("@Year", year);

                sqlConn.Open();
                using (SqlDataReader sqlReader = sqlComm.ExecuteReader())
                {

                    int totalHour = 0;
                    while (sqlReader.Read())
                    {
                        DateTime dt = (DateTime)sqlReader["Date"];
                        string shift = (string)sqlReader["Shift"];

                        // a user may had 1,2,3 schedule a day
                        string cellText = row[dt.Day.ToString("00")].ToString();
                        if (cellText.Length &lt; 1)
                            cellText = shift;
                        else
                            cellText += "," + shift;

                        // count schedule hour total
                        if (Utilities.FreeScheduleList.IndexOf(shift.ToUpper()) == -1)
                        {
                            if (shift == "M" || shift == "Q")
                                totalHour += 10;
                            else
                                totalHour += 7;
                        }

                        row[dt.Day.ToString("00")] = cellText;
                    }

                    row["Total"] = totalHour;
                }
            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(ex.Message, "GetUserSchedules Error", MessageBoxButton.OK, MessageBoxImage.Stop);
            }
        }
    }
}
I'm going to try to avoid raging about the data table based data access layer because I doubt a random person on the internet is going to change the direction of your project. So, let's look at your SQL query:

SELECT Date, 
       Shift 
FROM   ScheduleList 
WHERE  ID = @ID 
  AND  MONTH(Date) = @Month
  AND  YEAR(Date) = @Year


I've removed the extra brackets because they were just adding noise. This query could easily be changed so you could add a covering index to your schedule list table:

SELECT Date, 
       Shift 
FROM   ScheduleList 
WHERE  ID = @ID 
  AND  Date &gt; @Date
  AND  Date &lt; DATEADD(MONTH, 1, @Date)


Combined with an index on the Date and Id columns which also includes Shift and your query will be much faster. 

CREATE INDEX IX_ScheduleList_Id_Date on your_schema.ScheduleList
(
    Id,
    Date
) INCLUDE (Shift)


I'd be more inclined to do this though:

SELECT Date, 
       Shift 
FROM   ScheduleList 
WHERE  ID = @ID 
  AND  Date &gt; @StartDate
  AND  Date &lt; @EndDate


This way you can search for schedule list in any window of time, not just one month at a time.

Having said that, what I'd actually do is return the employee list with their schedules at the same time - SQL is a relational database: it's good at joining related data together.

(SQL typed into browser and I'm rusty so it might not be 100% right).