Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


138 posts

Master Geek
+1 received by user: 7


Topic # 97109 10-Feb-2012 19:34
Send private message

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.

View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2 | 3
4128 posts

Uber Geek
+1 received by user: 753

Trusted
Subscriber

  Reply # 579931 10-Feb-2012 19:43
Send private message

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. 

1818 posts

Uber Geek
+1 received by user: 52

Trusted

  Reply # 579985 10-Feb-2012 21:52
Send private message

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

BDFL - Memuneh
61477 posts

Uber Geek
+1 received by user: 12203

Administrator
Trusted
Geekzone
Lifetime subscriber

  Reply # 579992 10-Feb-2012 22:04
Send private message

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.







138 posts

Master Geek
+1 received by user: 7


  Reply # 580191 11-Feb-2012 15:45
Send private message

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.

2445 posts

Uber Geek
+1 received by user: 146


  Reply # 580193 11-Feb-2012 16:04
Send private message


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


1491 posts

Uber Geek
+1 received by user: 191

Trusted

  Reply # 580209 11-Feb-2012 17:30
Send private message

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

34 posts

Geek
+1 received by user: 1


  Reply # 580212 11-Feb-2012 17:40
Send private message

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?

BDFL - Memuneh
61477 posts

Uber Geek
+1 received by user: 12203

Administrator
Trusted
Geekzone
Lifetime subscriber

  Reply # 580213 11-Feb-2012 17:43
Send private message
1598 posts

Uber Geek
Inactive user


  Reply # 580217 11-Feb-2012 17:52
Send private message

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



138 posts

Master Geek
+1 received by user: 7


  Reply # 580503 12-Feb-2012 15:13
Send private message

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

10251 posts

Uber Geek
+1 received by user: 1576


  Reply # 580520 12-Feb-2012 15:55
Send private message

Do you see much disk activity during the run?



138 posts

Master Geek
+1 received by user: 7


  Reply # 580531 12-Feb-2012 16:25
Send private message

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.

1491 posts

Uber Geek
+1 received by user: 191

Trusted

  Reply # 580591 12-Feb-2012 20:56
Send private message

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. 

36 posts

Geek


  Reply # 580642 13-Feb-2012 00:58
Send private message

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. 

14204 posts

Uber Geek
+1 received by user: 2567

Trusted
Subscriber

  Reply # 580661 13-Feb-2012 08:42
Send private message

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.




AWS Certified Solution Architect Professional, Sysop Administrator Associate, and Developer Associate
TOGAF certified enterprise architect
Professional photographer


 1 | 2 | 3
View this topic in a long page with up to 500 replies per page Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.