Jump to content
corgano

New Excel UDF faster on older computer?

Recommended Posts

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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
water

Can you please post your test script?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

;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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

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


0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


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

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


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


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
corgano

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


0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×