Since you seem to be happy to leave CS4 behind for CS5, I won't bother trying to help you to figure out why your experience was so bad.
Let us know how things work out with After Effects CS5, and let us know if we can help.
Of course, I assume that you've seen this:
How big are your project files, Bogie? I find that with some very large project files, when MP is enabled AE (CS3 and CS4) seems to hang for a very long time on the last frame of each render. Disable MP, and AE renders that last frame like normal. Is this what you saw in your tests?
As an example, I have one project in which I'm rendering ~150 network menus at a time. These menus render in 20-30 seconds each on a single processor. But when I enable multiprocessing, they take something like 4 minutes each, the vast bulk of which is spent on the last frame. If I reduce the project to only contain a dozen of these menus, MP performance is comparable to non-MP performance. However, I have other projects where I'm rendering ~100 keys at a time that definitely do benefit from MP, so I simply have to remember which ones do and which don't, and turn MP on or off accordingly.
I really wish AE were smarter about this stuff, e.g. actually have the program check to see if it is putting more time into overhead than into actual rendering, and disable MP or, barring that, make it possible to attach MP settings to the project, rather than having one global preference for the application (buried in the preferences, no less). CS5 could be a different story, but my client won't be upgrading for months, so I simply don't know.
Cobb, I had not meant to get a thread going, the option to use MP is useless on my systems, I just wanted to kvetch But…
I had a variety of projects in a many sizes and codecs from a line of text typing on in DV ising ProRes4444 to 20-1080 layers of heavy CC effects, lights, and moving 3D layers using Animation.
All renders start with a tangible hesitation, run for about 20 frames and then turn into treakle on a winter day or, more colorfully, snot on a cold doorknob--they go totally glacial.
None of the user-adjusteable settings affected the time required for the rendering with MP active except to turn it off. REducing or increasing RAM, procs, system overhead--none of it mattered.