Performance Issue Opening Moderate Sized Spreadsheets

[jfeaver]

Hi,

First of all, thanks for creating this gem!

I'm trying to use the gem to load account data using excel. I foresee having 100+ users in a single excel file with 15+ attributes for each user. When I open a sample file(Spreadsheet.open('sample.xls')), this takes considerable time (>1 minute in some cases on a MacBook Pro).

I suggest adding some sort of logging to the console so the user knows that Spreadsheet is still parsing and not in an infinite loop.

In the long term, I suggest looking into how performance could be improved while opening larger spreadsheets.

I originally thought this was an error and looked at a similar sounding issue: #8. For that reason, I'll share the first line of a stack trace after using ctrl-c:

### irb ###
IRB::Abort: abort then interrupt!
    from /Users/nathan/.rvm/gems/ruby-1.9.3-p0@foo/gems/spreadsheet-0.7.7/lib/spreadsheet/workbook.rb:94:in `call'

### rails console ###
IRB::Abort: abort then interrupt!
    from /Users/nathan/.rvm/gems/ruby-1.9.3-p0@foo/gems/activesupport-3.2.11/lib/active_support/core_ext/array/conversions.rb:55:in `call'

Cheers,
Nathan

[jfeaver]

Update:
Tried opening a full size file to test how long it would take. Here are the results:

File:
4 Sheets
1674 cells with data

Upload:
~4 minutes
~Computer Activity Monitor showing full memory usage, including 8GB of swap
~Upload was not successful. Here's the error and trace:

irb(main):002:0> Spreadsheet.open('file.xls')
Errno::EINVAL: Invalid argument - <STDOUT>
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:311:in `write'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:311:in `printf'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:311:in `output_value'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:160:in `block (2 levels) in eval_input'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:273:in `signal_status'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:156:in `block in eval_input'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb/ruby-lex.rb:243:in `block (2 levels) in each_top_level_statement'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `loop'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `block in each_top_level_statement'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `catch'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `each_top_level_statement'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:155:in `eval_input'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:70:in `block in start'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:69:in `catch'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/irb.rb:69:in `start'
    from /Users/nathan/.rvm/rubies/ruby-1.9.3-p0/bin/irb:12:in `<main>'
Maybe IRB bug!

[zdavatz]

Can you please post your sample file and code to github so I can test on Linux as well!

[jfeaver]

Here's the files:

the spreadsheet: https://github.com/jfeaver/public/blob/master/test_user_data.xls
the code: https://github.com/jfeaver/public/blob/master/spreadsheet_loader.rb

As I was preparing this for you, I ran my script using ruby spreadsheet_loader.rb and the file was opened and ready to go in 0.02 seconds! I suppose the message at the bottom of the description might have been correct: Maybe IRB bug!. I'm relieved that I'll still be able to use the gem. Once again, thanks for creating and sharing such a useful tool!

You can close the issue if you don't need to support IRB sessions.

[zdavatz]

;), ok. I will then close the issue. Good that you tried it in real life as well! ;) - and thanks for sharing the files, the code and the issue.