Not as much as you would think.
First off, all modern Hi Def TV's have built in upscalers. They will take whatever is input and convert it (through their own internal upscalers) into their native fomat, be it 720 or 1080.
Since Composite and S-Video are only 480, it's doing what it can with it already.
If, by some slight chance, your new external upscaler is better than the one in the TV, then you might realize some slight improvement but remember, you can't make a silk purse out of a sow's ear.
Now, assuming you have a hi-def TV (and that's about the only ones I can think of that have HDMI inputs), if you want a better picture, you're gonna have to spring for a Hi-Def cable box (and possibly a service upgrade) and these come with either component or HDMI, or possibly both. They are the only two formats that pass Hi-Def signals, and they both work equally well. But remember. you still need a hi-def signal to start with.
And, conversly, if you don't have a hi-def TV set, why bother?