This site keeps an updated list of software that works with Rosetta and how they work. For example some applications do work, but with serious performance issues. If you have to use them professionaly (or often) you should consider not to go with Intel Macs and stay with old Macs.
With some other applications however Intel performances are quite impressive.
