New Posts  All Forums:Forum Nav:

Excel Benchmark - Page 4

post #31 of 110
Have you seen these for Monte Carlo?
https://www.techspot.com/review/1345-amd-ryzen-7-1800x-1700x/page3.html
https://www.techspot.com/review/1497-intel-core-i7-8700k/
https://us.hardware.info/reviews/7602/11/intel-core-i7-8700k--i5-8600k--i5-8400-coffee-lake-review-affordable-six-cores-benchmarks-web-browsing-and-microsoft-office-word-and-excel-2016n
https://www.hardwareluxx.ru/index.php/artikel/hardware/prozessoren/43220-coffee-lake-intel-core-i7-8700k-i5-8600k-i5-8400-test.html?start=6
https://www.ocaholic.ch/modules/smartsection/item.php?itemid=3990&page=3
https://www.benchmark.rs/artikal/test_intel_coffee_lake_-_core_i7_8700k_i_core_i5_8600k-4439/9

"Big Number crunch"
https://pctuning.tyden.cz/hardware/procesory-pameti/48751?start=9

Black Scholes
https://www.overclockersclub.com/reviews/intel_core_i7_8700k__core_i5_8400/5.htm

Would be interesting to compare Excel 2010 and 2016. Excel 2010 has less bloat.

edit:
first run of the original sheet without timer on Sandy Bridge i5-2500K @ 4.6GHz is just under 3 min 20s ( < 200seconds) , 2x4GB DDR3 1600 CL9, with a ton of other stuff open & running Excel 2016 on Win 7 Pro

huzzug's test had 183.25seconds spit out on that machine when I closed Firefox and other stuff (I still had all the background apps such as antivirus).

There's something horribly wrong with this test's multi-threading if I can score nearly as high as any i7 newer than 2nd gen.

Running huzzug's test in Excel 2016 on a Ryzen 7 1700X set to stock clocks results in 30.28 seconds , 2nd run 28.97seconds. I'll have to retest it a few times , the CPU usage is really low.
2x8GB DDR4 3200 CL16 (Hynix with manual timings)
Excel 2016 , Windows 7 Pro



The original sheet on the same setup resulted in about 3 min 29 seconds (=209 seconds) , with the Process Monitor showing on average 6% CPU usage (so horribly single threaded , as 1/16 ~ 6% of CPU). A subsequent run on a different column resulted in 3 min 19 seconds (=199 seconds) so it may be sensitive to XFR kicking in faster.
Edited by AlphaC - 11/20/17 at 8:40am
post #32 of 110
Thread Starter 
Quote:
Originally Posted by huzzug View Post

Warning: Spoiler! (Click to show)
I made a little bigger change to the way the file benchmarks. I've changed how the final data is being sorted since that column is dependent on the data at the rear columns. The changes that I've made:

1. Truncated the data from the original 60,000 odd rows to just 1,000 rows where every cell is being calculated.
2. You need not close the file and restart to get correct score. The file now should be consistent whether you're benchmarking for the first time or 10th.

With just the above changes, the file takes ~3 times the time it took to bench the first time, while also pegging the main thread ~70-90%. Currently, the cells are only doing additions, divisions and getting averages across a small range of cells. I'd like to know any more suggestions that you guys like to see implemented.

P.S. I'm not an advanced user with VBA's, so I may not be able to accomplish everything that you may have in mind, but do let me know and I'll try to incorporate them.

Data1000.zip 1282k .zip file

I let the original file run on my work system the entire night with the changes that I made. My core 3 seems to be pegged at more than 70% and rest of the cores are pegged ~ 30.
[\SPOILER]

I am hesitant to replace the benchmark after people have already made runs and data collection has started, but I could include it on the top post as a better threaded option with a second spreadsheet when we are sure it is what we want.

I am doing a benchmark time by CPU speed (3.0 --> 4.8Ghz) graph now, and hope to have data up soon. Short answer is that CPU speed does affect sort time (at least on my machine), and clearly all cores are not being pegged to 100%. This is an Excel benchmark after all, and I was hoping that this benchmark would reflect real-world performance of Excel and allow users to make informed decisions about build specs.
post #33 of 110
Currently trying to research more options to spread load across multiple threads and better utilization. From what I've gathered these past few days is that excel cannot utilize more than one core / thread if you're using VBA (mine does use VBA) but general calculations within the sheets with formulas can run parallel (it's why you see ~25% utilization on my sheets on other cores).

My file to me seems to be a mix of both, but I'm still looking to incorporate lookups to tie in RAM with proc usage as well.

It will take time since I find 2 hours to spend in the evening on this, so I'll be a little slow with updates.

My next goal is to find what gives better utilization across threads as well as to get consistent loads on each core.
post #34 of 110
My observation :The original file , with simply clicking the column filter (column heading) and hitting "sort by smallest to largest" results in a horribly singlethreaded test. There's no way an i5 is faster than a i7-6950x.

ir88ed : I'm unsure of the purpose of sorting the columns , but if you need to sort multiple times you might consider using a Pivot Table or using MATLAB code to process the data in parallel and then spitting it back out into the Excel sheet as a new sheet altogether.
post #35 of 110
Quote:
Originally Posted by AlphaC View Post

My observation :The original file , with simply clicking the column filter (column heading) and hitting "sort by smallest to largest" results in a horribly singlethreaded test. There's no way an i5 is faster than a i7-6950x.

ir88ed : I'm unsure of the purpose of sorting the columns , but if you need to sort multiple times you might consider using a Pivot Table or using MATLAB code to process the data in parallel and then spitting it back out into the Excel sheet as a new sheet altogether.

I like this suggestion, but wouldn't that basically be benchmarking MATLAB and not Excel ?
post #36 of 110
My point is Excel installed normally (i.e. no custom install) is default 32 bit.
post #37 of 110
But, how would that affect benching excel ?
post #38 of 110
Quote:
Originally Posted by huzzug View Post

But, how would that affect benching excel ?

64 bit might be better tongue.gif

Especially for over 4 GB of virtual RAM...

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-performance-and-limit-improvements
Quote:
Large data sets and the 64-bit version of Excel

The 64-bit version of Excel 2010 is not constrained to 2 GB of RAM like the 32-bit version applications. Therefore, the 64-bit version of Excel 2010 enables users to create much larger workbooks. The 64-bit version of Windows enables a larger addressable memory capacity, and Excel is designed to take advantage of that capacity. For example, users are able to fill more of the grid with data than was possible in previous versions of Excel. As more RAM is added to the computer, Excel uses that additional memory, allows larger and larger workbooks, and scales with the amount of RAM available.

In addition, because the 64-bit version of Excel enables larger data sets, both the 32-bit and 64-bit versions of Excel 2010 introduce improvements to common large data set tasks such as entering and filling down data, sorting, filtering, and copying and pasting data. Memory usage is also optimized to be more efficient in both the 32-bit and 64-bit versions of Excel.
Quote:
Calculation improvements

Starting in Excel 2007, multithreaded calculation improved calculation performance.

Starting in Excel 2010, additional performance improvements were made to further increase calculation speed. Excel 2010 can call user-defined functions asynchronously. Calling functions asynchronously improves performance by allowing several calculations to run at the same time. When you run user-defined functions on a compute cluster, calling functions asynchronously enables several computers to be used to complete the calculations. For more information, see Asynchronous User-Defined Functions.


Multi-core processing

Excel 2010 made additional investments to take advantage of multi-core processors and increase performance for routine tasks. Starting in Excel 2010, the following features use multi-core processors: saving a file, opening a file, refreshing a PivotTable (for external data sources, except OLAP and SharePoint), sorting a cell table, sorting a PivotTable, and auto-sizing a column.

For operations that involve reading and loading or writing data, such as opening a file, saving a file, or refreshing data, splitting the operation into two processes increases performance speed. The first process gets the data, and the second process loads the data into the appropriate structure in memory or writes the data to a file. In this way, as soon as the first process begins reading a portion of data, the second process can immediately start loading or writing that data, while the first process continues to read the next portion of data. Previously, the first process had to finish reading all the data in a certain section before the second process could load that section of the data into memory or write the data to a file.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance
Quote:
Drill-down approach to finding obstructions

The drill-down approach starts by timing the calculation of the workbook, the calculation of each worksheet, and the blocks of formulas on slow-calculating sheets. Do each step in order and note the calculation times.
To find obstructions using the drill-down approach

Ensure that you have only one workbook open and no other tasks are running.

Set calculation to manual.

Make a backup copy of the workbook.

Open the workbook that contains the Calculation Timers macros, or add them to the workbook.

Check the used range by pressing Ctrl+End on each worksheet in turn.

This shows where the last used cell is. If this is beyond where you expect it to be, consider deleting the excess columns and rows and saving the workbook. For more information, see the "Minimizing the used range" section in Excel performance: Tips for optimizing performance obstructions.

Run the FullCalcTimer macro.

The time to calculate all the formulas in the workbook is usually the worst-case time.

Run the RecalcTimer macro.

A recalculation immediately after a full calculation usually gives you the best-case time.

Calculate workbook volatility as the ratio of recalculation time to full calculation time.

This measures the extent to which volatile formulas and the evaluation of the calculation chain are obstructions.

Activate each sheet and run the SheetTimer macro in turn.

Because you just recalculated the workbook, this gives you the recalculate time for each worksheet. This should enable you to determine which ones are the problem worksheets.

Run the RangeTimer macro on selected blocks of formulas.

For each problem worksheet, divide the columns or rows into a small number of blocks.

Select each block in turn, and then run the RangeTimer macro on the block.

If necessary, drill down further by subdividing each block into a smaller number of blocks.

Prioritize the obstructions.


Speeding up calculations and reducing obstructions

It is not the number of formulas or the size of a workbook that consumes the calculation time. It is the number of cell references and calculation operations, and the efficiency of the functions being used.


Because most worksheets are constructed by copying formulas that contain a mixture of absolute and relative references, they usually contain a large number of formulas that contain repeated or duplicated calculations and references.

Avoid complex mega-formulas and array formulas. In general, it is better to have more rows and columns and fewer complex calculations. This gives both the smart recalculation and the multithreaded calculation in Excel a better opportunity to optimize the calculations. It is also easier to understand and debug. The following are a few rules to help you speed up workbook calculations.

In particular
Quote:
Avoid single-threaded functions:

PHONETIC
CELL when either the "format" or "address" argument is used
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
ADDRESS where the fifth parameter (the sheet_name) is given
Any database function (DSUM, DAVERAGE, and so on) that refers to a pivot table
ERROR.TYPE
HYPERLINK
VBA and COM add-in user defined functions

Edited by AlphaC - 11/20/17 at 9:09am
post #39 of 110
Quote:
Originally Posted by AlphaC View Post

Quote:
Originally Posted by huzzug View Post

But, how would that affect benching excel ?

64 bit might be better tongue.gif

Especially for over 4 GB of virtual RAM...

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-performance-and-limit-improvements
Quote:
Large data sets and the 64-bit version of Excel

The 64-bit version of Excel 2010 is not constrained to 2 GB of RAM like the 32-bit version applications. Therefore, the 64-bit version of Excel 2010 enables users to create much larger workbooks. The 64-bit version of Windows enables a larger addressable memory capacity, and Excel is designed to take advantage of that capacity. For example, users are able to fill more of the grid with data than was possible in previous versions of Excel. As more RAM is added to the computer, Excel uses that additional memory, allows larger and larger workbooks, and scales with the amount of RAM available.

In addition, because the 64-bit version of Excel enables larger data sets, both the 32-bit and 64-bit versions of Excel 2010 introduce improvements to common large data set tasks such as entering and filling down data, sorting, filtering, and copying and pasting data. Memory usage is also optimized to be more efficient in both the 32-bit and 64-bit versions of Excel.
Quote:
Calculation improvements

Starting in Excel 2007, multithreaded calculation improved calculation performance.

Starting in Excel 2010, additional performance improvements were made to further increase calculation speed. Excel 2010 can call user-defined functions asynchronously. Calling functions asynchronously improves performance by allowing several calculations to run at the same time. When you run user-defined functions on a compute cluster, calling functions asynchronously enables several computers to be used to complete the calculations. For more information, see Asynchronous User-Defined Functions.


Multi-core processing

Excel 2010 made additional investments to take advantage of multi-core processors and increase performance for routine tasks. Starting in Excel 2010, the following features use multi-core processors: saving a file, opening a file, refreshing a PivotTable (for external data sources, except OLAP and SharePoint), sorting a cell table, sorting a PivotTable, and auto-sizing a column.

For operations that involve reading and loading or writing data, such as opening a file, saving a file, or refreshing data, splitting the operation into two processes increases performance speed. The first process gets the data, and the second process loads the data into the appropriate structure in memory or writes the data to a file. In this way, as soon as the first process begins reading a portion of data, the second process can immediately start loading or writing that data, while the first process continues to read the next portion of data. Previously, the first process had to finish reading all the data in a certain section before the second process could load that section of the data into memory or write the data to a file.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance
Quote:
Drill-down approach to finding obstructions

The drill-down approach starts by timing the calculation of the workbook, the calculation of each worksheet, and the blocks of formulas on slow-calculating sheets. Do each step in order and note the calculation times.
To find obstructions using the drill-down approach

Ensure that you have only one workbook open and no other tasks are running.

Set calculation to manual.

Make a backup copy of the workbook.

Open the workbook that contains the Calculation Timers macros, or add them to the workbook.

Check the used range by pressing Ctrl+End on each worksheet in turn.

This shows where the last used cell is. If this is beyond where you expect it to be, consider deleting the excess columns and rows and saving the workbook. For more information, see the "Minimizing the used range" section in Excel performance: Tips for optimizing performance obstructions.

Run the FullCalcTimer macro.

The time to calculate all the formulas in the workbook is usually the worst-case time.

Run the RecalcTimer macro.

A recalculation immediately after a full calculation usually gives you the best-case time.

Calculate workbook volatility as the ratio of recalculation time to full calculation time.

This measures the extent to which volatile formulas and the evaluation of the calculation chain are obstructions.

Activate each sheet and run the SheetTimer macro in turn.

Because you just recalculated the workbook, this gives you the recalculate time for each worksheet. This should enable you to determine which ones are the problem worksheets.

Run the RangeTimer macro on selected blocks of formulas.

For each problem worksheet, divide the columns or rows into a small number of blocks.

Select each block in turn, and then run the RangeTimer macro on the block.

If necessary, drill down further by subdividing each block into a smaller number of blocks.

Prioritize the obstructions.


Speeding up calculations and reducing obstructions

It is not the number of formulas or the size of a workbook that consumes the calculation time. It is the number of cell references and calculation operations, and the efficiency of the functions being used.


Because most worksheets are constructed by copying formulas that contain a mixture of absolute and relative references, they usually contain a large number of formulas that contain repeated or duplicated calculations and references.

Avoid complex mega-formulas and array formulas. In general, it is better to have more rows and columns and fewer complex calculations. This gives both the smart recalculation and the multithreaded calculation in Excel a better opportunity to optimize the calculations. It is also easier to understand and debug. The following are a few rules to help you speed up workbook calculations.

In particular
Quote:
Avoid single-threaded functions:

PHONETIC
CELL when either the "format" or "address" argument is used
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
ADDRESS where the fifth parameter (the sheet_name) is given
Any database function (DSUM, DAVERAGE, and so on) that refers to a pivot table
ERROR.TYPE
HYPERLINK
VBA and COM add-in user defined functions

I've seen the advantages to using 64bit, but VBS still work on one thread and seems to be 32bit. The general functions within excel are what can utilize more cores. My question is more to do with the current excel bench because I doubt you're excel is occupying more than 200MB of RAM even with 32bit.
post #40 of 110
Just for fun, I tried it with my FX-8320, but, it is using only one core in Excel 2013, resulting in a time of 218.68 seconds;

CPU: FX-8320
#CPU cores: 8
CPU clock speed: 4.5 GHz
Total system memory: 16GB
RAM speed: 1866 MHz
Excel ver: 2013
Time to sort (in seconds): 218.68 seconds
New Posts  All Forums:Forum Nav:
  Return Home