Jump to content
Sign in to follow this  
tempman

Can this script run faster?

Recommended Posts

tempman

#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

Share this post


Link to post
Share on other sites
tempman

Anyone?!? :

Share this post


Link to post
Share on other sites
Melba23

tempman,

Please do not bump your own threads within 24 hours. :naughty:

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


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______Open Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
DXRW4E

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

Edited by DXRW4E

apps-odrive.pngdrive_app_badge.png box-logo.png new_logo.png MEGA_Logo.png

Share this post


Link to post
Share on other sites
tempman

tempman,

Please do not bump your own threads within 24 hours. :naughty:

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?!?

Share this post


Link to post
Share on other sites
Melba23

tempman,

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

M23


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______Open Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
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.


UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Share this post


Link to post
Share on other sites
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 by tempman

Share this post


Link to post
Share on other sites
guinness

My code is slow? Oh well.


UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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. :bike:

Edited by czardas

Share this post


Link to post
Share on other sites
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


Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______Open Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
tempman

@ czardas

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

Edited by tempman

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


Link to post
Share on other sites
tempman
:idiot: :idiot: :idiot:

Share this post


Link to post
Share on other sites
czardas

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

Edited by czardas

Share this post


Link to post
Share on other sites
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(log2 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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×