0

I am running the following query , and it takes over 1 hour to finish execution. As there are so many joins and then one union is involved, is it normal for this query to be executed in 1 hour, or is it because of bringing result from two different servers ? In fact , there is one procedure , involving this select statement , creation of the procedure also took very long.

select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue 
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev  
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlysalreimb  salreimb 
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0 
Group by reg.Associateid,reg.Companyid 
Union all
select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue 
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev  
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlyarrreimb  salreimb 
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0 
Group by reg.Associateid,reg.Companyid
2
  • Both queries in union are same, Why so ? Nov 5 '14 at 10:25
  • :( dont know. It was there in the procedure only for a long time I guess , but I noticed it today only , while getting a bug from front end , so I resolved it now , by changing the connectionstring to point only a single server , and it works fine now. This is not a production DB , so no issue in changing the server :D:D Nov 5 '14 at 10:39
1

Yes you're right, joins across linked servers is slow. A simple but unflattering solve is to pull the data across from each table on other servers and insert it into a temp table (select a,b,c into #temp from server.database.schema.table) or table variable. Then use that table in your queries. A better way would be to design an ETL to periodically pull these tables onto a single server.

1
  • 1
    It's not so much that they are slow, but you don't get any row elimination from the join syntax. You are right that if you figure out which parts of the off server table you need and pull them over into a temp table and join on that will cause it to go much faster. Apr 5 '16 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.