When I first heard "Try to Remember" (from The Fantasticks, a show in which I recently performed), it moved me - I got a lump in my throat and teared up when I really listened to the lyrics the first few times. Years later, I've rehearsed the show for months, performed it, and sung the song more times than I can count. I still like the song, but after so much exposure, its emotional weight is mostly an abstraction to me. In a couple of weeks, I'm going to start rehearsals for a production of Into The Woods - I've been watching a filmed production of the show, and its effect on me at the moment is very similar to The Fantasticks' in the beginning. When the run is ending in September, I'm sure I'll be looking back fondly on the experience, but I doubt I'll still be getting choked up by the closing number.
I suspect this phenomenon is common to all performers, and perhaps all artists. When writing songs, I have similar experiences. During the initial writing process, I know very clearly when I've hit upon a good idea (according to my own tastes, of course), but once I spend some time developing it into a song, with a bass line, drums, structure, etc., it no longer appeals to me like it did at first.
Perhaps this psychological application of the law of diminishing returns is fundamental to what makes great art hard to create. Most art forms take time and repetition to "polish" to finality, and this numbs the creator's ability to emotionally connect to their own creation. The upside is that time away seems to counteract the numbing effect (eventually leading to nostalgia, I suppose). It takes me months and months (sometimes over a year) to finish a song, because I like to let it sit long enough to forget, then come back to it with fresh ears. Only this process gives me sufficient perspective to really evaluate what works and what doesn't.