Memory and Speed Improvements

[MarkBaker]

One of the most frequent criticisms raised against PhpSpreadsheet is the excessive memory required to maintain the "in-memory" model of a spreadsheet, particularly when working with larger volumes of data. While caching cell data is an option, the Cells Collection still maintains a simple map of whether cells exist in the cache or not just to provide a faster response for calls to the Cell Collection.
Over the last two months (since the 1.22.0 release), we've done a lot of work to reduce the memory footprint. Normally this entails a trade-off between memory and execution speed. Changes to the Cell Collection for the 1.23.0 release reduced memory usage, but at a cost in speed. Further work since then, both on the Cell Collection and the Writers, has reduced memory usage still further, but also improved execution speed again.

The following tables summarise the results of these changes between PhpSpreadsheet versions, and also across different versions of PHP for the different Writers.

Example speed and memory results creating and writing a 32 column by 16384 row spreadsheet:

Creating/Writing an Xlsx Spreadsheet

With PHP 7.4 against different versions of PhpSpreadsheet:

Version	Speed (s)	Peak Memory (KB)
1.22.0 Release	10.7626	419308
1.23.0 Release	12.6089	398828
1.24.0 Release	10.1844	214508

Comparison using the same spreadsheet between releases with different versions of PHP:

PHP Version	1.22.0 Speed (s)	1.22.0 Memory (KB)	1.23.0 Speed (s)	1.23.0 Memory (KB)	1.24.0 Speed (s)	1.24.0 Memory (KB)
PHP 7.3	10.6250	417260	12.6572	396780	10.3876	212460
PHP 7.4	10.7626	419308	12.6089	398828	10.1844	214508
PHP 8.0	10.4615	419308	12.3873	398828	10.2686	214508
PHP 8.1	10.1035	419308	12.3105	398828	9.8377	214508

Creating/Writing an Xls Spreadsheet

With PHP 7.4 against different versions of PhpSpreadsheet:

Version	Speed (s)	Peak Memory (KB)
1.22.0 Release	11.0121	409604
1.23.0 Release	12.8721	389124
1.24.0 Release	9.8618	213000

Comparison using the same spreadsheet between releases with different versions of PHP:

PHP Version	1.22.0 Speed (s)	1.22.0 Memory (KB)	1.23.0 Speed (s)	1.23.0 Memory (KB)	1.24.0 Speed (s)	1.24.0 Memory (KB)
PHP 7.3	11.3977	409604	12.8303	389124	9.7031	204220
PHP 7.4	11.0121	409604	12.8721	389124	9.8618	213000
PHP 8.0	10.8203	409604	12.9916	389124	9.7212	213000
PHP 8.1	11.1689	409604	12.0845	389124	9.6589	204220

Creating/Writing an Ods Spreadsheet

With PHP 7.4 against different versions of PhpSpreadsheet:

Version	Speed (s)	Peak Memory (KB)
1.22.0 Release	9.8929	325836
1.23.0 Release	11.0812	305356
1.24.0 Release	10.0778	309452

Comparison using the same spreadsheet between releases with different versions of PHP:

PHP Version	1.22.0 Speed (s)	1.22.0 Memory (KB)	1.23.0 Speed (s)	1.23.0 Memory (KB)	1.24.0 Speed (s)	1.24.0 Memory (KB)
PHP 7.3	9.5982	325836	11.3355	305356	10.7218	309452
PHP 7.4	9.8929	325836	11.0812	305356	10.0778	309452
PHP 8.0	9.4322	325836	10.8476	305356	10.0161	309452
PHP 8.1	9.1880	325836	10.7948	305356	9.8153	309452

Creating/Writing the same Spreadsheet as Html

With PHP 7.4 against different versions of PhpSpreadsheet:

Version	Speed (s)	Peak Memory (KB)
1.22.0 Release	12.3861	248724
1.23.0 Release	14.3680	228244
1.24.0 Release	11.6465	228244

Comparison using the same spreadsheet between releases with different versions of PHP:

PHP Version	1.22.0 Speed (s)	1.22.0 Memory (KB)	1.23.0 Speed (s)	1.23.0 Memory (KB)	1.24.0 Speed (s)	1.24.0 Memory (KB)
PHP 7.3	12.6676	248724	14.9252	228244	11.9375	228244
PHP 7.4	12.3861	248724	14.3680	228244	11.6465	228244
PHP 8.0	12.0857	248724	14.0001	228244	11.1204	228244
PHP 8.1	11.8968	248724	13.7544	228244	11.1555	228244

[luanmm]

Hello, @MarkBaker!

These results are great, but let me put another perspective regarding resource consumption here, which may address criticism related to specific scenarios.

TL;DR: scalability.

This library is too tied with a high-level mentality, which is natural considering that this is the PHP perspective afterall (no judging here, just trying to racionalize). Translating this into technicality, the expected result is that the library do its job fastest it can, because PHP, in general, runs in servers, and servers have "a good resource pool".

What I see as a trend, because of the evolution of the distributed systems, is that this logic is not anymore an absolute truth. Sometimes the trade-off could be between speed and resource use (be a little bit slower, but without consuming a lot of resources). This is specially the case when we think about horizontal scaling (when its better to have lots of low-resource replicas running instead of one high-resource server, making even more sense for web servers that receive lots of requests).

Thinking about this scenario, I can understand why memory is a complain. Even thinking about this great improvements shown above, we see that (if I got it right) the memory consumed to produce this spreadsheet (with relative low number of rows, considering Excel limits, for instance) was far superior than the default PHP memory limit (which is 128M). So, considering that distributed environments tends to work using the default resource limits, this spreadsheet couldn't be written in this perspective.

To summarize the idea: this is probably the best PHP library to work with spreadsheets, but its design/foundation haven't taken scalability into account and, for this scenarios, it is indeed a understandable complain. It is even something that I'm having trouble with, that forced me to search for alternatives.

I really don't know deeper this library (and others) or even the spreadsheet formats particularities to give any technical guidance (and don't even know if it should be my role here). I will just give some suggestions, if I may, pointing towards what could be a good future perspective to have an eye on both scenarios:

• The resource intensive strategy, if we may call that way, could target speed;
• A new approach could be designed to address low-resource environments, as in distributed infrastructure, considering techniques like output streaming, without keeping the entire spreadsheet in memory.

Hope you and the project's community don't receive this as a criticism or any kind of judgement (the work done here is really great). This is just a perspective on how we got here (afterall this is a community project that keeps growing for a decade yet, which is impressive and helps a lot of people), and pointing towards some perspective changes because of new studies, techs and stacks.

Best regards!