Component will, in general, look better than S-Video. However, the Sony Trinitrons (at least the models I've played with) seem to have pretty good handling of S-Video inputs. I've seen them look almost as good as RGB on lower bandwidth signals when adjusted properly.
S-Video out from PCs, however, is subject to lots of pixel mashing. Basically, the card does "whatever is necessary" to make the signal compatible with a TV. This tends to result in pixel poop. It's not a failure of the S-Video connection; it's a failure of the TV out on the card.
Those DVI to Component adapters really only seem to work on certain model ATi graphics devices (the old 9000 series). I've never heard of anybody making them work with an nVidia device.
The "S-Video to component" cable that you have is probably not actually such a thing. For a while (7000/8000 series, mostly), nVidia had "HDTV out" on their cards via larger mini-DIN connector. It looks a lot like an S-Video connector (same connector family), but it has more pins and is often mechanically incompatible with S-Video connectors (fun, huh?). The good news is that this does give you a real YPbPr component output that you can use, though it's still often subject to some TV out scalers and IIRC some models won't do native 480i TV timings. Should still look better than your S-Video in any case.