For most people, you can probably pull a 3G/4G connection of a few megs. Alternatively, if you're somewhere with WiFi (such as at home), you can probably pull a few more megs. But the two are mutually exclusive - that is, if you're using one, you can't be using the other. Or rather, they were - because now, thanks to Super Download, you can run both simultaneously.
Obviously, the app could provide you with some pretty impressive speeds, but it's still in the early beta stages. As such, even the paid version costs the Play Store minimum of $0.99 - quite the steal as it is, and with the price likely to go up as the app matures, it seems like a good time to snag it.
It should go without saying that you need to be careful with your data caps (if they apply), and the app may not be of much use if your data speeds are poor. Also, running both radios at once probably burns right through your battery, so we wouldn't recommend straying far from a power source. Finally, the developer warns that some device/ROM combinations may not be supported.
The free (lite) version only supports file sizes of up to 50MB.