Hello all, I have the following challenge and need some help:
I have a SQL Server 2008 Database Table that has around 10-20 million records added per month. The current table size is around 200+ million records.
This table is used to keep track of Usage Data Records.
Each month we take the associated data and slice it and dice it for a number of reasons; reporting and billing. As part of the billing aspect we need to bill usage for IP addresses within a given IP range.
Note, this field is also currently not indexed.
For example only bill users with IP address range from 192.168.x.100 to 150
I'm at a loss at how to best process this efficiently. Does anyone have any recommendations on a good way to do this?
I thought about converting all the IP's into decimal, having an index on that and having another table with the full list of all possible matches. However, at the same time this seems inefficient. Especially if i was asked the following:
Get all uses against the following 10.x.x.100-150. This would have hundreds of thousands of combinations.
Look forward to responses. Thank you in advance