[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

slowness of WIN32OLE.rb in 1.8.2 - faster in 1.8.4?

Greg Lorriman

1/24/2006 7:17:00 AM

Hi,

I've extensively used WIN32OLE.rb to program against excel data and
Access databases via ADO. Even after generating the wrappers using the
generator tool COM object calls are excrutiatingly slow (indeed I
noticed no difference despite what it says in pickax2; and yes I did
modify my code to instantiate the wrapper's constructor)

I hate to say it but I've ended up doing quite a lot of VB instead of
Ruby simply because I can't live with the speed penalty.

It is so slow that it would indicate to me a problem in win32ole.

Any comments?

Greg
6 Answers

greg.rb

1/24/2006 5:39:00 PM

0

i am not an expert(yet) but here is my 2 cents:

i didn't find the wrapper helpful. seemed to take time to load up. i
posted some results on a prior thread - "win32ole --Dynamic lookup vs
Static?"

could you post the ole objects you create to manipulate?

if you just create an excel object without also creating another sub
object to manipulate a specific sheet or range of cells it could be a
little slower.

Corey Lawson

1/24/2006 5:48:00 PM

0

What does some of your code look like?

On 1/24/06, Greg Lorriman <temp@lorriman.com> wrote:
>
> Hi,
>
> I've extensively used WIN32OLE.rb to program against excel data and
> Access databases via ADO. Even after generating the wrappers using the
> generator tool COM object calls are excrutiatingly slow (indeed I
> noticed no difference despite what it says in pickax2; and yes I did
> modify my code to instantiate the wrapper's constructor)
>
> I hate to say it but I've ended up doing quite a lot of VB instead of
> Ruby simply because I can't live with the speed penalty.
>
> It is so slow that it would indicate to me a problem in win32ole.
>
> Any comments?
>
> Greg
>
>
>

Greg Lorriman

1/24/2006 9:18:00 PM

0

>if you just create an excel object without also creating another sub
object to manipulate a specific sheet or range of cells it could be a
little slower.

I was aware of that, and so all my code immediately assigns to the
nearest object. For example :

wb=excel.workBooks.load("something.xls");
ws=wb.sheets("somesheet");
ws.range('a1:a100').value.each { |cell| something; };

and I'll continue using wb,ws and the arrays from the "range" calls as
appropriate. Notice how I get a ruby array in the range call thereby
side-stepping 100 (slow) calls (it is faster, but only as a proportion
of a gigantic amount).

Its not enough 'though; the performance problem in 1.8.2 is of
elephantine proportions. Someone here also suggested, some time ago,
that WIN32OLE has 100% cpu usage even when idle?!? Was that a hoax (I
haven't tried testing for that)? That might go some way to explaining
my problems.

Anyone one else experiencing this, apart from the odd posts to this
group from the past?

Greg

Corey Lawson

1/24/2006 9:47:00 PM

0

On 1/24/06, Greg Lorriman <temp@lorriman.com> wrote:
>
> Its not enough 'though; the performance problem in 1.8.2 is of
> elephantine proportions. Someone here also suggested, some time ago,
> that WIN32OLE has 100% cpu usage even when idle?!? Was that a hoax (I
> haven't tried testing for that)? That might go some way to explaining
> my problems.


Well, I'm not seeing it. I'm messing around with ADO thru WIN32OLE in an IRB
session, and it's been holding the ADO connection open for a few days now,
and it's not running a load higher than the usual Windows 2000 background
noise.

Which version of Excel are you using?

Have you compared your ruby scripts with the same code in (barf) VBScript?

Ruby fakes early binding, but it's still doing late binding, vis a vis
Visual Basic. Try out your VB code with an explicit reference to the Excel
library (early binding), and with late binding (i.e., dim xl as object : set
xl = CreateObject("Excel.Application")) and see if there's a difference,
because there should be.

Anyone one else experiencing this, apart from the odd posts to this
> group from the past?
>
> Greg
>
>
>

Corey Lawson

1/24/2006 10:36:00 PM

0

What is slow, though? In Ruby 1.8.2, with a workbook with one sheet with
1000 cell values (pop'd with Random() *1000 values), I get pretty snappy
performance:

require 'win32ole'

xl = WIN32OLE.new('Excel.Application')
wb = xl.Workbooks.open('D:\docs\corey\rubytest.xls')
a = wb.ActiveSheet

#ws.range('a1:a100').value.each { |cell| something; };
r = a.range('b1:b1000')

#both of these work pretty dang fast for me:
r.value.each do |c|
#do something
end

and
r.each.value do |c|
#do something
end

even:

a.range('B1:B1000').each do |c|
print c.value
end

...is still pretty fast, but probably only 60-70% of doing the loop with the
reference directly to the Range collection.

But they're both fast enough, in that any apparent visual slowdown is in the
console output, not in the actual loop or dealing with transfers between
Excel & Ruby thru Win32OLE.

But I have 1100 MB RAM (and only an Athlon 1400 processor), using Excel 2000
& Win2000 SP4. Is your computer RAM-limited?
I have 2x512MB sticks, so I'm not going to pull any out to test with less
RAM...


On 1/24/06, Corey Lawson <corey.ssf.lawson@gmail.com> wrote:
>
> On 1/24/06, Greg Lorriman <temp@lorriman.com> wrote:
> >
> > Its not enough 'though; the performance problem in 1.8.2 is of
> > elephantine proportions. Someone here also suggested, some time ago,
> > that WIN32OLE has 100% cpu usage even when idle?!? Was that a hoax (I
> > haven't tried testing for that)? That might go some way to explaining
> > my problems.
>
>
> Well, I'm not seeing it. I'm messing around with ADO thru WIN32OLE in an
> IRB
> session, and it's been holding the ADO connection open for a few days now,
> and it's not running a load higher than the usual Windows 2000 background
> noise.
>
> Which version of Excel are you using?
>
> Have you compared your ruby scripts with the same code in (barf) VBScript?
>
> Ruby fakes early binding, but it's still doing late binding, vis a vis
> Visual Basic. Try out your VB code with an explicit reference to the Excel
> library (early binding), and with late binding (i.e., dim xl as object :
> set
> xl = CreateObject("Excel.Application")) and see if there's a difference,
> because there should be.
>
> Anyone one else experiencing this, apart from the odd posts to this
> > group from the past?
> >
> > Greg
> >
> >
> >
>
>

Greg Lorriman

1/25/2006 12:07:00 AM

0

Thanks for your reply.

It rather depends on what you mean by snappy performance. 1000 cells
processed in 1 second may seem good to you. However its still awful for
a >1 Ghz computer. I am processing enourmous numbers of excel cells and
a lot of Access records (seperate tasks). Obviously the reason I
mentioned using VB was because I am not having to wait for the same
operations (also I am the sole programmer and the code used is nearly
copy/pasted).

Conolse output speed isn't an issue.


Greg