How can I make this nested query mess faster?

Question

I'm querying an external Oracle database from my SQL Server database. I know, without the WHERE clause it returns about 190,000 records in about 55 seconds. The addition of the where clause causes the return to take anywhere from 30 seconds to 2.5 minutes. I know it's the last inner join (the nested queries) because without it the return only takes about 2 seconds, but I have no idea how I can fix it. I'm posting the original query below. I've tried making the first table a subquery of the JOBs so I'm only joining on the jobs I need the other info on, but that didn't help.

This is all called with a procedure. @jobNumber is a parameter of that procedure

SELECT * FROM OPENQUERY(externaldb,'SELECT
t1.SERIAL,
t3.PART_NUM,
t2.SUFFIX,
t3.JOB,
t2.DESC,
t2.MODEL,
t2.QTY,
t2.UNIT,
t2.LOCATION,
t6.STATUS,
t1.DESTINATION,
t1.ORDER,
t1.PURCHASER,
t1.PHONE,
t1.CUSTOMER_ID
FROM EXTERNALDB.SERIALS t1
INNER JOIN EXTERNALDB.DELIVERIES t2 ON t1.SERIAL = t2.SERIAL
INNER JOIN EXTERNALDB.DESC t3 ON t2.PART_NUM = t3.PART_NUM
INNER JOIN (SELECT PART, STATUS
            FROM (SELECT
                       t4.SUFFIX,
                       t4.STATUS_TYPE,
                       t4.STATUS_DATE,
                       t4.ID,
                       t4.PART_NUM PART,
                       t4.STATUS,
                       ROW_NUMBER()
                            OVER (PARTITION BY t4.PART_NUM
                            ORDER BY
                                 t4.SUFFIX,
                                 t4.STATUS_TYPE,
                                 t4.STATUS_DATE,
                                 t4.ID) RN
                   FROM EXTERNALDB.STATUSES t4) t5
                   WHERE RN = 1) t6 ON t3.PART_NUM = PART
WHERE t3.JOB = '''''+@jobNumber+'''''')

Answer 1

Maybe instead of create rownumber you should use cross apply to get the status of the number of part. for example

SELECT * FROM OPENQUERY(externaldb,'SELECT
t1.SERIAL,
t3.PART_NUM,
t2.SUFFIX,
t3.JOB,
t2.DESC,
t2.MODEL,
t2.QTY,
t2.UNIT,
t2.LOCATION,
t6.STATUS,
t1.DESTINATION,
t1.ORDER,
t1.PURCHASER,
t1.PHONE,
t1.CUSTOMER_ID
FROM EXTERNALDB.SERIALS t1
INNER JOIN EXTERNALDB.DELIVERIES t2 ON t1.SERIAL = t2.SERIAL
INNER JOIN EXTERNALDB.DESC t3 ON t2.PART_NUM = t3.PART_NUM
-- sintaxis sql server
CROSS APPLY
(
  SELECT TOP 1 Status
  From EXTERNALDB.STATUSES
  Where PART_NUM = t3.PART_NUM
) T6
-- maybe works in oracle
CROSS APPLY
(
  SELECT Status
  From EXTERNALDB.STATUSES
  Where PART_NUM = t3.PART_NUM
  FETCH first 1 rows only
) T6
WHERE t3.JOB = '''''+@jobNumber+'''''')

Reference about top in oracle https://blogs.oracle.com/sql/post/how-to-select-the-top-n-rows-per-group-with-sql-in-oracle-database

Reference about cross apply in oracle https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9530807800346558418

Answer 2

The most performant way to call a linked server is via

EXECUTE (...) AT [LINKED_SERVER]

That way the entire execution of a query will happen on the remote server. SQL Server will just output the result of it.

For the reference: SQL Server: Execute At LinkedServer

The 2nd benefit is that parameters passing is much cleaner. No need to concatenate strings. Check it out below SQL.

SQL

EXECUTE (N'SELECT
    t1.SERIAL,
    t3.PART_NUM,
    ...
    FROM EXTERNALDB.STATUSES t4) t5
    WHERE RN = 1) t6 ON t3.PART_NUM = PART
    WHERE t3.JOB = ?',
@jobNumber) AT [externaldb];