Sqlite and PHP memory

Topics: Developer Forum
Apr 12, 2013 at 4:55 PM
Edited Apr 12, 2013 at 4:56 PM
Im using the Sqlite cache option like this :
// use SQLite3 for caching
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3;
$cacheEnabled = PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
if (!$cacheEnabled)
    echo "### WARNING - Sqlite3 not enabled ###" . PHP_EOL;
$objPHPExcel = new PHPExcel();
But when i create a simple (as in no styling or formula's - just plain text - about 20 columns) 10000 line spreadsheet the PHP memory still rises - it seems to me that the :memory: database in Sqlite3 just consumes the PHP process memory - and eventually causes an error (PHP runs out of memory) - Im writing some pretty large tables - Im having to split them up to get them to ouput.

I have tried modifying the PHPExcel Sqlite3 class to use a file for the database - and that works - PHP memory rises to about 12/15mb and the database file grows - this works fine but takes a while ....

Do you have any suggestions on how to avoid using the PHP process memory ? perhaps the database filename could be a setting that you pass to the setCacheStorageMethod function
Apr 12, 2013 at 5:17 PM
SQLite should not use PHP memory for an in-memory database, that's part of the point of SQLite. If it does use PHP memory, then it's not working as described in the SQLite documentation: it should work purely within its own memory space independently of any programming language.

Otherwise, I'm afraid I'm completely out of suggestions - I've spent nearly 4 years now working on reducing memory consumption. The latest develop branch and using SQlite (because there is no longer any need to maintain any cell index in memory at all) is about the limits that I believe are achievable; and I'm experimenting with a couple of tweaks to SQLite configuration that should squeeze an extra few milliseconds of performance; but I can't think of anything more that I can do other than drop functionality or rewrite the whole thing as procedural code using pure arrays (which rather negates the purpose of PHPExcel, and has no guarantees that it will make anything better).
Apr 12, 2013 at 5:22 PM
Mark - thanks for your reply - I hope my questions wasn't read as a criticism of PHPExcel - because its an absolutely brilliant library - there is nothing else that is anywhere near it.

I have been testing using my Wampserver locally on my laptop - maybe it would be different if I tried a pure Apache / PHP environment it might be different ?

I will do some more testing on a serve environment - and i guess the sqlite3 file is an option ... perhaps i could contribute a pull request (or similar for codeplex) with an addition of the setting for file rather than memory.

Thanks for the hard work that you have put in to PHPExcel - its certainly appreciated by me :-)
Apr 12, 2013 at 6:19 PM
Something isn't working quite right ... when I run a simple Sqlite3 test :
echo ini_get("memory_limit").PHP_EOL;
$db = new SQLite3(':memory:');
$db->exec('CREATE TABLE foo (bar STRING)');
for ($index = 1;$index < 1000000; $index++) {
    $db->exec("INSERT INTO foo (bar) VALUES ('This is a test')");
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;
// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;
My output is
17:09:53 Current memory usage: 0.5 MB
17:09:53 Peak memory usage: 0.5 MB
If i look at windows task manager I can clearly see php.exe using ~32MB ... so something strange is happening there ....

When i run your sqlite3 example, slightly modified for loop (on line 93) :
for ($i = 2; $i <= 50000; $i++) {
i get the following output :
17:34:41 Enable Cell Caching using SQLite3 method
17:34:41 Create new PHPExcel object
17:34:41 Set properties
17:34:41 Add data
17:34:41 Hide 'Phone' and 'fax' columns
17:34:41 Set outline levels
17:34:41 Freeze panes
17:34:41 Rows to repeat at top
17:35:43 Write to Excel2007 format
17:39:46 File written to phptest.xlsx
Call time to write Workbook was 242.8200 seconds
17:39:46 Current memory usage: 89.25 MB
17:39:46 Peak memory usage: 114.5 MB
17:39:46 Done writing file
and that tallies with the windows task manager ... do you have any suggestions ?!?!?!