Usually air is perfectly sufficient, even with performance parts that run hot.
Obviously, if I had tons of money to blow, water would be ultimate... but there are so many great air cooling solutions out. Many CPU and GPU overclocking limits can be reached easily on air... so the need for water isn't there.
For example: Say an i7's limit is 4.2GHz, usually you could reach this with a top notch air cooler as well, so water cooling would be overkill in terms of overclocking headroom.
Because water runs cool and consistent though, you can often squeeze a bit more out of your system. It would actually increase efficiency slightly too. Not to mention water cooling solutions offer the sound of silence... which is always welcome.
Price/Performance isn't worth it to me right now though.