Tamsulosin relaxes the smooth muscles of the prostate and internal urinary sphincter, helping you pee faster.
Peeing faster makes it more likely you will empty your bladder when you pee, which will reduce the frequency of having to pee. (You pee for a certain length of time, not until your bladder is empty. If you have a slow flow rate, you will finish peeing before your bladder is empty, so it will fill up again faster.) Failing to empty your bladder will increase risk/frequency of UTIs. Peeing slowly can also cause bladder spasms, which lead to urgency and the need to pee before your bladder fills up.
There's no problem taking Tamsulosin with prostate cancer. However, if the cancer is restricting the flow in the urethra (which is not very common as prostate cancer doesn't tend to start in the middle of the prostate), then Tamsulosin won't help with that.
Tamsulosin is a fast acting drug for improving flow, so you'll know pretty instantly if it's going to work. Equally, it wears off quickly and doesn't usually last 24h. Tamsulosin also has a long-term effect of slightly reducing prostate size, which might also be of benefit for BPH, although there are other drugs more aimed at doing this, Finasteride and Dutasteride (5 𝞪-reductase inhibitors, or 5ARI's). Note that 5ARI's will halve your PSA, so you need to double PSA test results to assess risk/progression of prostate cancer if you're on these (not for Tamsulosin though).
Tamsulosin is not normally of any use if you've had a prostatectomy, as all the tissues which respond to it will have been removed.