Linux verses Windows

Topics: Developer Forum
May 22, 2013 at 4:10 PM
I am using PHPExcel for batch processing of data, exporting from a Postgres database into a spreadsheet and importing that spreadsheet back into postgres (presumably after it has been changed).

I am seeing substantial differences in the behavior of the application when running on Linux verses Windows.

The Linux platform is Centos 6.4 Windows 8.
In both cases the version of phpexcel is 1.7.8 and PHP is 5.4

My development case reads 1993 records from postgres (version 9.2 btw)
In the development, Windows always works and does so quickly.
On Linux, if I format the cells, the job never completes. If I do not format the cells, it does complete but runs slowly.

I've placed debugging code into the program and memory use does not get about 34 megs.
With formatting, the program stops after processing 1557 records and at that point has used 25 megs of memory.

$obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(42);

Does anyone have any theories on this behavior or where/how to proceed from here?
Jun 11, 2013 at 2:41 PM
I really could you some help on this. Anyone???
Coordinator
Jun 14, 2013 at 12:18 PM
Very difficult to assess... PHPExcel is developed as a cross-platform library, though there can easily be performance differences as a result of PHP version and configuration (memory, available modules, xdebug, etc), speed of filesystem (reading/writing to shared folders can be incredibly slow).

There's also a couple of "kludges" in the code for problematic Operating systems such as AIX with iconv(), but nothing specific for Centos.
Jun 14, 2013 at 12:24 PM
Mark. Thanks. Is there any info I can provide that would make this easier to assess?

The hardware in question is an 8-core 12 Gigs of memory platform. It should be blowing through this work.


Rogers
Coordinator
Jun 14, 2013 at 12:37 PM
Not really, I write code that should work across all platforms, I test it on a variety of platforms; but debugging at that level could easily be a case of looking at how PHP itself works internally on certain platforms which is outside my experience.

I've mentioned shared drives (e.g. Samba) that I know are slow anyway.

Is anything being written to PHP logs, warnings or errors of any kind that might indicate problems? Even the overhead of handling/suppressing warnings is a pretty big PHP overhead.
Jun 14, 2013 at 12:43 PM
Samba isn't a factor and the only logging is when I've created debug statements to figure out what is going on.
Is there a way to limit the memory in play to only 8 columns max?


Rogers
Jun 14, 2013 at 12:51 PM
Mark:

Here is the code in question, in case that helps:

function writedata($obj, $info) {
$obj->setActiveSheetIndex(0);
$row = 2;
foreach ($info as $key => $info) {
    $col = 'A';
    $generic = $info['generic'];
    $name = $info['name'];
    $ingredients = $info['ingredients'];
    $form = $info['form'];
    $admin = $info['admin'];
    $dosage = $info['dosage'];
    $maker = $info['maker'];
    $ctry = $info['ctry'];

    $obj->getActiveSheet()->setCellValue($col . $row, $maker);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(42);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $ctry);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(9);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $generic);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(20);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $name);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(40);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $form);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(25);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $dosage);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(14);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $admin);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(14);
    $col++;

    $obj->getActiveSheet()->setCellValue($col . $row, $ingredients);
    $obj->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $obj->getActiveSheet()->getColumnDimension($col)->setWidth(67);
    $row++;

}
}
Coordinator
Jun 14, 2013 at 1:58 PM
Edited Jun 14, 2013 at 2:00 PM
Don't set the column dimensions for every row (inside the loop); you only need to set a column dimension once.

Don't set the alignment for each individual cell; it's much more efficient to set style features for a range of cells:

e.g.
$obj->getActiveSheet()
    ->getStyle("A2:H250")
    ->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
Look at using the fromArray() method to set a row (or block) of cells in a single call rather than setting the cells individually

That's not Windows/*nix specific; just a general performance improvement, and should speed things up on both platforms
Jun 14, 2013 at 10:17 PM
Thanks. Got incredibly busy and didn't have a chance to try this yet. On tap for tomorrow.

Rgoers