Click here to Skip to main content
14,696,298 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.
Posted
Updated 9-Aug-12 9:02am
v2
Comments
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

   
Comments
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