Question Excel Hardware GOAT out there? Trillion loop macro...

[Hanzle]

Thanks beforehand.

I've followed desired info format below this summary description.

Have a optimization Excel VBA program that loops nearly a trillion times.

Come to the conclusion I've got to buy a dedicated processor to run.

Here's my max <$1500 plan -

• Case Fan 120 mm Extra Quiet DC fan
• AMD RYZEN 9 7950X 16-Core 4.5GHz (5.7 GHz Turbo) -
• ASRock X670E PG Lightning AM5List item
• 500GB SSD Samsung 870 EVO Series Solid State Drive

AMD Wraith Stealth Aluminum EXTRA QUIET fan, DDR5, USB 3.2, USB-C, 2.5GB LANHD Onboard 3D graphics Dual head, HDMI (only for CPUs with integrated graphics), SATA3 6.0Gb/s, 560MBs 400W ATX Power Supply, Microsoft Windows 11 Home 64-Bit Edition


Thoughts? Any comments are welcome

Approximate Purchase Date: this week (the closer the better)

Budget Range: Under $1500 excl shipping

System Usage from Most to Least Important: Machine will be used solely for aforementioned task

Are you buying a monitor: No

Parts to Upgrade: N/A, complete build

Do you need to buy OS: Yes, will be buying Win 11

Preferred Website(s) for Build: Any bare bones online that delivers quality and price

Location: NYC Area (buying online)

Parts Preferences: Current suggestion above

Overclocking: No

SLI or Crossfire: Huh?

Your Monitor Resolution: N/A

Additional Comments: THANKS

 

[Eximo]

You probably don't need to spend money on X670E just to run a CPU. A decent B650 or B650E will get the job done just as well. X670E just gains you a lot of I/O and other connectivity by literally connecting two X670 chipsets up to the system.

You'll need memory. For large things, you probably want to go down the route of at least 2x32GB sticks, if not more.

And you will want a large CPU air cooler. Liquid coolers are not wise for business applications.

All that said, I would take whatever you are doing in Excel and move it into a real database. True SQL languages can get the job done with a fraction of the compute power. Excel is simply not designed to handle such things.

This is also all consumer grade hardware. For any real work I would suggest looking into workstation class hardware with true ECC memory. Though I understand the budget constraints.

 

[Hanzle]

Thanks for the response. It's a 40 by 6 array that's being looped (permutations) and all that is loaded in RAM. Very little, if any disk I/O takes place. For a near trillion run there are only about 50 result summaries output which is just a 6 by 6 report. Unsure how a database app can do that faster then Excel? A C program would beat Excel. But I need the flexibility of Excel.

 

[USAFRet]

Thanks for the response. It's a 40 by 6 array that's being treated and all that is loaded in RAM. Very little I/O takes place. For a near trillion run there are only about 50 result summaries output which is just a 6 by 6 report. Unsure how a database app can do that faster then Excel? A C program would beat Excel. But I need the flexibility of Excel.

Depending on how this is built and what it does, a real database application (stored procedures) can absolutely run a LOT faster than VBA within Excel.

Excel is a great calculator...it makes a crappy database.

If Excel is absolutely needed, then that is what you use.
But do be sure that Excel is actually the best tool for this.

 

[Hanzle]

Thanks for the responses. Perhaps a better description may help. Imagine a list of 40 names in a phone book (yeah I am old). Along with the names there is a vertical and horizontal coordinate. All of this data is loaded in a RAM resident array. The goal is to find the five closest names, by driving thru all permutations. A,B,C,D,E compared to A,B,C,D,F. and distance recorded. A,B,C,D,(best) then compared to A,B,C,D,G. Etc. This being all done in RAM will occur faster in a Database program? Hmm.

 

[USAFRet]

Thanks for the responses. Perhaps a better description may help. Imagine a list of 40 names in a phone book (yeah I am old). Along with the names there is a vertical and horizontal coordinate. All of this data is loaded in a RAM resident array. The goal is to find the five closest names, by driving thru all permutations. A,B,C,D,E compared to A,B,C,D,F. and distance recorded. A,B,C,D,(best) then compared to A,B,C,D,G. Etc. This being all done in RAM will occur faster in a Database program? Hmm.

This is pretty much exactly what Indexing and stored procedures do in a db application.

This sounds like a GPS/nav application.
What do the actual Nav applications use? Garmin, TomTom, Google maps....
hint...not Excel...😉

 

[Hanzle]

I still think the problem is being misunderstood. I apologize for poor communication. The challenge is not handling a mega amount of data. GPS/Garmin challenge is. Specifically VH coordinates for the world and additional data. My dataset is not large. There is no indexing. Exactly it is a 40*3 array! That's only 120 data fields. The challenge with my application is comparing the small dataset to itself, and culling a optimal solution. Thus it's a 40*39*38*37*36*35 permutation problem. From a near trillion run approx 100 small reports are generated. A report is generated when the looped comparison exceeds the optimal solution to that point. The time to i/o a report compared to the near trillion loop that occurs is minuscule. This is all done in RAM. No memory swapping from RAM to secondary storage takes place. Having witnessed performance gain when comparing my desktop performance to laptop performance (I9 vs Ryzen) there is notable difference of same program. Thus I'd like to get a hardware expert, with Excel experience, to chime in Thanks beforehand.

 

[Eximo]

I use Excel extensively for work. But I do all my heavy lifting in SQL databases running on VMs hosted on somewhat aged hardware compared to the 11th gen i7 I have in my laptop.

Array functions in SQL are a bit unique to each brand, but you could spend a few bucks on a cloud database server and give it a try in an afternoon.

Or simply install a local copy of MS SQL or My SQL and try it on your existing hardware. Time both, see how it goes.

If you want more expert advice I suggest StackOverflow. As long as you can be detailed enough with what your code is actually trying to accomplish, or better yet, provide examples.

In terms of hardware advice, this is an interesting question. Not sure if AVX instructions or AMX instructions would benefit you, probably. In which case AMD still offers AVX-512 on their consumer chips, Intel does not. But I think AMX is showing up in the latest Xeons, which is specific hardware for handling matrix calculations. That might take it from minutes to seconds.

I can't fault the simple brute force approach of getting a high core count CPU, unless there is a more efficient way to do the work. That would take some research.

 

[helper800]

If you are still going to brute force it with a new PC build, here is a list that will get the job done:

PCPartPicker Part List

CPU: AMD Ryzen 9 7950X 4.5 GHz 16-Core Processor ($531.74 @ Amazon)
CPU Cooler: be quiet! Dark Rock Elite CPU Cooler ($99.90 @ Newegg Sellers)
Motherboard: ASRock B650E Taichi Lite EATX AM5 Motherboard ($254.99 @ Newegg)
Memory: G.Skill Ripjaws S5 64 GB (2 x 32 GB) DDR5-5600 CL28 Memory ($189.99 @ Amazon)
Storage: Western Digital Blue SN580 1 TB M.2-2280 PCIe 4.0 X4 NVME Solid State Drive ($59.99 @ Amazon)
Case: Corsair 3000D AIRFLOW ATX Mid Tower Case ($84.99 @ Amazon)
Power Supply: SeaSonic FOCUS Plus 750 Gold 750 W 80+ Gold Certified Fully Modular ATX Power Supply ($99.99 @ Amazon)
Total: $1321.59
Prices include shipping, taxes, and discounts when available
Generated by PCPartPicker 2023-12-15 13:01 EST-0500

That motherboard has really good VRMs for sustained load over long durations. A decent chunk of RAM for your purposes, and a super reliable PSU. I also tried to pick a good case with decent airflow that wasn't too big in size, but compatible with the EATX board.