Update on Performance and Memory Usage

Topics: Developer Forum, Project Management Forum, User Forum
Coordinator
Nov 10, 2010 at 12:06 PM
Edited Dec 4, 2011 at 2:32 PM

I've been busy working on the performance of the Writers over the last few months, and have made some progress to improve overall performance of PHPExcel... both in speed, and in memory usage.
This posting is an update to show you the progress that is being made toward improved speed and memory usage for the next release.

The following tables show the results of a simple script that reads a workbook containing a single worksheet with 9 columns and 2048 rows, including different data types, some formatting, and a column of formulae; adds three new columns (2048 rows) of formulae, and a cell summing a set of column values; then writes the workbook. I've run each test 16 times against the different versions of PHPExcel, and averaged the results.

 

PHPExcel Version Instantiate Excel5 Reader Read Excel5 Workbook Update Workbook Instantiate Excel5 Writer Write Excel5 Workbook Peak Memory Usage
Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB)
1.7.0 0.0836 7.75 5.1496 28.25 1.2517 33.50 0.0936 35.25 9.8076 37.50 39.25
1.7.1a 0.0979 8.25 6.2076 28.75 1.7332 34.00 0.1091 35.75 9.8432 38.50 40.00
1.7.2 0.1156 8.50 6.4153 29.00 1.7902 34.25 0.1243 36.00 27.9357 42.50 42.75
1.7.3c 0.0587 1.75 8.0996 28.00 2.4337 33.50 0.0254 34.00 43.0947 53.25 56.75
1.7.4 0.0621 1.75 8.0863 29.75 2.4781 35.50 0.0291 36.00 42.8742 55.25 57.25
1.7.5 0.0576 2.00 7.1793 26.00 2.2869 31.50 0.0217 32.25 21.0155 38.50 40.50
Latest SVN Code 0.0577 2.00 7.2380 26.00 2.2917 31.75 0.0217 32.25 20.9440 38.25 40.50

 

PHPExcel Version Instantiate Excel2007 Reader Read Excel2007 Workbook Update Workbook Instantiate Excel2007 Writer Write Excel2007 Workbook Peak Memory Usage
Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB)
1.7.0 0.0329 7.00 5.2564 24.25 1.2669 29.25 0.0763 30.25 18.5855 36.25 39.25
1.7.1a 0.0342 7.75 6.3358 25.00 1.6807 30.00 0.0759 31.00 18.8721 36.25 39.25
1.7.2 0.0371 7.50 6.4564 25.00 1.6877 30.00 0.0822 31.25 25.7686 39.25 42.25
1.7.3c 0.0256 1.00 7.9802 24.75 2.2392 29.75 0.0708 30.75 27.5609 40.75 43.75
1.7.4 0.0349 1.00 7.5195 26.25 2.2546 31.75 0.0807 32.75 27.7737 43.00 45.50
1.7.5 0.0331 1.25 7.1705 22.50 2.2716 28.00 0.0719 29.00 16.8109 36.25 38.75
Latest SVN Code 0.0328 1.25 7.1032 22.50 2.2589 28.00 0.0702 29.00 16.5254 36.50 39.00

 

PHPExcel Version Instantiate CSV Reader Read CSV File to Workbook Update Workbook Instantiate CSV Writer Write CSV File from Workbook Peak Memory Usage
Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB) Time (s) Memory (MB)
1.7.0 0.0076 6.50 3.9077 23.50 1.3274 29.00 0.0047 29.00 12.9331 33.75 34.00
1.7.1a 0.0079 7.25 4.6437 23.75 1.6523 29.00 0.0048 29.00 13.2241 34.00 34.25
1.7.2 0.0075 7.50 4.6841 23.75 1.6195 29.00 0.0046 29.00 19.5209 36.75 37.00
1.7.3c 0.0076 0.25 5.7896 23.25 2.2077 28.50 0.0045 28.50 20.8464 36.50 37.00
1.7.4 0.0076 0.25 5.7505 24.75 2.1970 30.50 0.0045 30.50 17.8274 38.25 38.75
1.7.5 0.0079 0.50 5.5157 21.00 2.1837 26.75 0.0046 26.75 13.9719 32.00 32.25
Latest SVN Code 0.0080 0.50 5.5641 21.00 2.1893 26.75 0.0046 26.75 13.9203 32.00 32.25

 

PHPExcel Version Instantiate HTML Writer Write HTML Workbook Peak Memory Usage
Time (s) Memory (MB) Time (s) Memory (MB)
1.7.0 0.0173 29.50 19.4169 31.75 33.75
1.7.1a 0.0168 30.25 23.2540 33.50 35.25
1.7.2 0.0183 30.25 25.6768 35.75 38.00
1.7.3c 0.0156 30.00 31.6583 36.00 38.00
1.7.4 0.0148 32.00 32.4380 37.75 40.00
1.7.5 0.0137 28.25 22.7712 31.75 33.50
Latest SVN Code 0.0138 28.25 23.0657 31.75 33.50

While you're unlikely to see identical timings, because that's dependent on the server processing power and memory, you should still see a similar percentage improvement in Writer speed (and memory usage in the case of the Excel5 Writer). If anybody wants to do their own testing with the latest SVN code, it's available by selecting the "Source Code" tab here on the CodePlex PHPExcel site, then clicking on the download button in the panel on the right of the page in the box labelled "Latest Version" 

So far, my efforts have been focused on the Excel5, Excel2007, HTML and CSV Writers; and while these results show that progress has been made (particularly in speed of execution), I know that there is still more that can be done. I also need to turn my attention to the other Writers (PDF).

Oct 18, 2011 at 12:13 PM
Edited Oct 18, 2011 at 12:13 PM

PHPExcel 1.7.6
PHP 5.3.8 Zend Server
OS Centos 5 and Win 2003 srv

testing Excel2007 writer with 2000 rows and 30 cols and 5 sheets = memory usage 400MB on 32-bit.

cache_to_phpTemp
memoryCacheSize doesn't matter (with fixed bug #16212 myself). With 1M, 4M, 10M, 20M, 40M, 80M, 160M and 500M I got quite same time and memory usage.

cache_in_memory
cache_to_apc
cache_to_wincache
unuseful, quite same memory and time

cache_in_memory_serialized
better time 86,48%, better memory usage 63,17%

cache_to_discISAM
slower time 124,62%, better memory 54,24%

cache_to_phpTemp
slower 121,42%, better memory 54,18%

cache_in_memory_gzip
slower 140,89% - 213,58%, better memory 51,54%

Dec 1, 2011 at 4:49 PM

Thank you for these stats. cache_in_memory_serialized just fixed my script for a huge upload. :)

Coordinator
Dec 1, 2011 at 7:52 PM
Edited Dec 5, 2011 at 10:14 PM

I'm just adding a cache_to_sqlite option as well (having finally cracked a problem with storing serialized objects in SQLite)... because it doesn't need to retain an index in memory (unlike all the other methods) initial tests show that it's the lowest memory usage of all caching mechanisms when using an in-memory database, with speed somewhere between discISAM and phpTemp. Once I've finished it and posted it to SVN, I'll run some tests using all the different caching mechanisms and post the results here.

 

Some initial tests against different cache methods. reading an Excel 95 file (2 worksheets - A1:AP745 and A1:EC32 respectively, with some formatting and calculations) and writing it out as Excel 2007).

 

Cache Method Read .xls File Write .xlsx File
Time (s) Memory (MB) Time (s) Memory (MB) Peak Memory (MB)
Memory 6.0924 40.75 3.2061 43.50 47.25
Memory Serialized 6.6924 30.50 5.0707 26.00 34.00
Memory GZip 10.2580 16.00 9.2748 21.25 22.75
DiscISAM 7.8962 18.00 7.6800 20.50 24.50
PHPTemp 7.7899 18.00 7.6874 20.50 24.50
SQLite 9.1266 7.00 12.8834 10.00 13.50
SQLite3 10.1607 7.00 15.2189 9.75 13.50
Igbinary 7.3877 19.00 5.0570 21.25 25.25
APC 8.4138 10.00 9.8676 12.50 16.25

 

These initial tests were averaged over 50 iterations, but weren't run in consistent test conditions... but they should give some idea of the speed and memory differences between different caching mechanisms.

 

Guess I also need to update the table in the first post of this thread with 1.7.6 and latest SVN code figures.

Dec 2, 2011 at 8:07 AM

Sounds very interesting indeed. Nice work :)