Reading large File increases Execution time & Memory (E.g file with 500000 records)

[kumarkal]

This is: a feature request

- [ ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

• To Read large files quickly

What is the current behavior?

• Takes more time to Read

What are the steps to reproduce?

• Try loading csv file of 500000 records and split it into smaller set of files of about 100000 records each using chunks which will be 5 files.
• As the size of file keeps reducing, performance increases

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

[bferrantino]

I would like to add a bit of clarity to this as well, since we are working together on this project.

We are processing rather large files, that are upwards of 300k lines. In order to scale this and not load the whole file into memory at once, we are using the chunkFilter functionality as laid out here: https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-files/#loading-a-spreadsheet-file under Reading Only Specific Columns and Rows from a File (Read Filters) .

We did some baseline testing of our script and we're noticing that once we pass the threshold of about 250k records, the $reader->load($inputFileName); portion of the code takes longer as the file gets larger. No matter how large of a chunk we are using, each time it tries to load the file, it takes about 20-30 seconds. This is compared to a file that is 100k records, that only takes on average 10 seconds to read each chunk. This number seems to increase/decrease significantly as we use a larger/smaller file, respectively.

What we're basically looking to determine here is how we can proceed further. Is there a known limitation with reading a certain sized file? Are there any steps we can take to make the reader perform better?

Below is the code we're using for our Chunk IReadFilter:

<?php

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

/**  Define a Read Filter class implementing IReadFilter  */
class Chunk implements IReadFilter
{
    private $startRow = 0;

    private $endRow = 0;

    /**
     * Set the list of rows that we want to read.
     *
     * @param mixed $startRow
     * @param mixed $chunkSize
     */
    public function setRows($startRow, $chunkSize)
    {
        $this->startRow = $startRow;
        $this->endRow = $startRow + $chunkSize;
    }

    public function readCell($column, $row, $worksheetName = '')
    {
        //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow
        if (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) {
            return true;
        }

        return false;
    }
}

?>

As well as a trimmed down version of the code we're using to chunk the file:

		// Create a new Reader of the type defined in $inputFileType
		$reader = IOFactory::createReader($inputFileType);

		// Define how many rows we want to read for each "chunk"
		$chunkSize = 10000;
		// Create a new Instance of our Read Filter
		$chunkFilter = new Chunk();

		// Tell the Reader that we want to use the Read Filter that we've Instantiated
		$reader->setReadFilter($chunkFilter);
		// Loop to read our worksheet in "chunk size" blocks
		for ($startRow = 1; $startRow <= $rawRows; $startRow += $chunkSize) {
			// Tell the Read Filter, the limits on which rows we want to read this iteration
			$chunkFilter->setRows($startRow, $chunkSize);
			// Load only the rows that match our filter from $inputFileName to a PhpSpreadsheet Object
			$spreadsheet = $reader->load($inputFileName);
		        ...
		}

Thank you in advance for your assistance.

Brian F (& Hemanth K, the original poster)

[dkarlovi]

I'm currently also looking into why it takes as much memory to read a file.

What I suggest is to install xdebug 2.6 which has memory usage profiling. I've already found some problematic areas in my code which reduced the memory usage somewhat, but it's still way to high.

[bferrantino]

Looking for some more guidance on this from the Devs. Maybe they know something directly related that can help us to resolve or at least improve our situation.

[pop-mihai]

Tried using file system caching of cells... a 411K row file still consumes beyond 1GB of RAM... chunks or no chunks the behavior is roughly the same. What are we missing ?

[zarte]

0.0

[pop-mihai]

We have been playing with this library for a few weeks now, benchmarking and profiling various read and write methods. We have hit several bottlenecks, managed to escape most of them except one: READING LARGE XLSX FILES !

Before we discuss about the XLSX problem in particular, I would like to share a few more things:

1. Following the examples provided here: https://phpspreadsheet.readthedocs.io/en/develop/topics/memory_saving/ - we tried using a simple disk cache.. and immediately failed because of iNodes limit. By default each excel cell would be saved as ONE file.. eventually exhausting the inodes server limit. So we wrote our own adaptor that stores several rows per file instead. This has significantly improved performance and memory usage.

2. Following the examples provided here: https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-files/ - we also made use of the readFilter to read large files in smaller chunks. This has also improved memory usage.

Obviously, both methods reduce memory usage but also increase processing time considerably.
By using a mix of both these changes, we managed to get a good overall result for everything except large XLSX files.

Here is our sample file: https://drive.google.com/file/d/1DbKD28u46BI761YjdsVelvjFux6YZJl1/view?usp=sharing

Whenever load() is called on this file, 8GB of RAM are exhausted immediately regardless of chunk size... and it seams to want even more after exhausting the full RAM available as it started using swap.

• php 7.2 environment
• a custom value binder that skips formula handling (not applicable in our use cases).
• we use setReadDataOnly(true) & setReadEmptyCells(false);
• dump to disk cache every 500 rows.
• reading in chunks of 4000 rows.
• garbage collecting at end of chunks.

For now we ended up making use of listWorksheetInfo to detect the total number of rows and columns and abandon the whole process if that number is too big. Method listWorksheetInfo seams to be able to handle the file quite well, but once you try to load the file everything blows up.

In total we have: 161 columns * 100K rows = 16.1 million cells.
We get it, its a big number.. but still after converting this file to CSV, we are able to process the entire thing without any problems and without ever going above 500MB.

After converting to XLS (which cannot go beyond 65536 rows) we are also able to process the 65K rows without exceeding 500 MB of RAM.

Any help on this matter would be highly appreciated.

[PowerKiKi]

Thanks @bferrantino and @pop-mihai for those detailed reports. I unfortunately cannot give you a quick and easy solution. Because I don't know PhpSpreadsheet's ugly details well enough. But we can attempt a few things.

First off, @pop-mihai you mentionned that CSV is successful with 16 millions cells. The first thing that comes to mind is that CSV does not have any styling at all. Have you tried commenting out all style-related code in the XLSX reader ? Does it make any difference ?

Were you able to do memory profiling with xdebug as @dkarlovi suggested ? were you able to pinpoint something specific that would consume especially a lot of memory ?

There is #648 that might help you. We need somebody to confirm the memory improvement and create a proper PR to merge it. Would one of you be able to help ?

[pop-mihai]

Hello @PowerKiKi , thank you for the input.
I looked over PR #648 but it seamed old compared to the latest phpSpreadSheet code.
I also tried all configurable of phpSpreadSheet, it does not make any difference when it comes to xlsx, we run out of resourecs.

We did some memory profiling yes, but at this volume it is somewhat hard to point any fingers. Biggest memory consuming process reported by xdebug is caused by the preg_match within the static coordinateFromString method which is weird.

We also looked at other libraries, including box/spout which is very promising due to its streaming mechanism - but suffers the same problem, memory leaks with xlsx, however not at this scale.

• CSV does just fine with 2 MB of RAM !
• XLSX ate up to ~500MB of RAM (still better than 8GB)
• They offer no support for XLS, nor do they plan to ever offer such support.

We believe that there is a memory leak in the compiled libxml version. With PHP 7.2, the compiled libxml version is: 2.9.1 while on xmlsoft.org the latest version is: 2.9.7 released Nov 02 2017. If you look at the change log for keyword "memory" between the 2 versions you`ll see over 25 fixes!

We will attempt to compile our php instance with the latest libxml version and see if this makes a notable difference. Then we`ll have to choose the best tool for each need (csv, xls, xlsx).

[dkarlovi]

Biggest memory consuming process reported by xdebug is caused by the preg_match within the static coordinateFromString method which is weird.

This is not weird at all, the functionality is used for each cell as it's being read. I had the same problem.

[PowerKiKi]

If that preg_match can indeed be confirmed to be a bottleneck, it could perhaps be replaced by a very simple parsing process instead. Since the pattern is actually quite simple, the parsing code would be rather straightforward and could maybe save time and/or memory ?

Anyway, as you know PhpSpreadsheet is not known for its speed or memory consumption. So every effort to improve the situation would be very welcome. @pop-mihai you seems to be in a good spot for that, with a strong use-case and good knowledge of the overall issues. Don't hesitate to experiment with PhpSpreadsheet own code and see if it could be merged back in a second step...

[dkarlovi]

@PowerKiKi I actually started working on this a bit for my use case, I've switched from getCell() to getCellByColumnAndRow(), passing createCell = false to missing cells.

See sigwinhq/xezilaires@e69afc4#diff-e11287c474a458a580cd0b18582ae5a8R236

[ryzr]

Does your spreadsheet have many calculated cells? I can import a sheet with around ~200k cells within a few seconds, however a sheet with ~600k cells were many of them are calculated times out after 5 minutes. Here are the major culprits in my profile. Note that it did time out, so it probably could have gotten worse?

EDIT: sorry, not thinking straight. It timed out because of xdebug - but my point was that there's definitely a high cost if your sheet has a lot of calculated cells.

castToFormula

getCell

coordinateFromString

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.