Millions getHashCode() calls make bad script perfomance

Topics: Developer Forum
May 16, 2010 at 9:34 PM

Hello respectfully.

I have some problem trying to generate XLS file with full dimention of ~1900 rows to 5 columns and final filesize about 180 Kb.
XLS file generated, but from 40 to 80 sec on different servers. Used ver. 5.16 and 5.22 of PHP.

Some style formatting applyed only for cells in two last columns, may be about 3000 cells, by follow code

$sheet -> setActiveSheetIndex(0) -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFont() -> getColor() -> setRGB($prow['color1']);
$sheet -> setActiveSheetIndex(0) -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFill() -> setFillType(PHPExcel_Style_Fill::FILL_SOLID) -> getStartColor() -> setARGB($prow['color2']);
$sheet -> setActiveSheetIndex(0) -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFont() -> setSize($prow['font_size1_3']);

 and some vertically merged cells (about 300-400) with code

$sheet -> getActiveSheet() -> getStyle('A'.$currentRow+$i) -> getAlignment() -> setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

It not my code and I still not refactor it, sorry. Code is not simple, so I not to prowide whole code here.

I was try to profile my script.
This is part of log of PHPEd IDE profiler. It provide count of every code line calls, that line min & max exec time and overall time spent to exec that line.

PHPExcel_Style_Color::getHashCode()
PHPExcel\Style\Color.php, line 398, count 2141154 times, min line exec time 0,002 ms, max line exec time 33,73 ms, total 6,5 sec
PHPExcel\Style\Color.php, line 401, count 2141154 times, min line exec time 0,009 ms, max line exec time 60,23 ms, total 31,1 sec

PHPExcel_Style_Border::getHashCode()
PHPExcel\Style\Border.php, line 374, count 1338205 times, min line exec time 0,002 ms, max line exec time 25,13 ms, total 3,9 sec
PHPExcel\Style\Border.php, line 377, count 1338205 times, min line exec time 0,006 ms, max line exec time 115,5 ms, total 10,5 sec

and below follow some calls of getHashCode() from different PHPExcel_Style parts...

There was also a code in my script

$s = array(
'alignment' => array(
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true,
),
);
$sheet -> getActiveSheet() -> getStyle('A1:L100') -> applyFromArray($s);

and when I disable it, counts and total exec time of PHPExcel_Style_Color::getHashCode() and etc now get smaller twice,

PHPExcel_Style_Color::getHashCode()
PHPExcel\Style\Color.php, line 398, count 1112781 times, min line exec time 0,002 ms, max line exec time 32,95 ms, total 3,5 sec
PHPExcel\Style\Color.php, line 401, count 1112781 times, min line exec time 0,009 ms, max line exec time 68,57 ms, total 17,5 sec

But it seems owerloaded too...

I has not work with that class before and not to familiar with it. But I think that a lot of millions of getHashCode() method calls an current whole script exec time is not good and expected behavior, so what wrong with class or with my mind or hands?

I found similar topic here, but it not give me any good idea...

Thanks at advance, Greeesha

May 16, 2010 at 10:33 PM

In addition about perfomance. Its small better than I describe.

Generating 1900 rows to 7 cols with 4 of cols styled (~6000-7000 cells) on dedicated server - 40 sec..

And also change lines in whole script

$sheet -> setActiveSheetIndex(0) -> getStyle

to

$sheet -> getActiveSheet() -> getStyle

not make visible effect

May 17, 2010 at 12:16 AM

Finally resolved.

I replace all parts of code

$sheet -> getActiveSheet() -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFont() -> getColor() -> setRGB($prow['color1']);
$sheet -> getActiveSheet() -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFill() -> setFillType(PHPExcel_Style_Fill::FILL_SOLID) -> getStartColor() -> setARGB($prow['color2']);
$sheet -> getActiveSheet() -> getStyle($letters[(3+$localCounter)].($currentRow+$i)) -> getFont() -> setSize($prow['font_size1_3']);

(there was 4 types of cell style, 4 blocks kind of this different by values)

with that

$sheet->getActiveSheet()->getStyle($curr_cell)->applyFromArray($styles['s1']);

where 

$styles['s1'] = array(
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array(
                    'argb' => '777777',
                )
            'font' => array(
                'color' => array(
                    'argb' => '99FF33',
                ),
                'size' => 22,
                 )
            )         
        )

and script exec time now 1 sec, not 40! It's a victory.

But so strange that font "size" is ignored now, because in Documentation Appendix A that key explaned...
Will be try further.