I tried quite a few distros on older, resource limited hardware like you describe and found several choices that worked quite well for my equipment, but with some limitations you might expect on a older computer. You will want to use a light window manager like Fluxbox, IceWM, or Xfce (or such) to minimize load rather than something like Gnome or KDE, which add features but too much overhead. Often issues can arise with older video cards, and choosing the VESA option during boot can help if you have difficulties.
The first 2 and last options work on almost anything (have never failed for me).
1)Damn Small Linux - basic, lightweight function initially, but you can add software as needed, ~meant to be run from CD/flash without installing but can install/convert to Debain
2) Puppy - more included with base install, expandable, also ~meant to be run from CD/flash without installing but can install and/or convert to Slackware 12, nicely functional out-of-the-box for many issues like web media,
3) Vector Linux - nice Xfce desktop, Slackware based system designed for older hardware, ~full desktop feel and
options, normally installed to HD
4) Xubuntu - xfce environment, wonderful support from community, slightly slower than Vector but safer for newbie,
normally installed to HD
5) Knoppix - great hardware support, ~meant to be run as live CD, full KDE envirnment so a bit heavy but can actually "work" if you want all the features
There are many others, but I have first-hand experience with the above.
Your experience with Linux on a system like you describe should be considered in comparison to the performance you'd expect from an OS that would normally run on it, like Win98. Linux will provide much better performance and with up-to-date software. On a new system you can expect performance at least comparable to Windows (generally better). Try a live CD on a recent system (which won't touch your current OS), though running from CD will not match the performance of an installed system - at least you can get an impression of function/features. Good luck!