Memory consumption.

Posted on Wed Jan 17 17:01:37 2007 by jmcnamara in response to 4064

Have a look at: Reducing the memory usage of Spreadsheet::ParseExcel and more directly related to your case A different kind of Spreadsheet::ParseExcel memory problem.

John.
--

Direct Responses: 4127 | Write a response

Posted on Mon Jan 29 17:03:47 2007 by jmcnamara in response to 4127

Re: Memory consumption.

Spreadsheet::ParseExcel isn't my module but I know a little bit about how it works.

I had a look at trying to weaken() the links internally but it looked a bit tricky to figure out all of the circular links.

Instead, I wrote a small function to take an ParseExcel object created by theSpreadsheet::ParseExcel::Workbook::Parse() method and to free the circular links manually. Here it is:

############################################################################### # # free_parser() # # Break the circular references in the Spreadsheet::ParseExcel object so the # memory can be garbage collected by perl. The circular references are caused # by links to the parent from the child objects. # sub free_parser { my $parser = $_[0]; # Free the parent _Book and Cell Format references. for my $worksheet ( @{ $parser->{Worksheet} } ) { $worksheet->{_Book} = undef; for my $cell ( @{ $worksheet->{Cells} } ) { for my $obj ( @{$cell} ) { my $type = ref $obj; next unless $type; if ( $type eq 'Spreadsheet::ParseExcel::Cell' ) { $obj->{Format} = undef; } } } } # Free the Font refs from Format for my $format ( @{ $parser->{Format} } ) { $format->{Font} = undef; } # Free the parent refs in the callback tables. for my $key ( keys %{ $parser->{_Excel}->{FuncTbl} } ) { $parser->{_Excel}->{FuncTbl}->{$key} = undef; } $parser = undef; }

You can use it as follows.

... my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file); foreach my $sheet ( @{ $excel->{Worksheet} } ) { # Do some work here. } # Finished parsing current file. free_parser($excel); ...

I tested it with a small number of Excel files of different types but there may still be some links that aren't freed. Let me know how you get on.

John.
--

Direct Responses: 4188 | Write a response

Posted on Fri Feb 2 12:54:24 2007 by pete1234 in response to 4188

Re: Memory consumption.

Hi John, thanks for your efforts. The function seems to lessen the impact, but it still leaks memory. (and with amount of input, almost any loss is fatal over time). Sample output from Devel::Leak is below, I don't know if it helps. I've had to go down the csv route because of time pressure. Thanks very much anyway.

new 0xa0ac214 : SV = PVHV(0xa0a8930) at 0xa0ac214 REFCNT = 7 FLAGS = (PADBUSY,PADMY,OBJECT,SHAREKEYS) IV = 20 NV = 0 STASH = 0x9c5fb84 "Spreadsheet::ParseExcel::Format" ARRAY = 0xa0ad478 (0:17, 1:12, 2:2, 4:1) hash quality = 88.5% KEYS = 20 FILL = 15 MAX = 31 RITER = -1 EITER = 0x0

new 0xa0ac220 : SV = RV(0xa0a5b38) at 0xa0ac220 REFCNT = 1 FLAGS = (ROK) RV = 0xa0ac034

new 0xa0ac22c : SV = PVAV(0xa0ac554) at 0xa0ac22c REFCNT = 1 FLAGS = () IV = 0 NV = 0 ARRAY = 0xa0ab3a0 FILL = 3 MAX = 3 ARYLEN = 0x0 FLAGS = (REAL)

new 0xa0ac238 : SV = PVAV(0xa0ac4d0) at 0xa0ac238 REFCNT = 1 FLAGS = () IV = 0 NV = 0 ARRAY = 0xa0ab378 FILL = 3 MAX = 3 ARYLEN = 0x0 FLAGS = (REAL)

new 0xa0ac244 : SV = IV(0xa0aa104) at 0xa0ac244 REFCNT = 1 FLAGS = (IOK,pIOK) IV = 64

new 0xa0ac250 : SV = IV(0xa0aa108) at 0xa0ac250 REFCNT = 1 FLAGS = (IOK,pIOK) IV = 64

...
# Test 3 got: "53285" (./dsu_man.pl at line 167)
# Expected: "33706" (Nuber of SVs created unexpected)

Direct Responses: 4269 | Write a response

Posted on Wed Feb 7 09:43:39 2007 by jdaily in response to 4269

Re: Memory consumption.

I haven't worked with this module in a few years. But when I did, I remember having the same issue. I tweaked the module to store the data in a hash, as oppose to the object that it was using. This worked well enough for my needs. Not sure if you have tried this, but if not, it's worth a shot.

Write a response