I discovered that the hardware upgrade in itself was not enough to cure our ills. After coming up against some disappointing results, despite the bump to Windows 7 and Excel 2010, I was obligated to re-write a big chunk of the data handling logic; something I was hoping to avoid.
My initial research led me to believe that 64 bit Excel has access to as much ram as you can slot into your machine, but this is not my experience. The spreadsheet part of Excel might be bigger, faster, stronger and fresher smelling, but the VBA object space is not.
Upgrading my VBA code to work in Excel 2010 was actually a non-event. I only found one line, (related to checking the amount of ram available, ironically) that choked. Otherwise the macros and formulas all behaved exactly as designed. Excel 2010 saves files in the Office 2007 file format, so that was my first clue that this isn't really that much of an Excel upgrade.
Then, after seeing Excel freeze, just like before, when my code tried to pre-load six million+ data points into the application's memory structures, I was beginning to think the whole ordeal and expense may have been for nothing. Further reading on-line found some comments from a Microsoft engineer who wrote that they achieved significant performance gains on Excel 2010's built-in functions, but basically didn't do a thing to optimize or beef up the VBA compiler... that will be in the next release, supposedly.
Perhaps the recent push towards Cloud-based applications is drawing some of the programming talent away from the Excel group at Microsoft. I'm a developer, so I can't really hold a grudge. Excel is still a miracle of engineering in my opinion, and the best software ever to run on any computer anywhere. (Yes, I like it.)
Anyway, I had no choice but to re-work the data logic so the application fetches what it needs at report time, instead of pre-loading on start-up. I made this change, and the app became usable again - even improved, from the client's perspective, because it "starts up a lot faster." Reports now require additional processing, but the fast new chip and fast ram more than make up for it. Also, thanks to the solid state hdd, (and possibly Widows7's superior design,) the memory swapping issue has totally gone away.
We didn't get the 800% performance boost that Moore's Law predicted, but our app's data ceiling is now a clear blue sky, and one busy brokerage is crunching numbers with it on a daily basis. The client is armed with an unbelievably powerful stock analysis tool, and now I know what to expect (and what expectations to instil) the next time a client wants to level up.
Posts
