Sql Query Optimization

See more:

SQL-server-2000

SQL-Server

Good Morning everyone,

I've been working on a query that connects 2 servers for the data I want. the issue is the way I'm currently running the query, it takes so long my website times out. I've been browsing a few sites and have had some that seemed promising but failed to deliver the results I needed. I will attach the query below.

select distinct isnull(t2.position_id,'') + '' + t2.last_name as [certifier name], t1.order_number as [order number]
from shop.dbo.orders t1, shop.dbo.managers t2
where t2.manager_ssn in (select t1.manager_ssn_id from shop.dbo.orders) and
t1.order_number collate database_default not in (select substring(t3.leafname,0,6) from server2.wsscontent.dbo.alldocs t3 where t3.dirname like 'intranet/supporting documents/%') and
t1.manager_ssn_id = ''
order by t1.order_number

Just a little more information on this query, the "collate database_default" was to solve a problem with the collation between the two servers. this query runs over 2 minutes, and I need it to run for no more than 5 seconds. I've tried many different ways of writing this query with no luck so any assistance will be greatly appreciated. if you need anymore information please let me know.