New Excel UDF faster on older computer?

[corgano]

There seems to be a major decrease of speed between UDF's for me, and I'm not sure why. A script that reads values and enters them into text files became a lot slower after I re-wrote it to use the new UDF. I thought at first it was just my computer being slow, but then I brought it to work and it completed the entire script in about .9 seconds - on an older xp computer, to boot! Maybe it was my windows version, or the version of excel I have?

 

Slower:

Windows 7, 64 bit, service pack 1

Excel / office 2007

AMD athlon 2 dual core

4GB ram

System is on a SSD

 

Faster:

Win xp (32 bit?)

Excel / office 2003

2.0 ghtz, dual core

2GB ram

 

The slower system SHOULD be faster, because everything is newer, but is isn't?

Edited August 18, 2014 by corgano

[water]

Can you please post your test script?

[corgano]

Here is the main bit of code dealing with excel. The for loop inside it takes about 300ms on the xp machine to complete, but almost a minute on my win 7 laptop.

expandcollapse popup

;Try to attach to the
    $oWorkbook = _Excel_BookAttach($File)
    If $oWorkbook = 0 Then $oWorkbook = _Excel_BookOpen($oExcel, $File)
    If @error Then
        _Excel_Close($oExcel)
        ConsoleWrite("Failed to open "&$File&"! "&@error&@CRLF)
        Exit
    EndIf

    $s = _Excel_RangeRead($oWorkbook, Default, $Colum&"60")
    ConsoleWrite($s&@CRLF)
    If StringInStr($s, "|"&$Till&"|") Then
        ;Prompt the user, and see what their reply is. Switch works like a lot of If...then, elseif....then statements.
        Switch MsgBox(3+32+256, "Error", "There appears to already be data entered for this date:"&@CRLF&_DateToMonth($ZedMonth,1)&" "&$ZedDay&@CRLF&"Would you like to overwrite?")
            Case 2 ;If they hit cancel, close the program
                Exit
            Case 7 ;If they hit no, continueloop will skip this zed file and go on to the next one
                ConsoleWrite("Skipping Zed for "&_DateToMonth($ZedMonth,1)&" "&$ZedDay&" (already entered)"&@CRLF)
                ContinueLoop
            Case 6
                $Overwrite = 1
        EndSwitch
    EndIf

    _Excel_RangeWrite($oWorkbook, Default, _DateToMonth($ZedMonth,1)&". "&StringTrimLeft("00",StringLen($ZedDay))&$ZedDay&"th/"&StringRight($ZedYear,2), $Colum&"5") ;Write to the Cell

    $ZedText = FileRead($ZedFolder&"\"&$ZedYear&"\"&$ZedMonth&"\Z005_"&$ZedDay&" .CSV")

    ;Fill in the top/middle values of the sheet.
    ;   This works by looking at the headings in colom A
    ;   It then checks the ini file to translate the heading to the zed name (If different)
    ;       There is a special step in here where the ini can return multiple things
    ;       Groceries is an example, where it has taxed and non taxed variants in the zed
    ;   It then looks up each heading in the zed, and whatever value it gets it puts on the sheet
    For $iRow = 7 to 30
        ;Clear the search and return values
        $Search = ""
        $Ret = ""

        ;read the name of the item we want to look up off the zed
        $Search = _Excel_RangeRead($oWorkbook, Default, "A"&$iRow)

        ;See if there's an override in the ini to change the name to something else
        $Search = IniRead($Ini, "Override", $Search, $Search)
        ConsoleWrite("      Checking Zed for row "&$iRow&', Heading(s) "'&$search&'"'&@CRLF)
        ;split into an array of items, to hangle things like grocery that have more than one section in the Zed
        $Search = StringSplit($Search, ",", 2)

        ;Search for all items for this item in the zed. Most will have one
        For $i in $Search
            ;First clean the item name, so the pattern doesn't get messed up
            $i = stringreplace($i, "+", "\+")
            $i = stringreplace($i, "$", "\$")
            Switch $i
                Case "%-"
                    $a = StringRegExp(FileRead($ZedFolder&"\"&$ZedYear&"\"&$ZedMonth&"\Z002_"&$ZedDay&" .CSV"), '"'&$i&'\s*?","(\d*?)","([^"]*)"', 3)
                    $a[1] = stringreplace($a[1], ",", "") * -1
                Case "CUST"
                    $a = StringRegExp(FileRead($ZedFolder&"\"&$ZedYear&"\"&$ZedMonth&"\Z001_"&$ZedDay&" .CSV"), '"'&$i&'\s*?","(\d*?)","([^"]*)"', 3)
                    $a[1] = $a[0]
                Case "GST"
                    $a = StringRegExp(FileRead($ZedFolder&"\"&$ZedYear&"\"&$ZedMonth&"\Z001_"&$ZedDay&" .CSV"), '"'&$i&'\s*?","(\d*?)","([^"]*)"', 3)
                    $a[1] *= -1
                Case Else
                    ;Search for the pattern of text in the zed for that item
                    $a = StringRegExp($ZedText, '"'&$i&'\s*?","(\d*?)","([^"]*)"', 3)
            EndSwitch

            ;check if we get a hit. should be an array, $a[0] = number of items sold, $a[1] = amount of money
            If UBound($a) >= 2 Then
                ;Add it to the return amount
                $Ret += stringreplace($a[1], ",", "")
            Else
                ;Error message for debugging
                ConsoleWrite("      Error? Could not find "&$i&" in Zed "&$ZedFolder&"\"&$ZedYear&"\"&$ZedMonth&"\Z005_"&$ZedDay&" .CSV"&@CRLF)
                ConsoleWrite('      "'&$i&'\s*?","(\d*?)","([^"]*)"'&@CRLF)
            EndIf
        Next

        ;Read what is in the cell already, and add the number we found to it
        ;The reason we do this is in case we have multiple tills
        ;To prevent adding a till to the sheet twice, we have it
        If Not $Overwrite Then $Ret += _Excel_RangeRead($oWorkbook, Default, $Colum&$iRow)
        ;If it found anything, add it to the sheet
        If $Ret <> "" Then _Excel_RangeWrite($oWorkbook, Default, $Ret, $Colum&$iRow)

    Next


    _Excel_RangeWrite($oWorkbook, Default, StringReplace($s, $Till&"|", "")&$Till&"|", $Colum&"60")

    _Excel_BookSave($oWorkbook) ; Now we save it

The program is for taking the Z reports off Casio register SD cards, and compiling them into a template spreadsheet.

Edited August 18, 2014 by corgano

[water]

I'm sorry, but this code snippet can't be used to measure performance.

There are undefined variables (what is the value of $Colum), it does a lot of things in addition to read from Excel, you didn't specify the version of AutoIt you are running  etc.

I would prefer a reproducer script to compare read performance and - if needed - a second to compare write performance.

When I did the rewrite of the Excel UDF I noticed a performance enhancement by the factor of 20 or even 100 compared to the old UDF.

[corgano]

I'll try to gather more data / samples for you, and post back when I have more. Both computers are running the latest version of autoit as of time of posting (not beta though)

-Was going to be an edit, but forgot to post (derp)-

Ok so here is a stripped down version, that has basically the same amount kind of reads / writes, as well as a dummy template to test on. I don't know what is making it so slow, but even in this stripped out version my initial tests are taking a lot longer than they seem to have before.

expandcollapse popup

#include <Date.au3>
#include <excel.au3>

;open an exxcel to work in
Global $oExcel = _Excel_Open()

;Create a spreadsheet for the appropiate week from the template
$File = @ScriptDir&"\Deposit Template - Copy.xls"

;Try to attach to the
$oWorkbook = _Excel_BookAttach($File)
If $oWorkbook = 0 Then $oWorkbook = _Excel_BookOpen($oExcel, $File)
If @error Then
    _Excel_Close($oExcel)
    ConsoleWrite("Failed to open "&$File&"! "&@error&@CRLF)
    Exit
EndIf

$t3 = TimerInit()
For $iColum = 69 to 72
    $Colum = Chr($iColum)
    $t2 = TimerInit()
    For $i = 1 to 2
        $t = TimerInit()

        For $iRow = 7 to 28
            $Search = ""
            $Ret = Random(100,10000,1)/10

            ;read the name of the item we want to look up off the zed
            $Search = _Excel_RangeRead($oWorkbook, Default, "A"&$iRow)


            $Ret += _Excel_RangeRead($oWorkbook, Default, $Colum&$iRow)
            If $Ret <> "" Then _Excel_RangeWrite($oWorkbook, Default, $Ret, $Colum&$iRow)

        Next


        _Excel_BookSave($oWorkbook) ; Now we save it

    ;~  MsgBox(0, "Exiting", "Doubble check, and then press OK to Save File and Exit")
    ;~  _Excel_BookClose($oExcel) ; And finally we close out


    ConsoleWrite("  "&TimerDiff($t)&"   "&TimerDiff($t)/21&@CRLF)
    Next
    ConsoleWrite(TimerDiff($t2)&@CRLF)
Next

ConsoleWrite(@CRLF&TimerDiff($t3)&@CRLF) 

http://www.mediafire.com/view/lfnf0o429faalkj/Deposit_Template_-_Copy.xls

It took me a total of 45.589 seconds to run this test.

[corgano]

I did a bit more science, opened up two more spreadsheets of about the same size, and it then took 160 seconds. Is this kind of slowdown expected? I know it should slow down some amount, but this is a full multiple of a single excel running slower. Was the last version of excel.au3 affected to this extent?

Edited August 19, 2014 by corgano

[BrewManNH]

Download one of the older versions of AutoIt and try it for yourself, shouldn't take that long to find out whether or not the older version was faster or slower than the current version.

[water]

You are reading cell by cell by calling _Excel_RangeRead 88 times. Why not call the function one time by reading column 69 to 72 into an array and then process the array?

That's much faster.

[water]

I have created a Workbook with 44 rows and 4 columns.

Reading all data in one go takes 30 ms.

Reading cell by cell takes about 5100 ms.

Excel 2010, Windows 7 64 bit.

[corgano]

Upload your script and file, I'll try it with the new and old UDF tonight on my machine.

[water]

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $oAppl = _Excel_Open(False)
If @error Then Exit MsgBox(16, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $sWorkbook = @ScriptDir & "\Excel test.xlsx"
Global $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $sResult
Global $iTime = TimerInit()
For $i = 1 To 44
    $sResult = _Excel_RangeRead($oWorkbook, Default, "A" & $i)
    $sResult = _Excel_RangeRead($oWorkbook, Default, "B" & $i)
    $sResult = _Excel_RangeRead($oWorkbook, Default, "C" & $i)
    $sResult = _Excel_RangeRead($oWorkbook, Default, "D" & $i)
Next
ConsoleWrite(TimerDiff($iTime) & @CRLF)

_Excel_Close($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $oAppl = _Excel_Open(False)
If @error Then Exit MsgBox(16, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $sWorkbook = @ScriptDir & "\Excel test.xlsx"
Global $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $aResult
Global $iTime = TimerInit()
$aResult = _Excel_rangeRead($oWorkbook)
ConsoleWrite(TimerDiff($iTime) & @CRLF)
_ArrayDisplay($aResult)
_Excel_Close($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Excel test.xlsx

Edited August 20, 2014 by water

[corgano]

I'm sorry, I somewhat forgot about this thread, and I no longer work at the same place I did when I first posted. As such, I no longer have access to the windows xp computer with the older version of excell, so can no longer test this.

I will take your advisement to read the entire group of cells at once, and manipulate it in autoit when I write my next excel program (IT's really good advice), but it was not the question.

The still unanswered question was, that the same code running on a windows xp 32 bit machine, with an older office version was FASTER than running it on a windows 4 computer with %50 better processor and 2x the ram, and a newer version of excel.

If someone has access to older versions of excel, or access to a windows xp computer and a newer one running excel, I would appreciate it if they would do some follow up on this, because I remember the difference in time was significant. The variables in question are:

• Windows version - Maybe xp had a better OLE implementation? OR one that works better with autoit
• Bit version - I do not have access to any computers running a 32bit OS but I am curious if this effects it
• Office version - Perhaps the newer versions of office have less use for OLE and therefore priortized other features in development?

[water]

As you are the only one who reported such performance problems I tend to say it isn't related to the Excel UDF.
And I'm sure it is not related to OLE as - as far as I know - COM has nothing to do with OLE.