I have not actually done such an upgrade but looking at the specs I don't see how it could not improve performance. The amount of improvement, however, would depend on the caches your current CPU has and the caches you get in the Core 2 Duo. If you have a low cache CPU now and go to the top of the line cache in the newer CPU, you'll get the best improvement. If you have a top of the line Pentium D and go to the lower cache version of the Core 2, you'll see less improvement.
Of course, that all depends on what you are actually running on the machine and whether or not the CPU creates a bottleneck with whatever other hardware you might be running. If it does create a bottleneck you probably won't notice any improvement.