The redirects should be left in place until your server logs show that no requests have been received for the old URLs, and until you decide that you don't need the PageRank from the old incoming links any more.
On my own sites, the answer is, "forever."
For this reason, it's a very good idea to think about coming up with a 'friendly URL' architecture that will be portable in the future, no matter what CMS your site is based on. This will prevent ever having to change your URLs again. This can be a huge challenge, but is usually well worth the effort, at least for the top dozen or few-dozen pages of your site.
As for load, the initial load may be noticeable, but realize two things:
1) After the search engines atart updating their links, they won't be causing the redirects to be invoked any more.
Think about the redirect code in the same way as you think about PHP code; You have a few hundred lines of redirect code, but how many thousand lines of PHP code in that CMS? Therefore, the PHP execution time and 'load' will likely swamp the redirect code execution time, and you may not even notice it. However, this fact should not make you feel that any and all optimizations shouldn't be made to the redirect code -- Take every opportunity to make it efficient from the start, and then you just won't have to worry about it.