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%
|
|
|
|
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.
|
|
|
|
Sounds very interesting indeed. Nice work :)
|
|