The hardware implementation of this concept
is somewhat old. System RAM partitions themselves are older, but not used for game performance reasons until recently.
If you have a very fast main storage unit at your disposal, the increase on performance depends on the memory management implementation in each game, which in short means: "In most games, you'll only see faster load times". This is because most games only access the main storage unit once in a while (i.e. loading between levels).
The exception would be games that use streaming extensively, this is, they continuously load (extract data from the main storage unit) while actually running, in order to, for example, render vast or very detailed areas without resorting to loading screens and without being limited to whatever amount of system memory you've got. Soul Reaver, the recent games from The Elder Scrolls series and Rage are examples of games that use this technique heavily.
And then when you already have what you need in system memory, the game stops loading from main storage. Until it does it again, any performance increase resulting from having a very fast main storage unit disappears. If you have lots of system memory available (as in a decent gaming PC), having a slow main storage unit usually doesn't hinder performance too much, since memory management facilities are built around assuming you have a regular, old-ass HDD. So the game loads at once everything it can in system memory, with system memory storage space to spare in order to keep loading slowly "behind curtains". For example: loading stuff that's very far away from the player's point of view.
Funnily, the best case I've seen of performance increases because of faster main storage units is on the PS3. Its versions of Rage and Skyrim (which again, feature streaming a lot, almost all the time) benefit greatly from installing an SSD on the PS3. This is mostly because the PS3 has a really tiny amount of RAM (even less available than the Xbox 360, and split in two 256MB chunks, video and system RAM) and it has to be refreshed continuously with data from both main storage units (the optical disc and the HDD). Having faster data transfers because of using solid state memory helps a lot. You can read about it
here. As you can see, there's little difference of performance with games that don't feature heavy streaming.
About loading times: they can be very fast with RAM drives (or to a lesser extent, SSDs) but they're not instantaneous. Here you've got a video of the PC version of Skyrim loading from a regular, kinda slow hard disk drive and then from a RAM partition:
http://www.youtube.com/watch?v=zMYhUm0n1Gw55 seconds on a 5400 RPM HDD vs 8 seconds on a 6GB RAM partition. Pretty impressive, huh? But there's still some loading time. The reason for this is simple: Even if with a RAM partition the main storage unit and system memory are physically the same device, they don't share the same set of memory blocks. So you have to move data from one to another via data paths which, I presume, are built into the motherboard and memory controller. Kind of inefficient, but a step forwards, nonetheless. And of course you still have to load data into video RAM, anyway.
Maybe in the near future we will see true fusion between storage, system and video memory in one big, fast pool of storage blocks connected to the processing units via very fast data paths. Then we would finally kiss loading screens goodbye. That is essentially how old computers worked. Funny, isn't it?