It depends on the game. A game like Super Smash Brothers Melee, that can create and save new data constantly, should be turned off at the title screen simply to make sure all of your data gets saved. But if you're in, say, the Trophy lottery and don't get a trophy you like, you could quickly reset the 'cube and try again. It doesn't hurt the game or the hardware at all.
Games like Zelda and ED and mario sunshine, which all save your game at a set place, are fine to reset/power off whenever.
Really, any game is fine to power off whenever you want. You'll simply lose the data since the last save point. Some games save constantly, others save at set points.