Jump to content

Excel read speed differences between AutoIt versions


tpek
 Share

Recommended Posts

Hello.

Here is very simple code that reads 5000 cells from xls file.

Question is what is the reason of so big speed differences between AutoIt version 3.3.6.1 and new versions like 3.3.8.1 and 3.3.9.4 beta?

Source and files compiled for both versions:

https://www.sugarsync.com/pf/D9930878_69481187_01893

#include <Timers.au3>
#include <Excel.au3>

$timer = _Timer_Init()
$o = _ExcelBookOpen(@ScriptDir & "\Base.xls", 0)
Dim $Array[5000]
For $i=0 To UBound($Array)-1
$Array[$i] = _ExcelReadCell($o, $i+1, 1)
Next
_ExcelBookClose($o)
MsgBox(0, "", "Time: " & _GetTimerDifference($timer))



Func _GetTimerDifference($timer)
Local $diff = Int(_Timer_Diff($timer)/1000)
Local $min = Floor($diff/60)
If $min == 0 Then Return $diff & "s"
Return $min & "m " & $diff-($min*60) & "s"
EndFunc
Link to comment
Share on other sites

You don't state whether it's faster/slower/about the same speed from one to the other. So it's hard to figure out what it is you're asking for.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

You don't state whether it's faster/slower/about the same speed from one to the other. So it's hard to figure out what it is you're asking for.

for version 3.3.6.1 it takes 4 sec.

for 3.3.9.4 - 20 sec.

Because this I still use version 3.3.6.1 for my big projects. Read 250 xls files in 2min instead 4.5min is very significant for me.

Link to comment
Share on other sites

Well V3.3.9.4 is beta so no surprises there really, it's still in development! Secondly, V3.3.8.1 is the latest stable release, not V3.3.6.1.

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

Link to comment
Share on other sites

If you need speed use native Excel functions. You can take the function from the UDF and strip off any unnecessary code (error checking etc.)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I believe the speed differences are because of something internal in AutoIt and how it handles the objects, because the code hasn't changed in the Excel.au3 file for _ExcelReadCell since 3.3.6.1, except for the addition of parentheses after the methods.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

If you need speed use native Excel functions. You can take the function from the UDF and strip off any unnecessary code (error checking etc.)

I did it. I wrote this in pure COM and result was the same. It looks like something was changed in COM handling. I did it many times on different machines and results was always the new versions of AutoIt are much slower in handling xls files.

Edited by tpek
Link to comment
Share on other sites

I believe the speed differences are because of something internal in AutoIt and how it handles the objects, because the code hasn't changed in the Excel.au3 file for _ExcelReadCell since 3.3.6.1, except for the addition of parentheses after the methods.

Exactly, but maybe it is possible to discover a reason of this and return to previous speed?

Edited by tpek
Link to comment
Share on other sites

OK. Something has been seriously changed in COM handling. Does anyone know what?

Here is 5000 cells write and write only time measurement. I wonder if this also applies to multi COM operations with another applications. NOTE: _ExcelWriteCell() is exactly the same in those Autoit versions.

Code:

#include <Timers.au3>
#include <Excel.au3>
$oExcel = _ExcelBookNew(0)
$timer = _Timer_Init()
For $i=1 To 5000
    _ExcelWriteCell($oExcel, $i, $i, 2)
Next
MsgBox(0, "", "Write time: " & Int(_Timer_Diff($timer)) & "ms")
_ExcelBookSaveAs($oExcel, @ScriptDir & "Result", "xls", 0, 1)
_ExcelBookClose($oExcel)

Results:

Autoit 3.3.6.1 - 4419ms

Autoit 3.3.8.1 - 24338ms

Autoit 3.3.9.4 - 24079ms

Edited by tpek
Link to comment
Share on other sites

IIRC there has been some discussion on COM performance when 3.3.8.1 came out.

To sum it up: "COM performance before 3.3.8.1 was better because COM error handling wasn't done the proper way in older versions".

I will check if I can find the thread.

I think you have to live with it or enhance your script by using a more efficient way to get the data (like using _ExcelReadArray or _ExcelReadSheetToArray)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

There was a problem in versions prior to 3.3.8.0, I'm not sure what the issue was, or what was done to fix it, but the fix causes a slow down for things like this. Although it's now slower, it's also now not broken.

Trak ticket (#2097) that I'm referencing, see the comment from trancexx.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

IIRC there has been some discussion on COM performance when 3.3.8.1 came out.

To sum it up: "COM performance before 3.3.8.1 was better because COM error handling wasn't done the proper way in older versions".

I will check if I can find the thread.

I think you have to live with it or enhance your script by using a more efficient way to get the data (like using _ExcelReadArray or _ExcelReadSheetToArray)

Thanks for answers. I understand there are operations that takes a lot of ms but in my opinion because of this, COM handling in Autoit becomes feature of questionable utility instead beautiful and useful feature that everyone loves. I can live with this in reading data from Excel because there are ways to read whole sheet in one COM query but I cannot accept this delay in multiple write and I do not know how to bypass this.

Autoit is my favorite tool that I love and I'm very interested in ability to change or re-think that error handling in COM.

Edited by tpek
Link to comment
Share on other sites

COM error handling has nothing to do with the slowdown you experience.

Previous code was bad and wrong. It was the cause of several major bugs and wasn't forward compatible.

Unfortunately script code was written without the knowledge of the possible issues. Still, the blame is not on the people writting that code, it was on us (devs) for not showing how to write the code correctly.

COM (Automation) APIs changed/evolved over time and because of the bad internal handling at some point AutoIt started to fail. That's why it was decided to redo things (mostly from scratch). What you have now is what you should have from day 1.

If however you choose to work with broken AutoIt, then you are welcome no to use the correct one. You will not have our support in any way, that's for sure.

As for the script code. This:

For $i = 1 to 10000
    $oObj.Something.SomethingElse.AndMore.Stuff()
Next

... is less correct than:

Local $oSub = $oObj.Something.SomethingElse.AndMore
For $i = 1 to 10000
    $oSub.Stuff()
Next
Edited by trancexx

♡♡♡

.

eMyvnE

Link to comment
Share on other sites

  • 1 month later...

Hello,

(I am author of error ticket 2097)

today I found that worse performance in 3.3.8.1 than in 3.3.6.1 probably is not problem with COM.

I try to test read file line-by-line and write file line-by-line on 3 different computers, first in AutoIT 3.3.6.1, afer reinstall to AutoIT 3.3.8.1

In 3.3.6.1 both operations reading and writing were more faster than in 3.3.8.1

local $readFilePath = "c:read.txt"
local $writeFilePath = "c:write.txt"
local $logFilePath
local $readFileHandle
local $writeFileHandle
local $logFileHandle
local $start, $end
local $totalRead, $totalWrite
local $line
local $count
local $runXtime, $runMaxtime = 20

;reading and writing
For $runXtime = 1 to $runMaxtime
$readFileHandle = FileOpen($readFilePath, 0)
$writeFileHandle = FileOpen($writeFilePath, 2)

$logFilePath = "c:log_" & @AutoItVersion & "(" & $runXtime & ").txt"
$logFileHandle = FileOpen($logFilePath, 2)
$count = 0
$totalRead = 0
$totalWrite = 0

While 1
;read line-by-line
$start = TimerInit()
$line = FileReadLine($readFileHandle)
If @error = -1 Then ExitLoop
$end = TimerDiff($start)
$totalRead += $end

;write line-by-line
$start = TimerInit()
FileWriteLine($writeFileHandle, $line)
$end = TimerDiff($start)
$totalWrite += $end
$count += 1
WEnd

FileClose($readFileHandle)
FileClose($writeFileHandle)

;write log
FileWriteLine($logFilePath, "version = " & @AutoItVersion)
FileWriteLine($logFilePath, "readed lines = " & $count)
FileWriteLine($logFilePath, "time to read = " & $totalRead)
FileWriteLine($logFilePath, "time to write = " & $totalWrite)
FileClose($logFileHandle)
Next
msgbox(0, "", "END")

File c:read.txt contains 1048576 lines with numbers from 1 to 1048576. Test run 20 times on both versions

3.3.6.1 - avg reading = 2.81 secs, avg writing = 4.92 secs

3.3.8.1 - avg reading = 3.82 secs, avg writing = 5.46 secs

In situation when you need I/O with many small files, stable version 3.3.8.1 can be really performance issue!

Edited by Ork
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...