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

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



--
www.alignment-systems.com


"Robert ap Rhys" wrote:

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" wrote in message
...

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

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 ;-)

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




--
www.alignment-systems.com


"Robert ap Rhys" wrote:


"John.Greenan" wrote in message
...

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" wrote in message
...

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 ;-)

--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?

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.


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

Robert ap Rhys wrote:
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

Comments in with the mail...


--
www.alignment-systems.com


"Robert ap Rhys" wrote:


"John.Greenan" wrote in message
...

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 ;-)

--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?


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.

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.


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.