Slow - Linked server query

• krypto69

  SSChampion

  Points: 13619

  More actions

  November 9, 2026 at 5:10 pm

  #5089606

  Hi

  I have this slow query that is pulling data from a linked server. This is the slow part of the statement:

    SELECT c_sub.claim_id, MAX(cs.status_type) AS clm_status_type
    FROM dbo.tblBatchKeys k_sub
    INNER JOIN [SQL22.CARE.NET\SQL22NODEASTAGE]PROD.dbo.claim c_sub ON c_sub.claim_id = k_sub.claim_id
    INNER JOIN [SQL22.CARE.NET\SQL22NODEASTAGE].PROD.dbo.claim_procedure ci_sub ON c_sub.claim_id = ci_sub.claim_id AND ci_sub.claim_procedure_id = k_sub.claim_procedure_id
    INNER JOIN [SQL22.CARE.NET\SQL22NODEASTAGE].PROD.dbo.claim_procedure_status cs ON cs.claim_procedure_status_id = ci_sub.claim_procedure_status_id
    WHERE k_sub.BatchSeq BETWEEN @StartSeq AND @EndSeq
        AND ci_sub.claim_procedure_status_id IN (71,72,1,70,2,66,79,5,73,80,16)
    GROUP BY c_sub.claim_id

  Is there any way to speed this up? Tried to use Open Query. But I think I'm doing something wrong:

    SELECT top 10 c_sub.claim_id, MAX(cs.status_type) AS clm_status_type
          FROM dbo.tblBatchKeys k_sub
          INNER JOIN OPENQUERY([PROD], 'SELECT * FROM PROD.dbo.claim ') c_SUB ON c_sub.claim_id = k_sub.claim_id   
          INNER JOIN OPENQUERY([PROD], 'SELECT * FROM PROD.dbo.claim_procedure') ci_SUB ON c_sub.claim_id = ci_sub.claim_id AND ci_sub.claim_procedure_id = k_sub.claim_procedure_id
          INNER JOIN OPENQUERY([PROD], 'SELECT * FROM PROD.dbo.claim_procedure_status ') cs ON cs.claim_procedure_status_id = ci_sub.claim_procedure_status_id           
          WHERE k_sub.BatchSeq BETWEEN '1111' AND '2222222'
              AND ci_sub.claim_procedure_status_id IN (71,72,1,70,2,66,79,5,73,80,16)
          GROUP BY c_sub.claim_id

   

  The above doesnt seem to be much faster. Any ideas?