1) GUIDS
2) probably unnecessary SORT/DISTINCT for the UNION
3) Most importantly what is the hardware configuration?
4) Have you done file IO stall and wait stats analysis during these hours-long runs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service