I have been on memory foam for a little over 5 years now. I am a bit overweight at 230lbs.
My first was a queen size, decent quality generic brand, and is still in the same shape as the day I bought it.. I slept on it for about 4 years and it had the adjustable electric base. I gave it to my daughter. It is only 2 kinds of foam (about 2 inches of lighter stuff on top and the denser stuff in the rest) but was still comfortable. I only got rid of it because my girlfriend was staying over and a queen size was no longer big enough.
My current is a king size Tempurpedic Cloud Elite on regular box springs (adjustable base was out of my price range when I bought it). It was not cheap at all, but I wanted a good bed that wouldn't end up like most spring mattresses and only last 5-7 years. I did buy it from a close friend and he guaranteed me it would last 30+ years, and being a close friend he would be eating that mattress if it didn't. I have had some top end spring mattresses and after only a few years they end up with indentations where I sleep as the springs sag.
Complaints: I got it right after I started having back issues (facet joints, not muscular) and it is a little too soft, so when I sleep on my stomach (which is my default position), my lower back arches just a few degrees. As the back issue got worse, I found it harder to sleep in this position. I made the mistake of listening to my doctor when he said a single shot in my back would cure the problem permanently, so I kept the bed. By the time I realized I would be suffering chronic back issues the rest of my life, my window to return the bed had passed and while I could have gotten my friend to take it back, it was my own fault for keeping it so long before deciding I needed something firmer. Temperature can be an issue with these kinds of beds too. The one I have is not too bad with heat, but I do have to choose my comforter wisely or I can overheat pretty easily. If the bed is cold, it is firmer at first and then will get softer as it warms up. The last complaint might seem a little TMI so I will censor it - sex and other activities on memory foam is more difficult because you sink down a little more... You would be surprised how much this affects your view of this kind of bed. Regarding the same issue with sinking in, a disabled friend of mine just got a hybrid bed (memory foam top with spring core) and the sinking in issue made it harder to transfer from his wheelchair to bed, and he actually strained his shoulder transferring.
Pluses: Comfortable as hell and unless you have an arthritis issue where joints hurt when they don't move for many hours at a time, you will sleep extremely well. You will not move as much in this bed and you will get better sleep than any other mattress. This isn't some sales pitch, it does what it is supposed to do and you can get far deeper sleep. If you have broad shoulders and sleep on your side, it is WAY more comfortable than a spring bed.. really sinks down where your shoulder drives into the foam and doesn't have pressure points there. My girlfriend was never able to sleep on her stomach on regular beds because of her breasts getting too squished but she can sleep like a log on this bed in any position. In my experience (comparing high end memory foam to high end spring beds), memory foam will outlast springs by about 3x. I suppose if you don't weigh over 200 lbs this wouldn't be as much of a problem.
Because of my back issues, I looked at the sleep number but frankly I wasn't impressed. Even in the top end, as you fill the air bag it feels like someone is putting a knee in your back - the pressure is not evenly distributed as it gets more firm, and it all seems to focus on the center of your body.. you feel like you are lying on a log wrapped in memory foam, which you essentially are. If they had 4 or 5 different chambers each with their own air pressure and better support at the edge of the bed, I would spend the money on one (was looking at $9k for a split king with the power adjustable base). A good spring mattress with a really good top and firm springs is just as good, but then you are right up to the same dollar amounts as a good quality foam mattress, and in my experience they just don't last as long.
In effort to make my current bed work with my back situation, I recently placed a large bath towel folded up directly under the mattress (between the mattress and box spring) where my back arches. It isn't too thick (maybe 2 inches) and you can't see it when the bed is made and nobody is on it, but that little bit of extra firmness right there made all the difference in the world for me. I can sleep on my stomach for twice as long as before (5 or 6 hours before I have to move) this way and it has been really nice. Second, in sleeping on my side, I use a pillow between my legs to keep them lined up right. This helps a lot with comfort and although the pillow is annoying when you have to move around, it can make a massive difference in how you feel in the morning. I never needed to do anything like this a few years ago when my body was still healthy, and I slept so well on memory foam, but as I get older I found I needed more firmness sometimes, which makes the sleep number idea appealing, they just lack in execution.
Bottom line: If you are looking for a cheap mattress, stick with what you know. A comfortable $300-500 spring mattress isn't going to have anything over a $300-500 foam slab and especially if only used a little will probably last just as long. But if you are investing in your mattress (i.e. investing in your sleep), it sure sucks to have to throw a $2,000 spring mattress away after 5 years. If I decide to make a move to a firmer mattress, I will buy a split king and get the single version of what I have for my girlfriend and go with a firmer version for me. She is 100% sold on it. I can't put a price on what a good night's sleep means to me - for me OR her.. I do have a memory foam mattress in my spare room for guests and they always comment on how nice it is. It's similar to my first foam bed and I got it for free when my mom upgraded to a newer top end mattress. It is in the same shape as the day she bought it 7 years ago.