* DON'T USE USER DEFINED FUNCTIONS IN ANY PREDICATE OF ANY TYPE EVER, THIS IS DEATH.
* If you want things to go fast, consider that your query needs to be able to be converted into a "search argument" to use any of those precious indexes you made, so:
LIKE 'hdfhafsdh%' -- This works!
LIKE '%fafads%' -- Scanning all the things, so sad.
In the same vein, any function you use on your values in the tables mean that there are often no indexes you can use to help your operation (with a few technical exceptions around specific optimizations SQL Server has around datetime <-> date calculations )
Download SQL Sentry Plan Explorer - it just became free and with it you can shred apart your execution plans with ease and keep histories of the work you have done on a query, or watch a plan change over time as your tune it with ease.