You can put up to a 1gHz G4 Zif into that thing, which is pretty damn fast. But as was mentioned, if you have a 400mHz G3 and you put in a 400mHz G4, you'll have the same response in non-altivec applications, though OSX will run a bit smoother.
Also keep in mind that if, for example, you have a 300mHz b/w and you buy a 550mHz G4 Zif for cheap, though it won't be fast by most standards it will be almost twice as fast as what you have now, so you'll definitely notice a bump.
However, to do a lot of the functions of even a sawtooth, you need to:
upgrade the HD (G3 stock drives run at 5400rpm)
upgrade the optical drive (if you want dvd playback)
upgrade the graphics card (the one in there won't handle dvd video; and remember, PCI only)
deal with the 1gig max RAM (but max it out!)
If it's already pretty pimped out, then you'll get a damn good response from the upgrade of the processor. My suggestion would be to upgrade all of them if you can do it for cheap, but chances are you can't, and you should only do it if you are completely in love with your case. For Tiger, I would say at the VERY LEAST you should max your RAM and get a better graphics card (though it will run on the card you have, just not very well). The plus is that your bus speed is the same as the early G4s (100mHz), so you're going to get good performance out of the upgrade.
I would pick up a G4 cheap and turn your G3 into a server, or find another use for it (mine is a dedicated audio recording machine).