Need to buy a high end EXCEL pc.

[shophoney]

Hi I need to purchase a new high end PC. I currently have a 2 year old XEON pc, with 32gb ram, 64bit, SSD.

I need more power from Excel. Cant figure out if i need more ram or a faster processor with more cores.

I use a 100mb file that OBDC to our server. But I only see about 20% cpu usage and 2-5gb of ram being used.

I've tried upgrading our server from 16gb to 32gb, that didnt help. That computer is only 12 month old.

Not sure where things slow down?

Should I buy a 64gb, 12 core Xeon cpu?

Should I move our files over to a true server rather than hosting them on a pc?

What are peoples thoughts?

thanks

 

[bellman101]

Hi there,

What takes the time? Opening the file? formulae calculating? inporting data?

Cheers
JB

 

[shophoney]

The importing from the other PC hosting the files. Also the time calculating the queries.

I use power query to import the data, I've created a OBDC connection. Then the data is imported to power pivot and measures are used to extract the results.

I hope that makes it more clear.

 

[bellman101]

I don't know a great deal about the relationship between resources and Excel performance, but I can say I've never had the pleasure of working with anything better than an 8Gb dual core when working with 100Mb+ files, and while they would of course take longer, it was never that painful, so I was wondering if it is in fact not hardware related. The fact your only using 10-20% of resources would go some way to back that up. Try running the file on a different machine, with say half the spec, and see how much difference there is in processing times. If it is minimal, then I wouldn't throw more cash at hardware, and maybe try to streamline the file itself?

 

[shophoney]

I've tried running it even local on the system where the files are hosted.

We have a retail management system. The data is in a SQL db and I pull 3 years worth of data into a pivot table. Thats why the file size is small. But the data is in the millions im sure.

So I can eliminate it being a network issue, a pc issue.

It's pulling in a lot of data, and i'm suing a power query to get the data.

Not sure of the bottle neck?

But how do i get excel to use more resources. I've got Excel 2016, 64 bit and a ton of ram.

 

[lrobbo314]

I've done some pretty intense work with Excel files. I think that there is some inherent limitations to what Excel can do. One project I ended up creating an EXE with vb.net, and the calculation speed was night and day. Also, you can program it to do multithreaded processes which would speed it up as well.

Having said that, I have a computer at home with an i7 7700k, 16gb of RAM, and a GTX 1060, and it really does make a big difference running on that PC as compared to the Dell Optiplex garbage they have at work. You can always go into the task manager and set the PC's priority and affinity for the excel process to a higher level, but I can't speak to how much that might help. I'm really interested to build a PC with AMD Ryzen to see how well it runs with that hardware.

 

[shophoney]

What program would you suggest using to convert it to an EXE. Would that speed it up?

I think my slow down can also be that im polling a 2gb sql database file. with 20 years of data. I'm only pulling out the last 2-3 years of data. But maybe having it import the data is slow.

thanks

 

[lrobbo314]

I used visual studio to program in vb.net to make the EXE. You're going to have know how to do some programming. But, the code language is visual basic and is very similar to the VBA that you can use in Excel. For the kind of things you're describing that the worksheet does, it should be pretty basic programming. Nothing that couldn't be figured out with some Googling.