How to memcache or store key value pair when key is in range and not a single value?

Question

We have a table in our database that has static ip range of cities and their ip address. It looks something like:

IP-TO, IP-FROM, CITY

100, 110, A

111, 168, B

...

965, 1000, Z

I have mentioned sample data. The real data is huge with almost 64k rows in the table.

For each user on our website, we determine their city from their IP address by executing sql query on sql express server.

Since the data is static, for example every user with IP in range 100 to 110 belongs to city A, we are unnecessarily hitting database every time.

We were thinking of caching every unique ip visit. For example: IP-100 mapped to A IP-101 mapped to A ... IP-110 mapped to A

But this would create 64k keys in memcache and i feel there is no point of storing multiple keys that hold same value when we know the range.

Can we somehow do this in a better way i.e. by minimum mem cache key or using a different approach altogether?

In the example I see there is a difference of 10 in each IP range. Does the range always have a constant value? — Bharat Gupta, Apr 07 '16 at 18:24
No. This is just sample data. Its not like that. I would modify that for better clarity. — Sahil Sharma, Apr 07 '16 at 18:25

score 0 · Answer 1 · answered Apr 07 '16 at 18:54

A list of IP addresses can be sorted (since they are, essentially, numbers). If you have a large non-overlapping list of IP ranges, you can sort them into one big list. If you have a big, sorted list of values, you can do a binary search on it. With 64k items, you can search the whole list in about 16 comparisons (practically instant).

With the right indexes and queries, your DB might be able to do this for you. If you think it could be faster another way (hint: use profiling to determine if it really is!) or are concerned about the extra trips to the database, you might cache the whole table's data in memory, and search the list. In high-level terms:

public class IPRangeCache
{
    private List<IPRangeRecord> sortedRangeRecords = null; // get from database

    public string GetCity(IPAddress ip) {
        // binary search to find from sortedRangeRecords
    }
}

The binary search will need to consider both the start and end numbers. A custom comparator or a custom binary search should make this doable. This should be very fast.

You could also try caching the last few minutes' worth of IP addresses in a dictionary, but I think it's unlikely to be any faster.

Bharat Gupta · Answer 2 · 2016-04-07T19:15:03.803

We can use C# generic dictionary.

We create a class that holds the IP range. This class will act as a key to the dictionary.

class IP_Range 
{
    public int MinIP { get; set; }
    public int MaxIP { get; set; }
}

We will then have to create a comparer class which will help in comparing the keys of the dictionary.

class IP_RangeComparer : IEqualityComparer<IP_Range>
{
    public bool Equals(IP_Range r1, IP_Range r2)
    {
        return (r1.MinIP == r2.MinIP && r1.MaxIP == r2.MaxIP);
    }

    public int GetHashCode(IP_Range r)
    {
        return r.MinIP.GetHashCode();
    }
}

We can then create a generic dictionary and use it as below:

IDictionary<IP_Range, string> myCache = new Dictionary<IP_Range, string>(new IP_RangeComparer());

// Adding entries
myCache.Add(new IP_Range() { MinIP = 100, MaxIP = 110 }, "A");
myCache.Add(new IP_Range() { MinIP = 111, MaxIP = 168 }, "B");
myCache.Add(new IP_Range() { MinIP = 169, MaxIP = 200 }, "C");

// Reading the dictionary
string city = myCache[new IP_Range() { MinIP = 169, MaxIP = 200 }];

Refer this article for further explanation.

Note: To find out the key for the particular IP you are searching for, you will have to iterate over the myCache.Keys collection.

score 0 · Answer 3 · answered Apr 07 '16 at 19:27

You can create an instance of the IpAddress class from the value, and then use just one of the bytes as the cache key. That way you're only going once to the database for 001.xxx.xxx.xxx, once for 002.xxx.xxx.xxx, etc.

var address = new IPAddress(value);
var bytes = address.GetAddressBytes(); //an array of four bytes

How to memcache or store key value pair when key is in range and not a single value?

3 Answers3