How long do the two remote queries take to run and how many rows are returned?
SELECT *
FROM OPENQUERY (ingres2, '
SELECT repheader.repairno
, repheader.propref
, repheader.repairdesc
, priority.decode
, reptype.code
, workforce.wforcecode
, workforce.wforcename
, worderline.billval
, worderline.payval
, statehist.statedate
, reptype.decode AS JobType
FROM ((repheader
INNER JOIN workorder ON repheader.repairno = workorder.repairno)
inner JOIN worderline ON workorder.repairno = worderline.repairno
AND workorder.repsubno = worderline.repsubno)
LEFT OUTER JOIN reptype ON reptype.code = repheader.reptypenow
LEFT OUTER JOIN priority ON priority.code = workorder.prioritycode
LEFT OUTER JOIN statehist ON statehist.repaltkey = repheader.repairno
LEFT OUTER JOIN workforce ON repheader.wforcecode = workforce.wforcecode
WHERE statehist.repstatecode = 99')
SELECT *
FROM OPENQUERY (ingres2, '
SELECT
propfixed.propref
, propfixed.houseno
, propfixed.proadd1
, propfixed.proadd2
, propfixed.proadd3
, propfixed.propstcde
, mnode.mnodename
, propfixed.mnodecode
FROM propfixed
inner join mnode ON propfixed.mnodecode = mnode.code' )
At least one filter from your output detail section could be applied to the remote queries.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden