title: How to optimize the performance of comparisons for large data-sets?
body: <p>I have a method that is pretty slow and I am not sure how to optimize this. I also don't really know how LINQ works, so if the solution is to use LINQ, please explain. Thanks a lot.</p>
  
  <p>The DataTable <code>dtExcel</code> in the parameter of the method contains first part of data, the other is <code>_dt</code> from database. 
  The data that runs through the two for loops is about following: 700 (<code>dtExcel</code>) * 10,000 (<code>_dt</code>) = 7,000,000 comparisons. </p>
  
  <p>Here the code: </p>
  
  <pre><code>public async Task&lt;DataTable&gt; GetAdressesFromDB(DataTable dtExcel)
  {
      try
      {
          return await Task.Run(() =&gt;
          {
              CurrentProgress = 0;
              ProgressbarDBVisible = true;
  
              _dtFoundDuplicates.Clear();
  
              _dt = new DataTable();
  
              _dt = DBConn.GetAllAddresses(dtExcel);
  
              ProgressMaximum = dtExcel.Rows.Count;
  
              for (int i = 0; i &lt; dtExcel.Rows.Count; i++)
              {
                  CurrentProgress++;
                  for (int y = 0; y &lt; _dt.Rows.Count; y++)
                  {
                      // Criteria to check duplicates
                      string compareAdressExcel = "";
                      string compareAdressDB = "";
  
                      // Get the setted filter criteria and create both excel and db compare strings
                      string[] criteriaFields = ConfigurationManager.AppSettings["strFilter"].Split(',');
                      foreach (String cField in criteriaFields)
                      {
                          string cFieldTrimmed = cField.Trim();
                          if (cFieldTrimmed == "Strasse")
                          {
                              compareAdressExcel += dtExcel.Rows[i][cFieldTrimmed].ToString()
                                                   .ToLower()
                                                   .Replace(" ", "")
                                                   .Replace("str", "strasse")
                                                   .Replace("straße", "strasse")
                                                   .Replace("str.", "strasse");
                              compareAdressDB += _dt.Rows[y][cFieldTrimmed].ToString()
                                                 .Replace(" ", "")
                                                 .ToLower()
                                                 .Replace("str", "strasse")
                                                 .Replace("straße", "strasse")
                                                 .Replace("str.", "strasse");
                          }
                          else
                          {
                              compareAdressExcel += dtExcel.Rows[i][cFieldTrimmed].ToString().Replace(" ", "").ToLower();
                              compareAdressDB += _dt.Rows[y][cFieldTrimmed].ToString().Replace(" ", "").ToLower();
                          }
                      }
  
                      // If the company doesn't exists in Database, the contact to that company found in excel
                      // automatically won't exist either. Otherwise, check if contact exists.
                      if (compareAdressExcel == compareAdressDB)
                      {
                          string strOneExistTwoNot = "2";
  
                          if (!string.IsNullOrWhiteSpace(dtExcel.Rows[i]["FirstName"].ToString().Trim()) &amp;&amp; 
                              !string.IsNullOrWhiteSpace(dtExcel.Rows[i]["LastName"].ToString().Trim()))
                          {
                              strOneExistTwoNot = _crm.CheckContactExists(Convert.ToInt32(_dt.Rows[y]["AdressNummer"].ToString().Trim()),
                                                  dtExcel.Rows[i]["FirstName"].ToString().Trim(), 
                                                  dtExcel.Rows[i]["LastName"].ToString().Trim());
                          }
  
                          // Check if CheckContactExsists was successful
                          if (strOneExistTwoNot != "1" &amp;&amp; strOneExistTwoNot != "2")
                          {
                              throw new Exception(strOneExistTwoNot);
                          }
  
                          // If Contact exists, mark row and add duplicate row,
                          // otherwise only add duplicate row
                          if (strOneExistTwoNot == "1")
                          {
                              dtExcel.Rows[i]["ContactExists"] = 1;
                              _dtFoundDuplicates.Rows.Add(dtExcel.Rows[i]["ID"], _dt.Rows[y]["AdressNummer"], "1");
                          }
                          else
                          {
                              _dtFoundDuplicates.Rows.Add(dtExcel.Rows[i]["ID"], _dt.Rows[y]["AdressNummer"], "0");
                          }
                          dtExcel.Rows[i]["AdressExists"] = 1;
                      }
                  }
              }
              ProgressbarDBVisible = false;
              return dtExcel;
          });
      }
      catch (Exception ex)
      {
          throw ex;
      }
  }
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <p>Alright, so with the help of <code>@dlxeon</code>s answer I tried to normalize my data outside of my two for loops. I also tried to use a Dictonary to improve comparing speed. What I can't do right now is normalizing the database and making single statements instead of retrieving whole table. Thank you all for helping. Please tell me if there is still room from improvement in <strong>code</strong>.</p>
  
  <p>New code:</p>
  
  <pre><code>public async Task&lt;DataTable&gt; GetAdressesFromDB(DataTable dtExcel)
          {
              try
              {
                  return await Task.Run(() =&gt;
                  {
                      CurrentProgress = 0;
                      ProgressbarDBVisible = true;
  
                      _dtFoundDuplicates.Clear();
  
                      _dt = DBConn.GetAllAddresses();
  
                      ProgressMaximum = dtExcel.Rows.Count;
  
                      // Normalization
                      string[] criteriaFields = ConfigurationManager.AppSettings["strFilter"].Split(',').Select(x =&gt; x.Trim()).ToArray();
  
                      Dictionary&lt;int, string&gt; excelAddresses = new Dictionary&lt;int, string&gt;();
                      for (int i = 0; i &lt; dtExcel.Rows.Count; i++)
                      {
                          StringBuilder compareAdressExcel = new StringBuilder();
                          foreach (String cFieldTrimmed in criteriaFields)
                          {
                              if (cFieldTrimmed == "Strasse")
                              {
                                  var normalizedValue = dtExcel.Rows[i][cFieldTrimmed].ToString()
                                      .ToLower()
                                      .Replace(" ", "")
                                      .Replace("str", "strasse")
                                      .Replace("straße", "strasse")
                                      .Replace("str.", "strasse");
                                  compareAdressExcel.Append(normalizedValue);
                              }
                              else
                              {
                                  compareAdressExcel.Append(dtExcel.Rows[i][cFieldTrimmed].ToString().Replace(" ", "").ToLower());
                              }
                          }
                          excelAddresses.Add(i, compareAdressExcel.ToString());
                      }
                      Dictionary&lt;int, string&gt; dbAddresses = new Dictionary&lt;int, string&gt;();
                      for (int i = 0; i &lt; _dt.Rows.Count; i++)
                      {
                          StringBuilder compareAdressDB = new StringBuilder();
                          foreach (String cFieldTrimmed in criteriaFields)
                          {
                              if (cFieldTrimmed == "Strasse")
                              {
                                  var normalizedValue = _dt.Rows[i][cFieldTrimmed].ToString()
                                      .ToLower()
                                      .Replace(" ", "")
                                      .Replace("str", "strasse")
                                      .Replace("straße", "strasse")
                                      .Replace("str.", "strasse");
                                  compareAdressDB.Append(normalizedValue);
                              }
                              else
                              {
                                  compareAdressDB.Append(_dt.Rows[i][cFieldTrimmed].ToString().Replace(" ", "").ToLower());
                              }
                          }
                          dbAddresses.Add(i, compareAdressDB.ToString());
                      }
  
                      foreach (var exAdd in excelAddresses)
                      {
                          CurrentProgress++;
  
                          foreach (var dbAdd in dbAddresses)
                          {
                              // If the company doesn't exists in Database, the contact to that company found in excel
                              // automatically won't exist either. Otherwise, check if contact exists.
                              if (exAdd.Value == dbAdd.Value)
                              {
                                  string strOneExistTwoNot = "2";
  
                                  if (!string.IsNullOrWhiteSpace(dtExcel.Rows[exAdd.Key]["FirstName"].ToString().Trim()) &amp;&amp; 
                                                                 !string.IsNullOrWhiteSpace(dtExcel.Rows[exAdd.Key]["LastName"].ToString().Trim()))
                                  {
                                      strOneExistTwoNot = _crm.CheckContactExists(Convert.ToInt32(_dt.Rows[dbAdd.Key]["AdressNummer"].ToString().Trim()), 
                                                                                  dtExcel.Rows[exAdd.Key]["FirstName"].ToString().Trim(), 
                                                                                  dtExcel.Rows[exAdd.Key]["LastName"].ToString().Trim());
                                  }
  
                                  // Check if CheckContactExsists was successful
                                  if (strOneExistTwoNot != "1" &amp;&amp; strOneExistTwoNot != "2")
                                  {
                                      throw new Exception(strOneExistTwoNot);
                                  }
  
                                  if (strOneExistTwoNot == "1")
                                  {
                                      dtExcel.Rows[exAdd.Key]["ContactExists"] = 1;
                                      _dtFoundDuplicates.Rows.Add(dtExcel.Rows[exAdd.Key]["ID"], _dt.Rows[dbAdd.Key]["AdressNummer"], "1");
                                  }
                                  else
                                  {
                                      _dtFoundDuplicates.Rows.Add(dtExcel.Rows[exAdd.Key]["ID"], _dt.Rows[dbAdd.Key]["AdressNummer"], "0");
                                  }
                                  dtExcel.Rows[exAdd.Key]["AdressExists"] = 1;
                              }
                          }
                      }
                      ProgressbarDBVisible = false;
                      return dtExcel;
                  });
              }
              catch (Exception ex)
              {
                  throw ex;
              }
          }
  </code></pre>