It ain't like it used to be. No more MHz comparisons.
I went from a 1.4GHz Tualatin to a 1.86GHz Core2Duo.
In simple single-threaded apps, like LAME MP3 encoding, performance is only a little better with Core2Duo. In fact I get even better performance from the 3.2GHz P4 Northwood in my work PC. In this case using a DOS app, clock speed is a strong indicator of performance.
But in tough multi-threaded applications, like AutoGK video encoding, performance is leagues better with my Core2Duo; I'd say between 4-5x faster than my 1.4GHz Tualatin.
I would not buy a Netburst CPU (e.g. Pentium D) under any circumstances, however, because of power and heat issues.