How to optimize a slow query?
You have three potential areas you could address: LIKE operators, subqueries, and OR operators.

Regarding LIKE operators:

See https://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx


  
  If your filter criteria uses equals = and the field is indexed, then
  most likely it will use an INDEX/CLUSTERED INDEX SEEK
  If your filter criteria uses LIKE, with no wildcards (like if you had
  a parameter in a web report that COULD have a % but you instead use
  the full string), it is about as likely as #1 to use the index. The
  increased cost is almost nothing.
  If your filter criteria uses LIKE, but with a wildcard at the
  beginning (as in Name0 LIKE '%UTER') it's much less likely to use the
  index, but it still may at least perform an INDEX SCAN on a full or
  partial range of the index.
  HOWEVER, if your filter criteria uses LIKE, but starts with a STRING
  FIRST and has wildcards somewhere AFTER that (as in Name0 LIKE
  'COMP%ER'), then SQL may just use an INDEX SEEK to quickly find rows
  that have the same first starting characters, and then look through
  those rows for an exact match.
  


Regarding Subqueries

I would recommend inserting all of your subquery data into a temporary table first. Then, join the temporary table to your main select query.

See https://stackoverflow.com/questions/921931/why-is-inserting-into-and-joining-temp-tables-faster

Regarding OR operators:

Regarding performance, a question you should ask yourself is if you should use the OR operator or UNION operator as they both can have the same result.

See http://www.sql-server-performance.com/2011/union-or-sql-server-queries/


  
  Selecting all columns: OR is faster than UNION
  Non-Clustered and Clustered Index Columns only: no difference
  Selecting all columns for different fields: no difference
  Selecting Clustered index columns for different fields: UNION is faster than OR