IP/Subnet Query Question

[cokparae]

Hi,

Problem:

Given a table like this:
Create Table IPList
(
IPAddress nvarchar(15), -- e.g. 192.168.1.1
SubnetMask nvarchar(15), -- e.g. 255.255.255.0
)
then given an IP address (e.g. 192.168.3.1)
find the row(s) - if any, from IPList where the given IP falls in the given
subnet. If not decrement by 1 until the subnet is found.

Ideas:
I can think of a couple of ways to implement this.

Idea #1: Create a UDF that checks the data for a match, like this:

select ... where dbo.MatchIP( table.IPAddress, Table.SubnetMask, @GivenIP ) =
1
-- assuming @givenIP is a nvarchar representation

Idea #2: (probably), add another field to the table:
MaskedIP int -- integer result of bitwise AND of IPAddress & Subnet

query like this:
select ... where ( table.MaskedIP & @GivenIP ) = table.MaskedIP.
-- assuming @givenIP is an integer representation

Given that background, here is my question:

Speed is of the essence, the query has to run FAST.

Has anyone implemented either of these ideas, or a different solution to a
similar problem? If so, how did it work out?

Any assistance would be great.

C

 

[Michel Walsh]

If the IP are stored as one long integer, you can write a program that find
the min and max integer that matches the ip given the mask. In your case:

192.168.1.0 to 192.168.1.255

Then, a simple criteria (between) and an ORDER BY seems to be able to do
the job, as fast as an index can perform. The program supplying the ip_min
and ip_max value does not have to be written in SQL, I assume. So, if you
really want only valid addresses, sure, you can remove 0 and 255 (all bits
to 0 and all bits to 1) as valid host addresses.


Probably a similar technique can be used if the IP is stored as four octets:
get (as parameter) the min and max value for each field.


Now, if the value is stored as alphanumerical, I am afraid that no index can
be really used, and thus, on a long table,... that would be simply slow.



Vanderghast, Access MVP