Excel 2010 - Fixing a Slow Solver, XP 64
I use Excel a lot at work, and I use Solver pretty extensively for some calculations that almost have to be solved iteratively. One of those spreadsheets has grown pretty big, to the point where in Excel 2002 (Excel XP), it was taking around 35 seconds to solve a particular scenario.
When Excel 2007 came out, I thought I'd give it a try, but solver took forever to run. So, I decided to hold off on upgrading.
When Excel 2010 came out, I downloaded the beta version of it to give it a try. Again, solver took forever. The scenario that took 35 seconds to run in 2002 took 8 minutes and 7 seconds to run in 2010 - 13.9 times longer.
Well, this time I did a little more digging, and saw an option that I suspected might be giving it a problem. Under Options -> Advanced -> Formulas -> Enable multi-threaded calculation, I unchecked that checkbox. I ran solver again. Lo and behold it was down to 46 seconds - still slower than in 2002, but at least now it was something I could live with.
I went back and checked on a colleague's computer with Excel 2007, and even though I didn't time it this time around, disabling the multi-threaded calculation made a huge difference.
So, to anyone who's having a problem with Solver being mind numbingly slow, this may help you out.
As another side note for Office 2010, Microsoft apparently decided that they didn't want to support it for XP 64. For the beta version, at least, this isn't a problem. Just run the compatibility wizard (Start -> Run -> hcp://system/compatctr/compatmode.htm), and set the installation program to run under 'Microsoft Windows XP'.
Added 2010-10-01 Well, I've been using the officially released version of Excel 2010, and Solver has been working okay. One more thing to add - after you disable multi-threading in the options, exit out of Excel and start it back up again for the option change to take effect.
Added 2010-10-26 I've been working on a project where I've been having to use Solver quite a bit, and it's more buggy than I originally thought. First of all, there's a second option that needs to be disabled. In Options, under the Advanced tab, in the General category, look for 'Enable multi-threaded processing' (right below the 'Web Options...' button). Uncheck that option.
With those two options unchecked, Excel will run fine for a while. However, it periodically re-enables the 'Enable multi-threaded processing' (not calculating) on its own. Worse, it sometimes seems to re-enable it without showing the check box as checked. So, whenever you're getting ready to start a project for the day, go back and double check that 'Enable multi-threaded processing' is disabled. If it shows to be disabled but Excel still seems to be running slow, enable it, OK out of the options, then go back in and disable it. This is a pain, and very irritating that the program messes with options that I've already set, but at least it's been working for me for the past few weeks.