next »

[KaptainKrayola]

Since everyone else was writing similar functions in their languages the Kaptain decided to take a few minutes to do the same so here you go:

Code:

     function ip2num(ip){
oct1 = listfirst(ip,'.');
oct2 = listgetat(ip,2,'.');
oct3 = listgetat(ip,3,'.');
oct4 = listlast(ip,'.');

returnThis = 1 & iif(len(oct1 LT 3),DE("#repeatstring('0',3-len(oct1))##oct1#"),DE("#oct1#")) & iif(len(oct2 LT 3),DE("#repeatstring('0',3-len(oct2))##oct2#"),DE("#oct2#")) & iif(len(oct3 LT 3),DE("#repeatstring('0',3-len(oct3))##oct3#"),DE("#oct3#")) & iif(len(oct4 LT 3),DE("#repeatstring('0',3-len(oct4))##oct4#"),DE("#oct4#")) ;

return returnThis;
}


function num2ip(num){
num = removechars(num,1,1);
oct1 = left(num,3);
oct2 = mid(num,4,3);
oct3 = mid(num,7,3);
oct4 = right(num,3);

returnThis = "";

for(i = 1; i LTE 4; i = i + 1){
currOct = evaluate("oct#i#");
for(z = 1; z LTE 3; z = z + 1){
if(left(currOct,1) EQ 0){
currOct = removechars(currOct,1,1);
}else{
if(i LT 4){
returnThis = returnThis & currOct & '.';
}else{
returnThis = returnThis & currOct;
}
writeOutput(currOct & "<br>");
break;
}
}
}
return returnThis;
}

Useage:

Code:

<cfoutput>
<cfset ipnum = ip2num("64.168.11.1")>
<cfset numip = num2ip(ipnum)>

#ipnum# - #numip#

</cfoutput>

Original IP : 64.168.11.1
ip2num: 1064168011001
num2ip: 64.168.11.1

Enjoy!

[perkiset]

Nice Kap'n - maybe if you have a moment you can do some of the speed trials that NBs, SB and me are doing as well... nice to have all of those stats...

Well done!
/p

[KaptainKrayola]

Aye, but you guys are gonna make the Kaptain's little brain hurt we know it.

give me a few and we can run some tests

[perkiset]

Aye, but you guys are gonna make the Kaptain's little brain hurt we know it.

It's my whole goddam raison d'etra.

[KaptainKrayola]

SPEED TEST RESULTS!!

Ok here are the conditions:

Web Server: Windows Server 2003 + IIS + ColdFusion MX7
Database Server: MS SQL 2005

3 tables -
Table 1 - 4 cols, 1 for each chunk of the ip - all tinyint datatype
Table 2 - 1 col for the ip number converted by the CF function above - bigint datatype
Table 3 - 1 col for the ip as it appears normally - varchar(15) datatype

Each table was filled with 100,018 randomly generated, unique, IPs

I ran 4 tests with 5 runs each.  Each run grabbed 50 random ips from the table and then looped over them querying for them and calculating the time it took to complete the loop and return the page to the browser.

Test 1, Table 1, no index set on any column and no PK - average return time 0.613 seconds
Test 2 Table 2, no index set on the column and no PK - average return time 1.12 seconds
Test 3, Table 3, no index set on the column and no PK - average return time 1.27 seconds
Test 4, Table 2, ASC and DESC index set on column and column is set as PK - average return time 0.069 seconds

Obviously the number of rows is pretty low but I got tired of running the scrip that was filling the tables over and over and over again so deal with it.  If someone wants to send me a huge list of IP's I'll be happy to run the tests again with a much later database.

Dunno if that is exactly what you were looking for Perkatron so if you want any other data let me know.

[perkiset]

Hey KK -

Certainly makes sense that the indexed version would come up the quickest... I'll be home later tonight and pop you out a list of 500K to thicken up your list. The tests that we were running were pitting the string against the num against the chunked in indexed situations - and then adding <whatever code was necessary to make the conversion> - from my test results in MySQL, the numeric array is far and away the quickest... but unless there is a way to convert the IP address to a num really quickly (natch, my C/PHP experiment) then it doesn't make sense, because the time gained is lost in local processer expense doing the conversion.

Nutballs is hot on the trail of seeing if MSSQL does 4 keys of all tiny int faster than anything else - and I am very curious to see how that goes as well.

Nice work!
/p

[KaptainKrayola]

Indexing the 4 tinyints was going to be my next experiment.  There really isn't any reason, at the point, that you can't have SQL server just return the ip structured as an IP. 

I am going to schedule my little IP generator to run every minute or so until tomorrow also that way i'll have a ton waiting for me in the morning.

i wonder if it would be quicker to have t-sql do the parsing...hmm.....One could write the same ip2num and num2ip functions in t-SQL and i bet SQL server is better at parsing the text than CF is.

great, thanks for getting me interested perk, bastard.

[KaptainKrayola]

ok real quick i added an index to each col in the chunky table and ran the test again

it averaged .41 seconds compared to .613 w/o any indexing on the table.  slight improvement but the bigint ipnumber is still tons faster for me at least.

[KaptainKrayola]

Ok here we go again

test - query for 50 IPs from the table with IPs as strings, loop over the 50, each iteration use the ip2num function to convert to ipnumber and query for ipnumber from the table with the indexed bigint col, then use num2ip function to convert back and print to screen.

Average request time - .052 seconds.  The longest running request I got was .078 seconds.  Even with using the 2 CF fuctions its still tons faster than the chunky table and the table with the strings by far.  It will be interesting to see how this scales with lots more rows.

[perkiset]

It would seem that NBs suggestion to store and index as numerics as opposed to strings is strong... even though he now is on the hunt for success with the 4 octets in seperate columns thang... seems like a load of work to me, but looking forward to his results as well.

This is great stuff Kapn... perhaps this, mine, NBs & SB's posts need to go into the new Tournament board...