Excel read speed differences between AutoIt versions

[tpek]

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

[BrewManNH]

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.

[tpek]

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.

[guinness]

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.

[water]

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.)

[BrewManNH]

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.

[tpek]

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 September 26, 2012 by tpek

[tpek]

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 September 27, 2012 by tpek

[tpek]

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 September 27, 2012 by tpek

[water]

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)

[BrewManNH]

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.

[tpek]

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 September 28, 2012 by tpek

[trancexx]

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 September 28, 2012 by trancexx

[Ork]

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

expandcollapse popup

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 November 7, 2012 by Ork