You are absolutely right Pete. The memory muscle is an excellent analogy and it gets easier when you get used to recognising patterns in the music.Musical memory is like a muscle - use it and it develops: you get faster at learning new tunes and you can remember more and more. Just keep at it and it will take off. You will be amazed at what you and your fingers can remember.
As Pete C said above, practice definitely helps. I start by trying to get the sense of the phrasing: if there are lyrics these can be very helpful to help you keep your place in the melody. Wrestle with the tricky bits, and when those seem smooth, try without the music. Then next day, when you don't even have the music anywhere near, try again. That usually sends me scurrying to the music to remember the forgotten bits. The memory is half in the fingers half in the brain, and (for me anyway) the memory isn't a visual memory of the score. Much more a movement/muscle memory. I would probably struggle to write down even the tunes I know best.
Anyway the other really important thing for me is that when I have it in memory, I can play it so much better, so definitely worth doing.
Get it into your HEARING brain rather than finger memory. Get your fingers to play what you hear. Sounds simple, but this takes years to master, but once mastered you can play anything you can hear within your physical limitations.
I think Pete and Rikki have got it right about the exercise, but you need to hear and play without translating trough several layers in order to play fluently. If you hear something (like happy birthday), then have to analyse it by saying to yourself OK all is fine until I get to those big intervals, what are they? Oh yes, when I play in my key of C the first jump is an octave above the G (start note) then the next big jump is an F (the forth). Ok they are playing in Concert E that means that (since I'm playing a tenor in Bb) I'm in F so when I get to that part I'm playing a C octave jump and then the forth which is Bb. Yea everybody does that! Right!
You hear it in your head and you play it. Analysis is great for developing understanding and OK after the fact or if you need to figure something out well ahead of time. A pro just plays it, no analysis, no picture of notes on a page, no transposition as a mechanical intervention. You hear it, you play it. If you try to analyse everything on the fly you’re dead in your tracks. If you are trying to make this somehow a fast process when forever translating it’s just as awkward as trying to translate any other language through a dictionary, hand held translator, or even another person who translates. There is a time delay and something always gets lost in the translation. Why not be fluent in the language in the first place? When you work with your ear and your instrument to make them one, then you can play what you hear. Don’t know about any of you, but I think I can sing happy birthday to you in all twelve without thinking about transposition or structure. Can be just as easy to play IF you train yourself instead of trying to forever translate through an intermediary.