Depends on what you do with it.
With CPU intensive applications or games you'll notice a big, big difference. With GPU intesive games and applications the difference will be much less, though I imagine you'll probably be moving to PCIe and a new graphics card too so that'll give you a noticeable boost (obviously depending on which video card you get but I imagine it will be substantially better than what you run now).
In short though - Yes, there will be a significant difference.
I recently came into possession of an opty 146, it's at 2.9GHz and it's rocking compared to what's in my sig.