P4s were bottlenecking cards like the X1900 and 7800s!! Lol, I had a X1950 with a 3.2Ghz P4 then put it with a 1.8Ghz C2D and 3DMark06 score went up by 1/3!
Anyway, you are right to ditch the P4, and then you have to decide what to go for, which I suggest would depend on what you've got at the moment.
As you have a socket 775 motherboard you may just be able to drop a newer C2D or C2Q chip in, firstly check the CPU support list for your mobo model on the manufacturer's website. (If you can put a newer chip in then a BIOS update will proabably be necessary too - always do this in DOS)
If you cannot support any high end C2Ds or C2Qs then you will need to buy a new motherboard too, then this poses the question of whether to go i7, PII or C2D/Q.
I'm sure lots of people will have different opinions on this but my take is if your current mobo will take a E8400 for example and you have 2-4Gb of good DDR2 this will be the cheapest option, a Q9550 would be more expensive but would future proof a bit more and be better for heavy multitasking. If you nee a new mobo and if you have some good DDR2, 4Gb of 800-1066 say, then a newer 775 mobo like a good P45 or X38/X48 and a Q9550 would be a good buy, would give you the option of crossfire and would easily last you for the current i7 generation, or you could go for a similar PII setup. Or if you have the money now you could go i7 in the hope that that would last a bit further - this will of course be a bit more expensive!