If you have lots of software/games on CD's which require windows, then it would be better you run them on windows itself, atleast untill you find their equivalents in linux. You can run many windows games in linux using cedega, but results may not be perfect.
I would recommend Ubuntu or Mandrake as distributions.
Regarding resource usage, the results vary by a huge amount. Taken by themselves, linux applications are generally quite efficient and not resource hungry, but many distributions use desktop environments like Gnome or KDE, which being very feature packed, will run as slow as/slower than windows XP. But the good thing is that you can switch to lighter environments to run your applications in, which could run faster than Windows 95. So basically a linux desktop can run as slow/fast as you want it to.
Switching to these may require some manual configuration, and if speed is important, you can consider a distribution like Vector Linux.