C
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
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