sweet-spot right now is the E6600 @ 2.4, nearly every one of them in existence can be o/c'd out of the box to 3ghz and run on stock voltage and stock cooling and still maintain reasonable cooling levels. Add in a decent aftermarket cooler & good case airflow and 3.4 to 3.6 become real possibilities for a lot of chips.
E6600 is considered the sweet-spot because it has the 4M cache, but is still pretty easy to o/c (if that's your thing, and with the 2.4 going to 3 so easily, there's virtually no reason not to.
) The "lesser" chips will often o/c better, but only have 2M cache, so you loose some performance there that you won't get back unless you are crazy powerful o/c'd. And with the E6600 costing roughly $300, you could afford to buy 3 of them and o/c them to the breaking point for the price of 1 of the "extreme" chips (dual or quad) and still have some cash left. And more importantly, get roughly the same performance.
And nearly ANY of the conroe chips will run circles around a P4 of a PD chip.
Examples from my 3dmark06 scores:
P4 @ 3.4 w/ 7900gtx: 5107
PD @ 2.8 w/ 7950GX2: 5837
E6600 @ 2.4 w/ 7900GTX: 6116
E6600 @ 3.0 w/ 7900GTX: 6374
Those scores aren't the streamlined, bare boot scores, that's loading the normal startup stuff, then running the benchmark. Running barebones benchmarks to simulate everyday use is stupid.
Here's a nice example comparison for you:
My system: http://service.futuremark.com/compare?3dm06=1290812
Opposing P-D system: http://service.futuremark.com/compare?3dm06=1112933
Side by Side: http://service.futuremark.com/orb/multicompare_view.jsp?p0=1112933&n0=Best+P-D&p1=1290812&n1=My+Core+2&t=Core+2+vs+Pentium+D&k=14&s=1&l=1&c=-529936511
Mine is the core 2 system. The P-D system is clocked a full 1ghz more than me, and his video card is clocked up as well compared to mine. I still beat him for overall score and BURY him on CPU score 2634 to his 1981, and remember, he's a full ghz faster than my 3ghz. And the score I tested against is almost certainly a barebones run, his was an entry in the dell performance shootout contest, so he should have been going for a high a score as he could possibly get.
Wow, this got a little longer than I thought it would. Sorry about that.
Pretend I just said "Get a core 2 duo e6600, it'll do you right!"