next »

[perkiset]

NBs I guess I was a bit confused... I thought the test was more about the speed of the indexes than the code... in the example you give, the queries will be completely dependent on your function calls, which may be compiling into pretty expensive code. There's a lot of string code that's being called for every query, if I read that correctly.

Could you throw a test out that did something like this:

Pre-randomize all of the IPs you are going to look for ie., create an array of either 1M strings or 1M numerics. Then simply walk that array and look them up, doing no functional code inbetween each lookup - then we'll know the pure difference between a string and a numeric based index - which is what I thought the original notion was.

Then we'd know if it even makes sense to pursue better functions for converting that string into a number.

[nutballs]

the test i ran though is relevant to how you would actually use these lookups. I just ran in loops of 1000 so that I could get larger discrepancies to measure. This also ONLY tests lookups, which is what 90% of the usage of this would be for anyway. storage is a heavy hit no matter how you slice it, heavier even with indexes. But in the end the relevance is DB querytime plus manipulation time in code to prepare for the lookup. This is only for ClassicASP mindo you. It might be different for other langs. Another test to try would be the same exact thing but using stored procs. so something like a straight query such as "select top 1 * from table where ipnum=sp_ip2num('207.123.234.12')" which would give a more pure result, regardless of the interface language.

the records in SQL are not indexed, though i was planning on running them indexed as well.
For the Padded method, i tried a couple different manipulations, and the one i ran with performed the best it seemed. thats the right("00"&cstr(ip1),3) part obviously. i tried for loops, i tried a string of IF statements, though i dont know if i tried a select/case statement... hmm. i'll try that too. oh, and i guess i do have 2 of them, not being delivered via function. i will functionify those as well and post updated results.

every test is exactly the same, except for the method of lookup
t=now    '===set a start time
for i = 0 to ubound(arr) '===loop the entire array of IPs that I have pre-created for testing.
   sql="select top 1 * from ips where ipnum="&ip2num(arr(i)) '===build the SQL query
   db.execute sql '===run it, but return nothing
next
response.write "ipnum: "&datediff("s",t,now)&"
" '===write out the seconds of change.

[nutballs]

ok, update. and this is now weird.

I have made it so each test is as close to the same as possible, with the only variance being the method of lookup. each is called from a function to make sure the function call wasnt adding extra baggage to the process. I also tested all 3 padding methods i could think of.

I even made the query only request the number 1, so that row length doesnt even figure in.

so here are the results. Averages of 5 runs with 50 lookup each.


                          average seconds       pct faster then next up
split octets:          28                          30%
string:                 36.4                        4%
numeric:              38                           26%
padded Caseloop:  48                           1%
padded ifloop:       48.4                        7%
padded right:        51.8                        0%

Split 4 column octets works the fastest it seems.
but frankly im baffled. 2.4 million records, no indexing, and string based lookup is faster than numeric?!?!@??

functions for reference

Code:

function ipsplit(ip)
dim a,sql
a=split(ip,".")
sql="select top 1 '1' from ips where ip1="&a(0)&" and ip2="&a(1)&" and ip3="&a(2)&" and ip4="&a(3)
ipsplit=sql
end function

function ipstring(ip)
dim sql
sql="select top 1 '1' from ips where ipstring='"&ip&"'"
ipstring=sql
end function

function ippaddedright(ip)
dim iparr,sql
iparr=split(ip,".")
sql="select top 1 '1' from ips where ippadded="&Right("00" & iparr(0),3)&Right("00" & iparr(0),3)&Right("00" & iparr(0),3)&Right("00" & iparr(0),3)
ippaddedright=sql
end function

function ippaddedcase(ip)
dim iparr,sql,octet,pad
iparr=split(ip,".")
pad=""
for each octet in iparr
select case len(octet)
case 1 pad=pad&"00"&octet
case 2 pad=pad&"0"&octet
case 3 pad=pad&octet
end select
next
sql="select top 1 '1' from ips where ippadded="&pad
ippaddedcase=sql
end function

function ippaddedif(ip)
dim iparr,sql,octet,pad
iparr=split(ip,".")
pad=""
for each octet in iparr
if len(octet)=1 then
pad=pad&"00"&octet
elseif len(octet)=2 then
pad=pad&"0"&octet
else
pad=pad&octet
end if
next
sql="select top 1 '1' from ips where ippadded="&pad
ippaddedif=sql
end function

function IP2Num(ip)
dim str1,str2,str3,str4,sip,sql
sip=ip
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)
sql = "select top 1 '1' from ips where ipnum="&str1*256*256*256+str2*256*256+str3*256+str4
ip2num=sql
end function

[StephenBauer]

Sweet dude.  I didn't really mean you had to do it!    But it is way cool that you did.  This will come in handy a bit down the road and save me some time too.    has been credited to your PayPal account. 

Did you try the math using the binary shift at all? 

SB

[nutballs]

no i didnt try binary shifting since i couldnt figure out how to do it in ASP (mostly cause i havent bit shifted since college), and didnt really spend much time on the whole thing. only took me about an hour in front of the TV, refining stuff down and such.

im gonna guess though that 4 columns is going to be the best method, in any system, since i have thought about it a bit more. Converting to bigint is going to be the same math in any language, and would be the same steps. Padding also, though maybe certain languages may have faster implementation of IF/Case/For but i doubt it. Where as the 4 column method is a fixed array resulting from a split of the IP, which all languages should do about the same efficiency. The possible values are limited to 255 per column, meaning that little gain should be achieved from indexing i think. but im gonna try it anyway and post those results.

[esrun]

Maybe consider test flat file and use is_file