To sort of sum up:
Without processor overclocking: Going from slower to faster ram can give you a small but noticeable performance boost, but it is dependent on the application. Many applications will see no improvement whatsoever. You will only see these performance boosts if you manually go into your bios and change the memory ratio to take advantage of the higher speed. This is not overclocking the ram, as you are just setting it to run at its rated speed.
However, if you were to consider overclocking, considerable performance gains can be achieved with even a mild overclock. Getting ram with a higher rated frequency will give you a larger window for overclocking. DDR2-800 will allow you to run a 400MHz Front side bus, without pushing the memory beyond the rated specs. But then again DDR2-667 memory will allow a 333Mhz FSB which is a considerable improvement over stock. On a E6600, stock is 2.4GHz, OCing to a 333FSB would be 3.0GHz, and 400FSB would be 3.6 GHz. 3.0GHz would be considered a conservative overclock.