How much of a noticeable improvement is the SSD? Is it great enough to sacrifice the CPU speed and optical drive?
Yes it is worth it and then some. An SSD upgrade is the biggest general performance increase you will get on a modern PC. Let me put it this way our 8 core Mac Pros with 32 gigs of ram at work running normal HDD's feel slower in general use compared to my little 2011 MBA with 4 gigs of ram with its SSD.
Go the MBA unless you NEED large internal storage, if so get the MBP and chuck an SSD in the optibay.