Click here to Skip to main content
15,120,814 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Updated 9-Aug-12 9:02am
StianSandberg 9-Aug-12 15:39pm
Try to add some proper indexes.
corey80844 10-Aug-12 14:07pm
Could you explain please?
Abdul Quader Mamun 9-Aug-12 17:46pm
use cluster/non-cluster indices on condition basis.
corey80844 10-Aug-12 14:08pm
Could you explain a little more please?

1 solution

corey80844 10-Aug-12 14:09pm
I've looked and this seems pretty complex for me, I'm not that good with sql yet.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900