calculate without cache - too slow

Topics: User Forum
Mar 7, 2010 at 11:59 AM

I've got an issue with this:

I am using this command:

$objCalc = PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(false);

but it take too long to finish the script since I presume there is no cache. I put in htaccess:
php_value max_execution_time 200

And still the script does not finish..

Now using the
$objCalc = PHPExcel_Calculation::getInstance();

Produces wrong results.. Any suggestions?
This is about this thread: http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=178242

Thanks


Coordinator
Mar 7, 2010 at 7:34 PM

The calculation cache stores the results of previous calculations within the PHPExcel_Calculation instance. This allows better execution when you have calculated cells that are themselves dependent on the results of other calculated cells (e.g. A1 = B1+C1; B1 = B2+B3 and C1 = C2 / B1). With calculation caching disabled, and requesting the result for cell A1, B1 will be calculated twice (even though the result should be the same each time): with it enabled it will only be calculated the once. The cache has a default timeout that is set at 2.5 seconds, and can be changed using PHPExcel_Calculation::getInstance()->setCalculationCacheExpirationTime(). Normally, you should leave the calculation cache enabled to improve performance when calculating formulae that make nested calls, although if your script is long running, you may want to change the calculation cache expiration time.

If you want to calculate results, then change the underlying data and recalculate the results, then the PHPExcel_Calculation::getInstance()->clearCalculationCache() method should be used to clear any calculation results from the initial calculation before the second series of calls to calculate the formulae.

 

I'm not sure what you're trying to accomplish with the call to $objCalc = PHPExcel_Calculation::getInstance(); The Calculation engine implements the singleton class, so you're simply retrieving that singleton instance.

 

One thought is that you might have cyclic references in your formulae (e.g A1 = B1+C1 and B1 = A1+C1), something that the PHPExcel calculation engine cannot handle cleanly and that does lead to timeouts.

Mar 8, 2010 at 2:57 AM

Well I really didn't find any documentation explaining how to work with phpexcel, apart from the examples.. So I'm just copying the lines that look relevant.

The $objCalc = PHPExcel_Calculation::getInstance() call from my understanding make the different calculations work so I'm using it, and the 

$objCalc = PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(false); disables the cache (I found it in the forum)..

So basically I am just looking into what others did and using it in my project. I there documentation other then the list of classes
included in the distribution?

Anyway I'll take another look into calculations and get back to you on that.

Thanks
Coordinator
Mar 8, 2010 at 9:02 AM

The file "PHPExcel developer documentation.doc" in the /Documentation directory of the PHPExcel distribution is the main document, which is pretty comprehensive; together with "PHPExcel Function Reference developer documentation.doc" (not as up-to-date as it should be). Then there's the API documentation in /Documentation/API

Jan 5, 2011 at 2:42 PM

I've tried using PHPExcel_Calculation::getInstance()->disableCalculationCache(), and then PHPExcel_Calculation::getInstance()->clearCalculationCache(). I've realized that the first take longer to complete that the second (over 8 minutes).

So, I think it's better to use clearCalculationCache, each time you want to get a calculated value.

Coordinator
Jan 5, 2011 at 11:24 PM
Edited Jan 5, 2011 at 11:26 PM

The calculation cache is worth using if you have calculated cells that are dependent on the value of other calculated cells. How much it will benefit depends on the network of calculated cells. It is turned on by default.

If you do leave calculation cache enabled, then you only need to clear the cache if you get a calculated cell value, change the values of the cells that affect that calculation, and then want to recalculate... if you don't clear the cache, then you'll get the same result (unless the cache has timed out).

The latest version of PHPExcel does handle cyclic references in calculations.