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

[Greg Lorriman]

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.rb]

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]

------=_Part_37963_2838160.1138124845079
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

What does some of your code look like?

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

------=_Part_37963_2838160.1138124845079--

 

[Greg Lorriman]

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]

------=_Part_1634_25024757.1138139198623
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 IR=
B
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 : se=
t
xl =3D 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

------=_Part_1634_25024757.1138139198623--

 

[Corey Lawson]

------=_Part_2226_14869795.1138142144951
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 =3D WIN32OLE.new('Excel.Application')
wb =3D xl.Workbooks.open('D:\docs\corey\rubytest.xls')
a =3D wb.ActiveSheet

#ws.range('a1:a100').value.each { |cell| something; };
r =3D 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 th=
e
reference directly to the Range collection.

But they're both fast enough, in that any apparent visual slowdown is in th=
e
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 200=
0
& 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...

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 Exce= l
library (early binding), and with late binding (i.e., dim xl as object :
set
xl =3D 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

------=_Part_2226_14869795.1138142144951--

 

[Greg Lorriman]

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