For self defense, you need 12" of penetration after the heaviest clothing that you will encounter and expansion to ~0.5" diameter . Beyond that, it is almost 100% shot placement. For target shooting, whatever load allows you to shoot more rounds. For competition with momentum based limits, a heavier bullet traveling slower will have less energy (0.5m*V^2) for a given momentum (M*V), thus will have less felt recoil. For maximum penetration (which is accounted for by the 12" penetration requirement above, but if you are worried about a bear or want to hunt, it may matter), you want a mix of energy and momentum, with the equation being something like M*V^1.4 (i.e. not quite the energy equation, not quite the momentum equation, with the exponent being 1.5 for soft tissue and 1.2 for hard stuff like bone, so I use 1.4).
I shoot 115 grain HAP 9mm at 1200 FPS for target and competition because it's cheap, the recoil vs a heavier bullet is mostly a was with my very aggressive muzzle brake, I don't want to be exposed to lead like the 135 grain LRNs that folks commonly use, and I am not good enough for it to affect my scores. I carry a 115 grain HDY XTP in .380 that does ~1000 FPS from a P232, or a 200 grain HDY XTP .40 that does 930 FPS from a P239. I use 147 grain HDY XTP 357 Sig that do 1370 FPS from a 5.7" barrel P226X5. I worked up a 300 grain HDY XTP .44 Mag that does 1300 FPS from a 9.5" Ruger Super Redhawk, but the recoil is too much for me (the Hogue grips wtih a gel pad helped a lot, and I may try it again with those on the gun), and it's unpleasant to shoot. Of all of that, the 115 grain 9mm load is far and away the most practical, and for actual use could replace everything else if it had to. It's like the .308: not ideal for anything, but passably good in nearly any role.
YMMV, and these are just my opinions.