CODEWITHSUNDEEP
c#.netdatabaseselectdatatable
Yeseanul
Yeseanul

Reputation: 2887

Cache a DB table in a .Net 2.0 app. What's the fastest way of accessing it?

I have a (static) table which I need to access very frequently. Therefore I cache it at run time.

For now I'm using a DataTable to store the information and the Select() to retrieve it (the filtering is usually spread over 2 or 3 columns). But this appears to be very (!) slow and is a major bottleneck.

Therefore: What is the best way of caching a DB table (in .Net 2.0) and how to access it the fastest?

[edit 1]

DataTable cache = new DataTable();
cache.Columns.Add("id", typeof(int));
cache.Columns.Add("data_id", typeof(int));
cache.Columns.Add("data", typeof(string));
cache.Columns.Add("value", typeof(float));

Eg:

DataRow[] result1 = cache.Select("id = 1 AND data_id = 2 AND data = 'some data'");
DataRow[] result2 = cache.Select("data = 'some other data'", "data desc");

[edit 2]

Having in mind the suggestion from @MichaelPerrenoud, I ended up implementing a Dictionary. Please have in mind that this design is with respect to the structure of the DB table that I needed cached.

Dictionary<string, Dictionary<NewObject, float>> cache;
class NewObject {int id; int data_id;}

This way the cache has as the [key] the data and as the [value] a new Dictionary. And this latter has as the [key] the id + data-id and as the [value] the value.

In accessing the data, this Dictionary is ~%90 faster than the DataTable.

Upvotes: 1

Views: 440

Answers (1)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

So I think a good solution for you would be something like this. I'm making the assumption that you have the DataTable after you initially get the data.

DISCLAIMER: I have not compiled this code - it's a concept - you may need to fix some syntax.

Dictionary<string, List<DataRow>> CacheData(DataTable t, params string[] fields)
{
    Dictionary<string, List<DataRow>> cache = new Dictionary<string, List<DataRow>>();
    StringBuilder key = new StringBuilder();

    foreach (DataRow r in t.Rows)
    {
        key.Clear();
        foreach (string f in fields)
        {
            if (key.Length > 0) { key.Append("_"); }
            key.Append(Convert.ToString(r[f]);
        }

        if (!cache.ContainsKey(key.ToString())) { cache.Add(key.ToString(), new List<DataRow>()); }
        cache[key.ToString()].Add(r);
    }
    return cache;
}

Now, you can store those dictionaries any way you see fit and then pull data out of them when you need to, so for example if you needed to handle the first example it might look something like this to create the cache:

CacheData(table, "id", "data_id", "data");

and then to get data out you would build a string like this:

string key = "1_2_some data";

and pull the list of rows out of the dictionary like this:

dictionary[key];

and that would return a List<DataRow> for you to use.

But, you'll only have to do this one time and then leverage those dictionaries over and over.

Upvotes: 1

Related Questions