I would say it does work. It makes the felt recoil on the light USP, closer to what you would experience with a heavy SIG 226 or 229, and definitely less than what you get on the (also light) GLOCK. The SIG is the softer shooting pistol, but HK has tried to have the best of both worlds, lightweight and low felt recoil, and their solution is really a success I think. The benefit of a lightweight gun doesn't really come into play unless you carry the gun for a living or CCW, though.
The recoil reduction system doesn't make a huge difference in felt recoil, but it does have some effect. As I say you can achieve lower felt recoil by just making the gun 8 or 10 ounces heavier. Recoil reduction probably has more to do with extending the service life of the pistol than it does with reducing felt recoil for the shooter and extending the target shooting session at the range, but it helps there too.
USP is a good gun. Try and get a model with a match trigger if you can - if you are planning to do any target oriented shooting. I found the trigger to be more of an issue with this gun than recoil or anything else. The USP trigger is harder to get used to than many other DA/SA guns. Recoil is not much of an issue with these guns. I have a 9mm USP, but I've shot .40s and .45s too. They aren't hard recoiling guns.