Rob you have probably found all that you need to know on this topic but I guess I can help out some here:
General rule of thumb:
Below 3.2 GHz stick with the Northwood C version of the core. I have had a 2.8C for over a year and its great. Be careful if you do buy a 2.8 core because there are the B, C, and E versions (Northwood 533 FSB, Northwood 800 FSB, Prescott) of the core.
Above 3.2 GHz go with the Prescott.
Now for some more in depth info about Prescott. Its true that it is running hot as hell. Some are consuming 100 Watts of power which is absurd. It needs a massive heat sink and fan combo and the only place for that heat to go is to your office/room. Nothing like a space heater huh.
Even though the Prescott has a larger L2 cache it also has what is called a longer pipeline. For one instruction to be processed on a northwood core it must be analyzed by 20 steps where as an instruction on the prescott core must be analyzed by 31 steps. A similar thing happened several years ago in the move from the Pentium III (10 steps) to the Pentium 4 (Williamette core 20 steps). A 1GHz PIII could outperform a 1.5 GHz P4 because of this. But these extra steps (in addition to smaller transistors) allow you to increase the clock speed of your processor. It was increased so that Prescott could acheive an eventual top speed of 4-5 GHz. Northwood will top out at about 3.6-3.8 GHz.
So below the 3.2 GHz mark the long pipeline proves to be too much overhead for it but above that mark the large cache helps reduce the effects of the extended pipeline. This is quite a bit simplified but its the general idea.
If you want a LOT more info then i suggest you read this article:
http://www.anandtech.com/cpuchipsets/showdoc.aspx?i=1956As for me... well I just started the process of building a second machine because I want a Linux Box for e-mail, web, and coding. I'll be using an Athlon XP-Mobile processor. It uses a lot less energy which translates to less heat. It will work fine in a desktop but is designed for laptops.