Is there a top Excel hardware available? Trillion loop macro...
Is there a top Excel hardware available? Trillion loop macro...
Beforehand, I've reviewed the information you provided.
I believe I need to invest in a dedicated processor to handle the situation.
Here’s my budget-friendly setup:
- Case Fan 120 mm Extra Quiet DC fan
- AMD RYZEN 9 7950X 16-Core 4.5GHz (5.7 GHz Turbo)
- ASRock X670E PG Lightning AM5
- 500GB SSD Samsung 870 EVO Series
- AMD Wraith Stealth Aluminum EXTRA QUIET fan, DDR5, USB 3.2, USB-C, 2.5GB LANHD Onboard 3D graphics Dual head, HDMI (for CPUs with integrated graphics), SATA3 6.0Gb/s, 560MBs 400W ATX Power Supply
- Microsoft Windows 11 Home 64-Bit Edition
Feel free to share any feedback.
Estimated purchase date: this week (the sooner the better)
Budget limit: under $1500 excluding shipping
Primary use case: only for the tasks mentioned above
Would you like to buy a monitor? No
Upgrade suggestions: none required, complete build planned
Do you need an operating system? Yes, I’ll be purchasing Windows 11
Preferred sources: any reliable online stores offering quality at a reasonable price
Location: NYC area (online purchases)
Preferred preferences: current suggestion above
Overclocking: not required
SLI or Crossfire: unclear
Monitor resolution: not applicable
Thank you.
You likely don't have to invest in X670E just for running a CPU. A solid B650 or B650E will suffice equally well. The X670E adds significant I/O and connectivity by linking two X670 chipsets directly to the system.
For storage, you'll need ample memory—ideally at least 2x32GB, possibly more for bigger projects. A powerful CPU air cooler is essential; liquid coolers aren't recommended for general use.
All in all, if you're working with Excel and moving it to a database, true SQL languages will handle it efficiently with much less power. Excel isn't built for such tasks.
This hardware is consumer-grade. For serious work, consider workstation-class systems with genuine ECC memory—though I recognize budget limits.
Thank you for the feedback. The process involves looping through a 40 by 6 array with permutations, all stored in RAM without significant disk access. For a near trillion operations, only about 50 summary results are generated, essentially a 6 by 6 report. It's unclear how a database application could achieve this speed compared to Excel. A C program would likely outperform Excel, but I require the flexibility that Excel provides.
The performance difference between a real database application and VBA in Excel can be significant based on design and functionality. Excel serves as a useful calculator but struggles with database tasks. If a database is essential, consider using a proper database solution instead of relying solely on Excel.
The task involves handling a list of 40 names with coordinates, stored in RAM. The aim is to identify the five closest names by evaluating all possible orderings and tracking distances. Comparisons are made between different sets of names, such as A,B,C,D versus A,B,C,D,F, recording each distance. The question explores whether performing these operations in a database environment would be more efficient.
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.
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.
If you decide to tackle it by force with a new PC build, here’s a recommended parts list that should work:
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)
Overall cost comes to $1321.59
Includes shipping, taxes, and any available discounts
Created by
PCPartPicker
2023-12-15 13:01 EST-0500
This board offers excellent VRMs for long-term stability. It provides a solid amount of RAM suitable for your needs, and the power supply is highly dependable. I also selected a case with good airflow but not overly large, ensuring compatibility with the EATX model.