How can an air-to-air intercooler be more efficient than a water based intercooler?
There is an overwhelming quantity of ambient air available to cool an air-to-air core relative to the charge air thru the inside of the intercooler (The iced down water intercooler is the only exception to this argument.). At just 60 mph, with a 300 bhp engine at full tilt, the ambient air available to cool the intercooler is about ten times the amount of charge air needed to make the 300 hp. Whereas the water intercooler largely stores the heat in the water until off throttle allows a reverse exchange. Some heat is expelled from a front water cooler, but the temperature difference between the water and ambient air is not large enough to drive out much heat. Another way to view the situation is that ultimately the heat removed from the air charge must go into the atmosphere regardless of whether it's from an air intercooler or a water based intercooler. The problem with the water intercooler is that the heat has more barriers to cross to reach the atmosphere than the air intercooler. Like it or not, each barrier represents a resistance to the transfer of heat. The net result; more barriers, less heat transfer.
What are the relative merits of an air or water-cooled intercooler and which would suit my purposes best?
This depends on the circumstances. These circumstances are; street use, drag racing, or endurance racing (more than two minutes).
Street use: The air-to-air intercooler will prove superior in efficiency when sized properly.
Drag racing: The short spurt of power allows the iced water to cool the charge air to below ambient temperature.
Endurance racing: The air-to-air intercooler is clearly superior due to the shorter route of getting the heat out of the air charge and into the atmosphere. Endurance racing would preclude the use of ice water, thus negating the singular advantage of the water intercooler. Further, the air-to-air intercooler is (virtually, see comments below) maintenance free.