testing done.

4 loops.

random IPs checked, each of which occur in the database, so no negative hits.

each cycle uses the same ips for each type of query.

100 at a time

1 million in DB

same table for all methods

ipstring is a string lookup

ipnum is my method

ippadded is SBs method

ipssplit is each octet in a separate column

nothing done with query results.

Average

ipstring: 36 31 32 30 31 29 30 31 31.25

ipnum: 31 29 38 33 27 30 27 34 31.125

ippadded: 49 37 38 40 39 38 36 41 39.75

ipsplit: 26 28 26 26 24 24 23 25 25.25

So here is the surprise... As a string, or as a bigint number, the lookup is the same.... wtf? is the MOD math all that difficult? i guess so...

padding is the slowest, sorry SB.

but here is the other surprise. 4 column lookup is the fastest by about 20% over the ipnum method.

Im gonna try making it 10million rows and lookup 1k at a time. see that the numbers stay the same.

Each loop is exactly the same, except for the conversions. here is a code fragment of the important stuff for review.

'======= ipstring

t=now

for i = 0 to ubound(arr)

sql="select top 1 * from ips where ipstring='"&arr(i)&"'"

db.execute sql

next

response.write "ipstring: "&datediff("s",t,now)&"<br>"

'======= ipnum

t=now

for i = 0 to ubound(arr)

sql="select top 1 * from ips where ipnum="&ip2num(arr(i))

db.execute sql

next

response.write "ipnum: "&datediff("s",t,now)&"<br>"

'======= ipPadded

t=now

for i = 0 to ubound(arr)

sql="select top 1 * from ips where ippadded="&padded(arr(i))

db.execute sql

next

response.write "ippadded: "&datediff("s",t,now)&"<br>"

'======= ipsplit

dim A

t=now

for i = 0 to ubound(arr)

a=split(arr(i),".")

sql="select top 1 * from ips where ip1="&a(0)&" and ip2="&a(1)&" and ip3="&a(2)&" and ip4="&a(3)

db.execute sql

next

response.write "ipsplit: "&datediff("s",t,now)&"<br>"

function padded(ip)

dim iparr,ippadded,k

iparr=split(ip,".")

ippadded=Right("00" & CStr(iparr(0)),3)&Right("00" & CStr(iparr(0)),3)&Right("00" & CStr(iparr(0)),3)&Right("00" & CStr(iparr(0)),3)

padded=ippadded

end function

function IP2Num(tsip)

dim str1,str2,str3,str4,sip

dim num

'IP2Num=0

sip=tsip

if isnumeric(left(sip,2)) then

str1=left(sip,instr(sip,".")-1)

sip=mid(sip,instr(sip,".")+1)

str2=left(sip,instr(sip,".")-1)

sip=mid(sip,instr(sip,".")+1)

str3=left(sip,instr(sip,".")-1)

str4=mid(sip,instr(sip,".")+1)

num=cint(str1)*256*256*256+cint(str2)*256*256+cint(str3)*256+cint(str4)

IP2Num = num

end if

end function