Well - I don't know if you are male or female - that probably makes a big difference. I can only speak from the female perspective....
I had one many years ago....I don't know how (or if) things have changed. I had to drink a TON of water, and NOT PEE...that was the worst part. i swear, I thought I was just going to explode, or pee like a fire hose all over the exam table, LOL...but remember they do this stuff all day every day, so even though it seems really weird to you, this is their job.
They did give me some pain killers and numbing gel in the "nether regions", but it was still pretty uncomfortable. They were very patient, and kind, and to some extent they distracted me by turning the screen so I could see what they were seeing - it was weird and interesting, and did keep my mind off the discomfort somewhat.
The only real PAIN I had was afterward, when the numbing stuff wore off and then it felt like a UTI...burned a lot. But that was just irritation from the scope. If I recall correctly, they had me on some preventative antibiotics for a while, just because the urinary tract does get pretty irritated. But that all went away in a couple of days.
It' definitely not something I would care to repeat, but it's not horrible. Worse than a pap smear, worse than a mammogram, but not as bad as a root canal.
Hope that helps.