SLOW Performance

Topics: Developer Forum
Feb 18, 2011 at 3:08 PM
Edited Feb 18, 2011 at 3:14 PM

I have a scripting writing a spread sheet with 13,200 rows and 30-40 columns.  Performance is TERRIBLE.  It takes well over an hour to write the spreadsheet.  I am using the GZip caching.

What gives?  Are there any tips or steps I can take to improve performance?  I found some posts about helping performance with reads, and maybe I am missing them, but I haven't seen much about improving performance with writes. 

Running PHP 5.3.3 on a Power6 processor (which tends to makes short order of most of these PHP Scripts FYI).

Coordinator
Feb 18, 2011 at 3:39 PM

There are plenty of things that can be done to improve performance, but is it purely the writer that is taking time, or is it population of the PHPExcel object?

If it's the writing, and your workbook has a lot of formulae, you can disable precalculation by using:

$objWriter->setPreCalculateFormulas(false);

More information might help, such as the format that you're writing; or whether you're styling each cell individually, or using row/column styling; how many autofit columns do your have? etc.

Feb 18, 2011 at 3:46 PM
Edited Feb 18, 2011 at 4:36 PM

I am using a lot of formulas so  I added the setPreCalculateFormulas(false) just now and will run another test.  I am also having memory issues still as my current test just failed with this:

PHP Fatal error:  Out of memory (allocated 226230272) (tried to allocate 15630559 bytes) in /phpscripts/cli/includes/PHPExcel/Shared/XMLWriter.php on line 100

 

As for the other things, I am not using autofit, I am applying styles by range, and my formats are mostly currency with custom format codes.  

EDIT:
Also, guess I need to figure out if the writer or the building of the object is my hold up, but my initial tests lead me to believe that populating the PHPExcel object is going very slowly - so it could be a little of both

EDIT 2:

OK, after digging a little deeper the issue is the DEFINITELY writing.  It populates the object in an acceptable amount of time.

Feb 18, 2011 at 4:58 PM

Disregard the memory error above, after digging around some more I stumbled upon code I added this morning that caused that issue.

Setting preCalculateFormulas to false seems to have helped dramatically.   The report is now running in about 15-20 minute, which is much more reasonable.  Populating the object takes only a minute or two, so if you can think of anything else that might speed up the writer even more, let me know.

Thanks for the help!