Can this script run faster?

[tempman]

expandcollapse popup

#include <Excel.au3>

$Log= @ScriptDir & "\log.xlsx" ; containing 620 (sometimes more, sometimes less) ip entries from A2 to A621
$oExcel_log= _ExcelBookOpen($Log, 0) ; open excel file as not visible

$aArray_log = _ExcelReadSheetToArray($oExcel_log, 1, 1, 0, 1) ; create a 2D array from the A column
$Max_log = $aArray_log[0][0] ; read how many rows are in column A
MsgBox(16, "Max_log", $Max_log) ; info msg with number of rows

$Geo= @ScriptDir & "\geo.xlsx" ; containing 178546 entries in from of A2 = 16777216, B2 = 16777471, C2 = Australia, for more info visit http://dev.maxmind.com/geoip/geolite
$oExcel_geo = _ExcelBookOpen($Geo, 0) ; open excel file as not visible

$aArray_geo = _ExcelReadSheetToArray($oExcel_geo, 1, 1, 0, 3) ; create a 2D array from the A, B, C columns
$Max_geo = $aArray_geo[0][0] ; read how many rows are in column A
MsgBox(16, "Max_geo", $Max_geo) ; info msg with number of rows

Local $begin = TimerInit() ; start of calculating time difference in milliseconds.

Local $i_log = 2 ; log.xlsx reading start ponit
Do ; loop
$IP = $aArray_log[$i_log][1] ; taking IP from log.xlsx
$temp = StringSplit($IP,'.') ; splits up a string into substrings
$decIP = ($temp[1]*256*256*256)+($temp[2]*256*256)+($temp[3]*256)+$temp[4] ; converting IP to decimal value, more info https://www.ultratools.com/tools/decimalCalc

For $i_geo = 2 To $Max_geo ; geo.xlsx reading start ponit, Loop
If $decIP >= $aArray_geo[$i_geo][1] And $decIP <= $aArray_geo[$i_geo][2] Then $dummy = 1 ; commparing decimal IP with values from geo.xlsx and define walue just to script go on
Next

$i_log = $i_log + 1
Until $i_log = $Max_log ; Do until read all rows in log.xlsx

Local $dif = TimerDiff($begin) ; end of calculating time difference in milliseconds.
MsgBox(0, "Time Difference", $dif) ; 142 sec <<<<<<< TO SLOW!!!

; closing excel files
_ExcelBookClose($oExcel_log, 0, 0)
$oExcel_log.Application.Quit
_ExcelBookClose($oExcel_geo, 0, 0)
$oExcel_geo.Application.Quit

Exit

[tempman]

Anyone?!?

[Melba23]

tempman,

Please do not bump your own threads within 24 hours.

Remember this is not a 24/7 support forum - those who answer are only here because they like helping others and have some time to spare. You just have to wait until someone who knows something about your particular problem, and is willing to help, comes online. Be patient and someone will answer eventually.

M23

[DXRW4E]

try with (uses StringRegExp, will have to be faster)

Edited March 4, 2013 by DXRW4E

[tempman]

tempman,

Please do not bump your own threads within 24 hours.

Remember this is not a 24/7 support forum - those who answer are only here because they like helping others and have some time to spare. You just have to wait until someone who knows something about your particular problem, and is willing to help, comes online. Be patient and someone will answer eventually.

M23

And what if those who know answer, wasn't here yesterday and do not have habit to read a day before topics?!?

[Melba23]

tempman,

Then you are stuffed! But we still ask that you do not bump within 24 hrs.

M23

[guinness]

And what if those who know answer, wasn't here yesterday and do not have habit to read a day before topics?!?

What argument is that? If people want to help you or others they will read the help forum topics, if they don't ,then they won't. Simple.

I saw it today and was about to help you, but then you bumped the topic. Anyway, search my signature as I have parsed that IP database before.

[tempman]

Anyway, search my signature as I have parsed that IP database before.

It's too slow man, it's too slow!

It works fine for 4-5 entries, but I am dealing here almost always with more then a 10 000 entries...

Edited March 4, 2013 by tempman

[guinness]

My code is slow? Oh well.

[jchd]

Yes, do as Moderator Melba23 says.

Just like guinness I was about to answer yesterday but got distracted elsewhere.

By the time I compose this I see that you don't seem happy with what we came up with on a very similar problem.

[czardas]

And what if those who know answer, wasn't here yesterday and do not have habit to read a day before topics?!?

Most of the time topics that can be resolved are answered. A topic may not be resolved if it is: impossible to resolve, too difficult or breaks the forum rules. Not everyone has lots of time to spare on other people's problems, since they are often busy with their own projects, and personal life - I was interrupted when writing this and four responces have been added in the mean time. I don't see a way to significantly increase speed.

I don't fully understand what you are doing. Can you exit this loop when the condition is met? - The answer is yes.

For $i_geo = 2 To $Max_geo ; geo.xlsx reading start ponit, Loop
If $decIP >= $aArray_geo[$i_geo][1] And $decIP <= $aArray_geo[$i_geo][2] Then $dummy = 1 ; commparing decimal IP with values from geo.xlsx and define walue just to script go on
Next

Edited March 4, 2013 by czardas

[tempman]

Look people, I didn't come here to start up a fight or troll, I came here to find a solution for my problem!

My script for job that take 5-6 sec for similar .NET app last 142 sec and I want that time to cut.

So I am not a full time programmer, I use AutoIt occasionally to make my full time job easier...

I don't fully understand what you are doing. Can you exit this loop when the condition is met?

For $i_geo = 2 To $Max_geo ; geo.xlsx reading start ponit, Loop
If $decIP >= $aArray_geo[$i_geo][1] And $decIP <= $aArray_geo[$i_geo][2] Then $dummy = 1 ; commparing decimal IP with values from geo.xlsx and define walue just to script go on
Next

I put that $dummy just to make things easier, to make code more accesible to view and to concentrated on my problem.

If I put all other lines (more then 500) than it will be hard to see what is going on...

[czardas]

You don't get my point. The code is meaningless if you don't do something at the moment the condition is met. Whatever that is - I have no idea. I can only increase the speed of the script you posted. Using ExitLoop there is necessary unless you intend to reset the value $dummy within the loop. Providing the condition is met early on, the increase in speed will be significant with the code you posted.

Edit: Just read the next post.

Edited March 4, 2013 by czardas

[Melba23]

tempman,

Look people, I didn't come here to start up a fight or troll

Arguing with a Moderator does not seem very sensible in that case - you decided to go down that route with the consequences you now see.

M23

[tempman]

@ czardas

Is it ok with you to pm you my script with all .xlsx files (3.6MB)?

Edited March 4, 2013 by tempman

[czardas]

@ czardas

Is it ok with you to pm you my script with all .xlsx files (3.6MB)?

Not really. I have a busy schedule ATM.

Find out what exactly is slowing your script down, and then ask a question with your reproducer code.

Edited March 4, 2013 by czardas

[tempman]

:idiot:

[czardas]

Well, it was polite of you to ask. Try to stay on the rails. Oh boy!

Edited March 4, 2013 by czardas

[jchd]

First I'd recommend that you calm down as cynism isn't going to solve anything.

Second, don't compare compiled managed code with an interpreter like AutoIt. Result is obvious.

Third, realize that it's pointless to keep on comparing an unknown entry to *every* entry in you geo table: set some $location variable (initialized to empty) to the location once found. That will speed up the search by a factor 2 (assuming entries get spread evenly and entries are random, which are dubious assumptions).

Then, why do you compare both min and max value for each entry? Skip reference locations until unknown IP is larger than (min of $i + 1) and check that it sits between entry min and max of entry $i. That will again be a twofold improvement.

Also, double-check your geo IP table: it's very unlikely that they are not full class-b entries. Conversion of addresses to full 32-bits and comparison on such low grain is pointless.

Finally, sort geo IP table by min IP (ranges don't normally overlap!) and use dichotomy to locate the right location. This will reduce the search to O(log₂ n).

As a last resort and since IP ranges don't change often, you'd better build an SQLite DB wich you can load to memory and query in few milliseconds. I can help you doing that.

You can see we're not completely unhelpful.

[Myicq]

Did not have time to study your code fully - but I myself sometimes find that external tools are better suited to process hugh data sets very fast - and I use them if I get there faster and better.

AutoIT is perfect for many things, but many not be for all things.

For data processing, have a look at f.ex GnuWin unixUtils for windows. Awk, sed, tr, grep and their relatives can grind data incredibly fast. Plus the SQLite DB in memory.

Like a swiss knife can drive nails.. a hammer is often better.