A very long time of loading data. Large Amount of Data. SQL queries in C#

Question

I have the following code in C# WPF

ConsultaDB consulta = new ConsultaDB();
foreach (var item in lista)
{
    var cp = consulta.returnCP(item.Key);
    if (cp.Length != 5)
    {
        //Some code here with the data returned
    }
}

list is a collection of >100K elements, and the ConsultaDB object have the following code:

class ConsultaDB
{
    string CP;
    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CCPP.accdb");
    public string returnCP(string id)
    {
        var comm = conn.CreateCommand();
        comm.CommandType = CommandType.Text;
        comm.CommandText = "SELECT CP FROM CP WHERE ID='" + id+ "'";
        var returnValue = comm.ExecuteScalar();
        CP = returnValue.ToString();
        return CP;
    }

    public ConsultaDB()
    {
        conn.Open();
    }
}

The problem here is that all that requests to the DB consume a large amount of time to be completed. I saw that the loop is working well, but for sure is not optimized.

So, how can I improve the speed of that process?

Answer 1

You can try caching the whole CP table in a dictionary:

class ConsultaDB {
  private static Dictionary<String, String> s_Data = new
    Dictionary<String, String>();

  private static void CoreFeedCache() {
    using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CCPP.accdb")) {
      using (var comm = conn.CreateCommand()) {
        comm.CommandText = 
          @"select ID,
                   CP
              from CP";

        using (reader = comm.ExecuteReader()) {
          while (reader.Read()) {
            s_Data.Add(Convert.ToString(reader[0]), Convert.ToString(reader[1]));
          }  
        }
      }
    }
  } 

  static {
    CoreFeedCache();
  }

  public static string returnCP(string id) {
    String result;

    if (!s_Data.TryGetValue(id, out result))
      result = null;

    return result;
  } 
}

If CP has about 100K items as well, it will require MegaBytes of RAM.