As said it depends n the CPU and what speed you are aiming for.
If your CPU has a high multiplier you might be at the limit with a FSB of 400Mhz, in that case 800Mhz DDR2 is good enough, if your CPU has a lower multiplier you might need a FSB of 500Mhz+ and in that case 1066Mhz DDR2 helps.
I went for bog standard 800Mhz DDR2 as my E7200 has a 9.5x multiplier so 9.5*400Mhz gives me 3.8Ghz