New number crunching PC

Forums › Desktop computing › New number crunching PC

deepred

485 posts

Ultimate Geek

ID Verified

Trusted

#97109 10-Feb-2012 19:34

I'm pricing a quote for a new PC to be used for some rather insane number crunching. The client basically runs a 16MB Excel 2010 file with a calculation macro, and his current machine takes 19 hours to process it on an unspecified quad-core PC.

The only catch is, the client wants to be doubly sure that the new PC can do the same task within 12 hours. I've already tested the file he provided on an i5-2500K machine with 2x 4GB DDR3-1600 RAM, and according to my own calculations, it would have taken 37 hours to finish. The benchmark charts I've seen so far (Tom's Hardware, Anandtech et al) are a bit vague. It's definitely not thermal under-clocking, as the CPU was still within safe temp. And I've enabled XMP clocking in the BIOS to get the full dual-channel 1600MHz.

So far, the new PC has been quoted with an i7-3960X with 4x 4GB DDR3-2133 RAM and an overclockable X79 board. To date it's the fastest possible desktop config available, short of going for a dual-processor Xeon server board.

"I regret to say that we of the F.B.I. are powerless to act in cases of oral-genital intimacy, unless it has in some way obstructed interstate commerce." — J. Edgar Hoover

"Create a society that values material things above all else. Strip it of industry. Raise taxes for the poor and reduce them for the rich and for corporations. Prop up failed financial institutions with public money. Ask for more tax, while vastly reducing public services. Put adverts everywhere, regardless of people's ability to afford the things they advertise. Allow the cost of food and housing to eclipse people's ability to pay for them. Light blue touch paper." — Andrew Maxwell

1 | 2 | 3

6687 posts

Uber Geek

Trusted

Subscriber

#579931 10-Feb-2012 19:43

Do you have an understanding of exactly what the macro is doing?

I have a macro that takes about 30 minutes to run but I'm pretty sure that the slowness is due to inefficiencies in the way that Excel updates links from external workbooks. If I manually perform the actions that the macro would otherwise perform except for updating external links then it takes a fraction of the time that the macro would take.

So, you need to be a bit careful about assuming that the time taken by the macro is inversely proportional to the processing power of the PC. Ideally it sounds like your client's entire process needs to be re-engineered so that the heavy lifting can be done by an SQL server, but I know that's easier said that done in many cases like this.

hellonearthisman

1819 posts

Uber Geek

Trusted

#579985 10-Feb-2012 21:52

Have you looked at using a GFX card to crunch those numbers. http://developer.nvidia.com/what-cuda

freitasm

BDFL - Memuneh
79147 posts

Uber Geek

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#579992 10-Feb-2012 22:04

What Alasta said. This sounds like something that shouldn't be done on Excel. Perhaps loading this data (rows) into a database table and use a decent BI tool such as SQL Analysis Services run it through.

deepred

485 posts

Ultimate Geek

ID Verified

Trusted

#580191 11-Feb-2012 15:45

Ideally he'd use something other than Excel, but that's probably going to require re-engineering it from scratch. So we're having to make the best of a bad lot.

Turns out his existing PC uses a Xeon Quad with 32-bit Windows (not sure if it's XP or Vista or 7, but I suspect XP). Looks like we're in for a dual-CPU server board.

kyhwana2

2563 posts

Uber Geek

#580193 11-Feb-2012 16:04

deepred: Ideally he'd use something other than Excel, but that's probably going to require re-engineering it from scratch. So we're having to make the best of a bad lot.

Turns out his existing PC uses a Xeon Quad with 32-bit Windows (not sure if it's XP or Vista or 7, but I suspect XP). Looks like we're in for a dual-CPU server board.

Make sure excel is using multi-threading first! No point in forking out for 8+ cores if all it's doing is maxing out a single core..

tchart

2369 posts

Uber Geek

ID Verified

Trusted

#580209 11-Feb-2012 17:30

kyhwana2:
deepred: Ideally he'd use something other than Excel, but that's probably going to require re-engineering it from scratch. So we're having to make the best of a bad lot.

Turns out his existing PC uses a Xeon Quad with 32-bit Windows (not sure if it's XP or Vista or 7, but I suspect XP). Looks like we're in for a dual-CPU server board.

Make sure excel is using multi-threading first! No point in forking out for 8+ cores if all it's doing is maxing out a single core..

+1 Im pretty sure Excel is single threaded. So no point in quad, octo core (LOL is that a word?). We have a similar issue with software we use for geographic processing. Multi-core would simply allow you do other stuff while your number crunching runs.

Now if you happened to have 4 Excel instances running (not 4 sheets but 4 copie sof Excel open), thats another story and thats how we deal with the single threaded problem...

nzjwnz

40 posts

Geek

#580212 11-Feb-2012 17:40

tchart:
+1 Im pretty sure Excel is single threaded. So no point in quad, octo core (LOL is that a word?). We have a similar issue with software we use for geographic processing. Multi-core would simply allow you do other stuff while your number crunching runs.

Now if you happened to have 4 Excel instances running (not 4 sheets but 4 copie sof Excel open), thats another story and thats how we deal with the single threaded problem...

I thought they brought in some changes to take advantage of multi-core processors in Excel 2010?

Backblaze

Backblaze Unlimited Backup. World’s easiest cloud backup. Get peace of mind knowing your files are backed up securely in the cloud (affiliate link).

freitasm

BDFL - Memuneh
79147 posts

Uber Geek

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#580213 11-Feb-2012 17:43

Excel 2007 onwards supports multithread.

Also note that you must be using Excel 64 bit to use more than 2GB for spreadsheets.

Here is an article on improving Excel performance

codyc1515

1598 posts

Uber Geek
Inactive user

#580217 11-Feb-2012 17:52

Maybe look into setting up a high-performance instance on Amazon AWS EC2, on occasion, if you're that way inclined. ;)

deepred

485 posts

Ultimate Geek

ID Verified

Trusted

#580503 12-Feb-2012 15:13

It also turns out I was running the 32-bit version of Excel 2010, so that would have skewed the results. I might try redoing the test with 64-bit Excel. And for the most part, RAM usage isn't an issue, but more the RAM's clock rating - Task Manager only peaked at about 2GB RAM usage.

gzt

17001 posts

Uber Geek

Lifetime subscriber

#580520 12-Feb-2012 15:55

Do you see much disk activity during the run?

deepred

485 posts

Ultimate Geek

ID Verified

Trusted

#580531 12-Feb-2012 16:25

A fair bit, but not intensely so. The HDD is a 7200rpm SATA 3Gbs one, and I think the new PC should have an SSD.

tchart

2369 posts

Uber Geek

ID Verified

Trusted

#580591 12-Feb-2012 20:56

freitasm: Excel 2007 onwards supports multithread.

Also note that you must be using Excel 64 bit to use more than 2GB for spreadsheets.

Here is an article on improving Excel performance

Good to know but take with a "pinch of salt" me thinks!

According to this http://msdn.microsoft.com/en-us/library/ff700514.aspx#MultithreadedCalculation its only on asynchronous calculations. I'm guessing if this "number crunch" was built a while back (pre-Excel 2007) then its probably a linear number crunch which would not take advantage of multi-threading. I would say the calculation probably needs to be reworked.

I would expect the increase in RAM allocation would help a bit.

yorick

36 posts

Geek

#580642 13-Feb-2012 00:58

tchart: I'm guessing if this "number crunch" was built a while back (pre-Excel 2007) then its probably a linear number crunch which would not take advantage of multi-threading. I would say the calculation probably needs to be reworked.

This is in fact a common problem. Spreadsheets created in legacy software carries a lot of baggage that can only be solved by complete reworking on a regular basis. A 16gb spreadsheet is a disaster waiting to happen. A reasonable risk management policy would require moving that into a database if only to get robust error management. The European Spreadsheet Risk Interest group (EuSPRIG) documents a raft of high cost spreadsheet error horror stories. that should give anyone pause. No matter the perceived difficulties, a move to a high end number crunching DB such as PostGRES should be a matter of priority.

timmmay

20507 posts

Uber Geek

Trusted

Lifetime subscriber

#580661 13-Feb-2012 08:42

19 hours is pretty insane. Rather than a new PC how about either:
- Having someone with a lot of macro/performance experience look at what it's doing. If it's badly written that could reduce the run time by a huge amount
- Re-engineering the solution is a much better idea

Software developers are expensive though.

1 | 2 | 3