Before Chris W. took a stab at optimizing the below code, it had been a pain in the neck of his employer for a looong time.

Running in a call center, it loops through agents stored in a database table, queries their queues and the skills on the queue. It then inserts a record in another table for each combination.

If you wanted to query the queue of a single call center agent, really, it wasn't all that bad. However, if management wanted to see how everybody was doing, the routine took about 8 hours to run. At best, the "all agents" report, started in the morning, and finished by end of business was kind of useful with it's almost current information.

DataSet aDs, qDs;
aDs = _dbConnector.UpdateAgentList();
qDs = _dbConnector.GetQueueList();
foreach (DataTable aTable in aDs.Tables)
{
  foreach (DataRow aRow in aTable.Rows)
  {
    foreach (DataColumn aColumn in aTable.Columns)
    {
      DataSet asDs = _dbConnector.GetAgentSkills(aRow[aColumn].ToString());//AgentId
      foreach (DataTable asTable in asDs.Tables)
      {
        foreach (DataRow asRow in asTable.Rows)
        {
          foreach (DataColumn asColumn in asTable.Columns)
          {
            foreach (DataTable qTable in qDs.Tables)
            {
              foreach (DataRow qRow in qTable.Rows)
              {
                foreach (DataColumn qColumn in qTable.Columns)
                {
                  DataSet sqDs = _dbConnector.GetSkillsForQueue(qRow[qColumn].ToString());
                  foreach (DataTable sqTable in sqDs.Tables)
                  {
                    foreach (DataRow sqRow in sqTable.Rows)
                    {
                      foreach (DataColumn sqColumn in sqTable.Columns)
                      {
                        foreach (string skill in sqRow[sqColumn].ToString().Split(paramDelimStr))
                        {
                          if (skill == asRow[asColumn].ToString())
                          {
                            try
                            {
                              //Add it to the table
                              _dbConnector.SetAgentQueueSkill(aRow[aColumn].ToString(),
                                qRow[qColumn].ToString(),
                                skill);
                            }
                            catch { continue; }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Not long after digging into the logic, Chris was able to come up with a super-speedy fix such that it would finish in about a minute, even when it was run for all agents.

Thrilled to have such tangible, immediate results at their fingertips, management showered Chris with accolades galore. Chris could only reply"Awww, no problem, it was nothing!" and he wasn't kidding - all he did was replace the foreach loops with a single SQL query.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!