title: Increase Open Query performance
body: <p>I have a small problem with this query. It returns me around 13k rows and takes around 12seconds or more.</p>
  
  <pre><code>SELECT 
      hla.Id,
      hc.Nome AS Colaborador,
      hla.UAP,
      hra.Referencia, 
      hra.QtdAbastecimento, 
      hra.QtdPecasPorCaixa, 
      hra.QtdCaixas,
      A.Etiqueta, 
      A.Localizacao
  FROM 
      hListasAbastecimento hla
  INNER JOIN hColaboradores hc
  ON hc.Id = hla.ColaboradorId
  INNER JOIN 
      hReferenciasAbastecimento hra
  ON hla.Id = hra.ListaAbastecimentoId
  LEFT JOIN OPENQUERY(MACPAC, 
      'SELECT 
          FET001.ET0102 AS Referencia,
          FET001.ET0101 AS Etiqueta, 
          FET001.ET0109 AS Localizacao
      FROM 
          AUTO.D805DATPOR.FET001 FET001
      WHERE FET001.ET0104 = ''POE'' 
      AND FET001.ET0105 = ''DIS''
      ORDER BY FET001.ET0101 ASC ') A
  ON A.Referencia = hra.Referencia 
  WHERE hla.Id = @Id
  </code></pre>
  
  <p>I'm not sure what can i do to increase the performance of this. I am already using the only primary key to link both tables. I also have no control over the linked server so i cannot create new indexes</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I found out through a co-worker that there was a table on linked server that could help me get results by "UAP" which takes only 4 seconds just to get around 1700 rows which is what i needed</p>
  
  <pre><code>ALTER PROCEDURE GenerateListaAbastecimento
      @Id INT,
      @UAP NVARCHAR(20)
      AS
  BEGIN
  
          CREATE TABLE #tempTable
          (
              Id INT PRIMARY KEY,
              Referencia NVARCHAR(15),
              Etiqueta INT,
              Localizacao NVARCHAR(20)
  
              UNIQUE(Id, Etiqueta)
          )
  
          DECLARE @SQL NVARCHAR(MAX)
          SELECT @SQL ='INSERT INTO #tempTable    
          SELECT 
              Id, 
              Referencia, 
              Etiqueta, 
              Localizacao 
          FROM OPENQUERY(MACPAC, 
          ''SELECT 
              ROW_NUMBER() OVER(ORDER BY FET001.ET0101 ASC) AS Id,
              A.RH6001 as Referencia, 
              FET001.ET0101 AS Etiqueta, 
              FET001.ET0109 AS Localizacao
          FROM AUTO.D805DATPOR.TRP060H AS A
          LEFT JOIN AUTO.D805DATPOR.FET001 FET001
          ON FET001.ET0102 = A.RH6001
          AND FET001.ET0104 = ''''POE'''' 
          AND FET001.ET0105 = ''''DIS'''' 
          AND A.RH6002 = N'''  + QUOTENAME(@UAP,N'''') +  N''' '')'   
  
          EXEC sp_executesql @SQL
  
      SELECT 
          hla.Id,
          hc.Nome AS Colaborador,
          hla.UAP,
          hra.Referencia, 
          hra.QtdAbastecimento, 
          hra.QtdPecasPorCaixa, 
          hra.QtdCaixas,
          A.Etiqueta, 
          A.Localizacao
      FROM 
          hListasAbastecimento hla
      INNER JOIN hColaboradores hc
      ON hc.Id = hla.ColaboradorId
      INNER JOIN 
          hReferenciasAbastecimento hra
      ON hla.Id = hra.ListaAbastecimentoId
      LEFT JOIN #tempTable A
      ON A.Referencia = hra.Referencia 
      WHERE hla.Id = @Id
      ORDER BY A.Etiqueta ASC
  
      DROP TABLE #tempTable
  
  END
  </code></pre>