1) Have you done a file IO stall analysis or wait stats analysis while the code is running to see why it is slow? sp_whoisactive has a differential parameter
2) You have two functions around columns in WHERE clauses. Those have to go.
3) Have you checked for mismatched column data types you are joining?
4) Check the query plan for huge mismatches between estimated and actual rows.
5) How up to date are the statistics in the tables involved?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service