Multiple Excel Servers

[Robert ap Rhys]

Hi,

I have a real-time system pumping out DDE feeds from a manufacturing process
into Excel. Calculations are done on the input (up to 40000 rows) and this
analysis is used to feed back to production managers. Originally, this was a
single feed into a single Excel workbook. However, I now have to accommodate
up to 20 feeds. Due to Excel's internal limits on data, I have found that
each feed must go into its own instance of Excel. In order to consolidate
the feeds I have a single 'loader' instance of Excel which creates, via
COM/OLE, up to 20 out-of-process server instances of Excel. The 'loader' can
then respond to events in the servers and provides a single point of
feedback.

Unfortunately, using Excel 2003 and Windows XP Pro, on a top of the range
machine (quad core, 4 GB RAM), I'm running out of resources after running
about 8 servers. Since I can run 20 /independent/ instances of the server
workbooks quite easily, I'm thinking that the problem is with COM
bottlenecks, though the feedback communication between the servers and the
client is only amounting to maybe 80 events in total per working day. Is
there anything I can do or am I going to have to re-architect?

Your thoughts would be appreciated.

Robert

 

[John.Greenan]

If you are running 20 instances of Excel at one time on a PC that's an awful
lot of memory and processor power you'll need.

You could try:

1. Running excel without any add-ins and clean up the memory footprint, that
might help.

2. Binding the different instances of the DDE servers and Excel instances to
different cores (you'll need to work out how best to handle this - is it best
to keep the excel instance on the same chip / core or on another - I don't
know)

3. Hooking your DDE feeds into an Excel RTD solution - should remove the
need to have 20 excel instances but may prove to be time consuming - a
VB/C++/c# based solution rather than VBA.

4. Switching the "servers" to run with screen updating off / interactive off

5. Examine the equations - are your DDE feeds causing lots of
recalculations? How are the 40,000 row equations constructed? There's a
document on MSDN about the excel calculation model - have a read of it and
that may give you some tips.

But, I think you need to look at redesigning the solution. Having 40,000
rows of calculations based on real time data is going to hit any excel
instance very hard, but having 20 excel instances that are getting hit hard
will cause the PC to run like a sloth.

Hope that gives you a few pointers in the right direction...

 

[Robert ap Rhys]

Hi John,

Thanks for responding

If you are running 20 instances of Excel at one time on a PC that's an awful
lot of memory and processor power you'll need.

As I said in the original post "I can run 20 /independent/ instances of the
server
workbooks quite easily". Windows purrs along using only about 25% resources.
It's only when I try to link the whole thing together using COM that I get
issues.

You could try:

1. Running excel without any add-ins and clean up the memory footprint, that
might help.

I thought opening an instance of Excel via automation did this?

2. Binding the different instances of the DDE servers and Excel instances to
different cores (you'll need to work out how best to handle this - is it best
to keep the excel instance on the same chip / core or on another - I don't
know)

Wouldn't have the first idea how to do this in code - I'll do some research.

3. Hooking your DDE feeds into an Excel RTD solution - should remove the
need to have 20 excel instances but may prove to be time consuming - a
VB/C++/c# based solution rather than VBA.

I'd have to write a calculation engine, wouldn't I? Excel already has one;
that's why I'm using it ;-)

4. Switching the "servers" to run with screen updating off / interactive

off

They're already hidden. That's the default when you create an automation
instance.

5. Examine the equations - are your DDE feeds causing lots of
recalculations? How are the 40,000 row equations constructed? There's a
document on MSDN about the excel calculation model - have a read of it and
that may give you some tips.

They were calculation-intensive but I've optimised them so that calculation
is to all intents and purposes instantaneous. As I said, when not tied
together with COM, they don't tax Windows unduly.

But, I think you need to look at redesigning the solution. Having 40,000
rows of calculations based on real time data is going to hit any excel
instance very hard, but having 20 excel instances that are getting hit hard
will cause the PC to run like a sloth.

But it doesn't. Until I wrap a COM 'loader' around it.

Thanks for your attention to this.

Robert

 

[John.Greenan]

I'd have to write a calculation engine, wouldn't I? Excel already has one;
that's why I'm using it ;-)

-->No. Read the documentation for RTD. It replaces DDE in communication
with Excel.

They're already hidden. That's the default when you create an automation
instance.

Why not post your "COM Loader" code?? It's hard to make novel suggestions
when there's no code to look at.

 

[Robert ap Rhys]

-->No. Read the documentation for RTD. It replaces DDE in communication
with Excel.

Maybe I'm getting the wrong end of the stick here. I have a DDE feed from a
manufacturing process. This is not negotiable. If I write a, say vb.net app
to consume the DDE feed and do my analysis before feeding the results back
to Excel via RTD I /will/ have to reproduce Excel's (server workbook)
calculations in my vb.net code, won't I?

Why not post your "COM Loader" code?? It's hard to make novel suggestions
when there's no code to look at.

It's very simple, but there's too much to post. I have a class that wraps
the Excel.Application object (declared Withevents) and a strongly-typed
collection of instances of that class. When I want to create a new 'server'
instance I call the Add method of the collection and that goes off and
creates the new instance of Excel and loads up the DDE consumer workbook.
The class raises events when things of interest happen inside my 'server'
instance of Excel. These events are captured and converted to information,
alerts etc on the user interface.

Thanks

Robert

 

[Brian Gideon]

Maybe I'm getting the wrong end of the stick here. I have a DDE feed from a
manufacturing process. This is not negotiable. If I write a, say vb.net app
to consume the DDE feed and do my analysis before feeding the results back
to Excel via RTD I /will/ have to reproduce Excel's (server workbook)
calculations in my vb.net code, won't I?

Not necessarily. The VB.NET application could just translate the DDE
messages into RTD messages. The calculations could still take place in
Excel. Though, I'm not sure if that would help you any. I'm not clear
on what limits Excel has on the amount of data and why it's affecting
you. Couldn't you just place each feed into a different sheet?

Also, DDE is old and cumbersome to work with. Sometimes you don't have
a choice, but since you mentioned the data is coming from a
manufacturing process I suspect you may have an alternative. OPC (Ole
for Process Control) is supported by many PLC and SCADA vendors.
Unfortunately, I don't have any experience getting OPC data into Excel.
I just thought I'd throw that out there.

Brian

 

[John.Greenan]

Comments in with the mail...


--
www.alignment-systems.com

Maybe I'm getting the wrong end of the stick here. I have a DDE feed from a
manufacturing process. This is not negotiable. If I write a, say vb.net app
to consume the DDE feed and do my analysis before feeding the results back
to Excel via RTD I /will/ have to reproduce Excel's (server workbook)
calculations in my vb.net code, won't I?

No, my suggestion is to wrap the DDE server in RTD and then hook that up to
one Excel instance - one workbook with 20 sheets perhaps.

It's very simple, but there's too much to post. I have a class that wraps
the Excel.Application object (declared Withevents) and a strongly-typed
collection of instances of that class. When I want to create a new 'server'
instance I call the Add method of the collection and that goes off and
creates the new instance of Excel and loads up the DDE consumer workbook.
The class raises events when things of interest happen inside my 'server'
instance of Excel. These events are captured and converted to information,
alerts etc on the user interface.

Thanks

Robert

The solution will not every work reliably. The overhead of interprocess
communication from one excel instance to another is very high. If you look
on MSDN you can see that this take a lot of processor cycles.

You should note that COM events can be dropped by Excel under heavy load, so
the data will not update properly in your "master" worksheet if it's fed by
COM events from the "slave" workbooks and the PC is under heavy load. This
is not well documented but it's a fact of COM.

Your problem is that you are trying to push the envelope and link a
deprecated technology (DDE) using a technology on the way out (COM) in a way
that is never going to reliably work. I strongly suggest that you re-think
the way you are doing this.

If you want to discuss this, send me an email offline (go figure the email
address - it's pretty obvious) with a UK landline phone number and I will
explain - it's not well documented about COM missing events and it merits
more explanation, but I don't have time to type it all right now.

 

[Robert ap Rhys]

Hi Brian,

Thanks for responding.

Not necessarily. The VB.NET application could just translate the DDE
messages into RTD messages. The calculations could still take place in
Excel. Though, I'm not sure if that would help you any. I'm not clear
on what limits Excel has on the amount of data and why it's affecting
you. Couldn't you just place each feed into a different sheet?

Charles Williams's site http://www.decisionmodels.com/memlimitsc.htm has
some information on Excel limits. I've also spoken to MS themselves and they
confirmed that while Excel can store much more data than I'm doing, once you
try to actually do anything with it you soon start hitting heap limits.

Also, DDE is old and cumbersome to work with.

Funnily enough, I don't have a problem with DDE. It does exactly what I need
to get data out of the manufacturing system and is very fast compared to
COM. If I could find some way to get a two-way conversation going between
Excel instances I'd use that.

Sometimes you don't have
a choice, but since you mentioned the data is coming from a
manufacturing process I suspect you may have an alternative. OPC (Ole
for Process Control) is supported by many PLC and SCADA vendors.
Unfortunately, I don't have any experience getting OPC data into Excel.
I just thought I'd throw that out there.

Thanks. But since I suspect very strongly that my problem is 'too much COM',
I think I'll pass!

Cheers

Robert

 

[Robert ap Rhys]

Hi John,

No, my suggestion is to wrap the DDE server in RTD and then hook that up to
one Excel instance - one workbook with 20 sheets perhaps.

Trouble is that I suspect that COM is the problem and not the solution. I
also think that 20*40000 rows of calculations is going to go way beyond the
heap limits of a single Excel instance.

The solution will not every work reliably.

Well, I know this ;-) That's why I posted here.

The overhead of interprocess
communication from one excel instance to another is very high. If you look
on MSDN you can see that this take a lot of processor cycles.

I'm aware of the MSDN articles. They indicate that when one Excel needs to
communicate with another, in effect an entire copy of the instance is hauled
through the marshalling layer. That's some overhead! However, since I'm only
looking at maybe 80 events per day in total, I had assumed that the number
of communications would be comparitively small. Looks like I was wrong, and
that a lot more stuff is going on in the background. Including internal
memory leaks, I suspect.

You should note that COM events can be dropped by Excel under heavy load, so
the data will not update properly in your "master" worksheet if it's fed by
COM events from the "slave" workbooks and the PC is under heavy load. This
is not well documented but it's a fact of COM.

Yep, I had this until I optimised the calculation. In effect there was no
catch up time between a DDE feed coming through, subsequent calculations,
and the next feed event. Excel tried to catch up by simply not doing some of
its queued-up tasks. Reducing the calculation time to practically
instantaneous meant that I never dropped a single event in my tests (running
20 independent Excels).

Your problem is that you are trying to push the envelope and link a
deprecated technology (DDE) using a technology on the way out (COM) in a way
that is never going to reliably work. I strongly suggest that you re-think
the way you are doing this.

I don't think DDE is a problem. I DO think COM is a problem. And I do think
I'm going to have to re-architect. I think I need to find a way of
conduction a conversation between independent Excel instances. Some kind of
noticeboard where messages can be posted and read by the instances should be
OK. I could do this with a very simple database and a timer in the Client
app. The server instances could read and write to the database on their
Calculate event.

If you want to discuss this, send me an email offline (go figure the email
address - it's pretty obvious) with a UK landline phone number and I will
explain - it's not well documented about COM missing events and it merits
more explanation, but I don't have time to type it all right now.

A very kind offer. But I don't want to take up too much of your time, and
I'd rather keep any communications where the entire community can benefit.

Many Thanks/Diolch yn Fawr

Robert