Re: Win32::OLE Excel temporary objects destruction

[sundialsvc4]

OLE can be extremely expensive.   Avoid anything which has the possibility of creating more objects on the fly.   For instance, in this piece of code:

$Book->Worksheets($sheetNumber) ...

...put that object into a local variable and remember what sheet-number it uses.   Re-create the object only if the sheet-number changes, and arrange things so that the logic does not bounce between sheets.   Similar strategies should be applied at every reasonable opportunity.

I am pondering why this code makes exactly twice-as-many calls to AUTOLOAD as it does to DESTROY.   This almost implies to me that, somehow, OLE objects might be accumulating.   But you have not posted enough code to indicate if this is a problem, or a bug.

You neglect to say how much time “a huge amount of time” actually is, nor a hardware comparison (particularly, memory ...) between yours and your colleague’s machine.   Run a process-monitor on both machines as it runs on each one, and try to see what’s slowing it down, from that monitor’s high-level perspective.

OLE drives the application much like an interactive user session would.   Anything that an interactive user might choose to do, in order to save time, applies to you also.   For instance, when loading data into an Excel sheet, you should suspend re-calculation until all of the data has been loaded.   OLE is fundamentally an application-level API, and everything that you do has the potential for time-consuming side effects which your application will be forced to wait for.   (Per contra, the Perl-side object-twiddling actually takes negligible time by comparison.)   It is these side-effects that will bite you in the asterisk.   Depending on the implementation of the application that you are controlling, seemingly-minor changes can have dramatic effect ... for good, or for ill.