Simply installing the camshafts tends to make only a small difference.
From there it all depends on the skill of the tuner and the strategy they use to tune.
Some will only adjust the fuel and timing. Some will go the whole way and alter both the inlet and exhaust vanos profiles.
On the E46 M3 for example, fitting Schrick cams gave a maximum of 5bhp without tuning. It was hardly felt on the road. Many tuners did not know how to adjust the vanos profiles and therefore only every achieved small gains. Some knew how to control the ECU very well and gained anything upto 35hp which made a very significant difference on the road.
As for losing low end torque goes, this can be compensated to some degree by changing the overlap of the cams (by altering vanos profile) at low rpms to retain torque and then increasing the overlap even further at higher rpms to realise more power at high rpm's.
The tuning is the key behind this and it's a task that requires some dedication.
Many people who know their cars notice 10hp from a flash tune and they really notice the increase in power/torque when fitting exhausts like the Akrapovic or RPi.
Once something like these cams are fitted and the tuning is done correctly you'll notice this upgrade. You think this engine gets angry over 5000rpm now? It'll be a completely different story once something like this is done.
The cost is such that most people will go FI but for those who want to stay NA, it's a very good option. Tuning the first one will be interesting!