xaviermdq
commented
1 year ago I think a speed and memory usage test would not be very useful in absolute terms: it depends on PC speed, PHP version, operating system, system load, etc, etc. A more useful benchmark is to make a comparative test between the different classes under the same conditions. Or benchmarking of the same class by making internal changes to optimize speed and/or memory usage (make a certain block of code one way or another, make certain functions or variables static, or change a function to a variable when we need data convertions, or vice-versa, etc.). I did a test comparing the class you mentioned, mk-j (which, by the way, has low activity: there are many pull requests and issues pending), and simplexlsxgen. But first a brief explanation. The way the two classes work are slightly different:
- simplexlsx:
- sheet data production outside the object (*1)
- copy data to object (addSheet method)
- data processing and output file generation (__toString, saveAs or downloadAs methods)
- PHP_XLSXWriter, format 1:
- sheet data production outside the object (*1)
- copy data to object (writeSheet method)
- data processing and output file generation (writeTo* methods)
- PHP_XLSXWriter, format 2:
- row data production outside the object (*2)
- copy data to object (writeSheetRow method)
- data processing and output file generation (writeTo* methods)
1 : the entire sheet is an array where each element represents a row and each element, in turn, is another array whose elements represent the columns 2 : row is an array where each element represents a column
PHP_XLSXWriter format 1, use format 2 with each element of the data passed. From this, it follows that a proper comparison of classes is achieved using format 1 of PHP_XLSWriter. To get the data generation out of the measurement, I first generate the data, get time/memory state, create object, load data, get state again, call the file generator method, and get state again. From the tests carried out, it is concluded that:
- PHP_XLSXWriter uses about 3% more memory than simplexlsxgen
- PHP_XLSXWriter is between 2.5 and 3.5 (sometimes 5) times slower than simplexlsxgen
The code used for benchmarking is:
<?php
ini_set('memory_limit', '512M');
set_time_limit(240);
require_once 'simplexlsxgen.php';
require_once 'xlsxwriter.php';
//Init
$testrepetition = 4;
$testrows = [50000, 100000, 150000, 200000];
$testsuite = [];
for ($i = 0; $i < count($testrows); $i++) $testsuite[$testrows[$i]] = [
'data' => [], 'shuchkin' => [], 'mkj' => []
];
//Data generation
foreach ($testsuite as $rows => &$d) {
$d['data'] = [];
for ($i=0; $i<$rows; $i++) $d['data'][] = [$i, $i + 1, $i + 2, $i + 3];
}
unset($d);
function Test($class, &$data, $out = true) {
$rows = count($data);
$cols = count($data[0]);
$filename = __FUNCTION__ . " {$rows}.xlsx";
@unlink($filename);
if ($out) echo "<pre><b>Testing with a {$rows}×{$cols} sheet...</b>\r\n";
switch ($class) {
case 'Shuchkin':
$time1 = microtime(true);
$mem1 = memory_get_usage();
$xlsx = new SimpleXLSXGen2();
$xlsx->addSheet($data, 'Test Sheet');
$time2 = microtime(true);
$mem2 = memory_get_usage();
$xlsx->saveAs($filename);
$time3 = microtime(true);
$mem3 = memory_get_usage();
break;
case 'MKJ':
$time1 = microtime(true);
$mem1 = memory_get_usage();
$xlsx = new XLSXWriter();
$xlsx->writeSheet($data);
$time2 = microtime(true);
$mem2 = memory_get_usage();
$xlsx->writeToFile($filename);
$time3 = microtime(true);
$mem3 = memory_get_usage();
break;
}
if ($out) echo "
<table>
<thead><tr><th></th><th>Creation</th><th>Saving</th><th>Total</th></tr></thead>
<tbody>
<tr><td>Time [us]</td><td>" . sprintf("%.0f", round(($time2-$time1)*1000000)) . "</td><td>" . sprintf("%.0f", round(($time3-$time2)*1000000)) . "</td><td>" . sprintf("%6.0f", round(($time3-$time1)*1000000)) . "</td></tr>
<tr><td>Memory [KB]</td><td>" . sprintf("%.2f", ($mem2-$mem1)/1024) . "</td><td>" . sprintf("%.2f", ($mem3-$mem2)/1024) . "</td><td>" . sprintf("%.2f", ($mem3-$mem1)/1024) . "</td></tr>
</tbody>
</table>
</pre>";
return [round(($time3-$time1)*1000000), ($mem3-$mem1)/1024];
}
?><!DOCTYPE html>
<html>
<head>
<style>
th,td {border: 1px solid black}
th {background: #AAA}
</style>
</head>
<body>
<h1>shuchkin / simplexlsxgen</h1>
<?php
foreach ($testsuite as $rows => &$d) {
Test('Shuchkin', $d['data']);
$tsum = $msum = 0.0;
for ($i = 0 ; $i < $testrepetition ; $i++) {
$res = Test('Shuchkin', $d['data'], false);
$tsum += $res[0];
$msum += $res[1];
}
$d['shuchkin'] = [sprintf("%.3f", ($tsum/$testrepetition)/1000), sprintf("%.2f", $msum/$testrepetition)];
}
unset($d);
?>
<table>
<thead>
<tr><th>Rows</th><th>Time [ms]</th><th>Memory [KiB]</th></tr>
</thead>
<tbody>
<?php
foreach ($testsuite as $rows => $d) {
echo "<tr><td>{$rows}</td><td>{$d['shuchkin'][0]}</td><td>{$d['shuchkin'][1]}</td></tr>";
}
?>
</tbody>
</table>
<hr>
<h1>mk-j / PHP_XLSXWriter</h1>
<?php
foreach ($testsuite as $rows => &$d) {
Test('MKJ', $d['data']);
$tsum = $msum = 0.0;
for ($i=0 ; $i<$testrepetition ; $i++) {
$res = Test('MKJ', $d['data'], false);
$tsum += $res[0];
$msum += $res[1];
}
$d['mkj'] = [sprintf("%.3f", ($tsum/$testrepetition)/1000), sprintf("%.2f", $msum/$testrepetition)];
}
unset($d);
?>
<table>
<thead>
<tr><th>Rows</th><th>Time [ms]</th><th>Memory [KiB]</th></tr>
</thead>
<tbody>
<?php
foreach ($testsuite as $rows => $d) {
echo "<tr><td>{$rows}</td><td>{$d['mkj'][0]}</td><td>{$d['mkj'][1]}</td></tr>";
}
?>
</tbody>
</table>
<hr>
<h1>Conclusions</h1>
<table>
<thead>
<tr><th>Rows</th><th>Time [ms]</th><th>Memory [KiB]</th></tr>
</thead>
<tbody>
<?php
foreach ($testsuite as $rows => $d) {
$trel = round($d['mkj'][0] / $d['shuchkin'][0], 2);
$trelp = round( ($trel - 1) * 100);
$mrel = round($d['mkj'][1] / $d['shuchkin'][1], 2);
$mrelp = round( ($mrel - 1) * 100 , 2);
echo "<tr><td>{$rows}</td><td>mkj {$trel} times slower than shuchkin ({$trelp}%)</td><td>mkj {$mrel} times more memory usage than shuchkin ({$mrelp}%)</td></tr>";
}
?>
</tbody>
</table>
</body>
</html>
Simil to report https://github.com/mk-j/PHP_XLSXWriter