title: how can i substitute a function to make a query faster?
body: <p>I'm trying to make a query without using a funciton, because it takes too much time for it to give the result. this is the code with the function and the result.</p>
  
  <pre><code>SELECT  
  LEFT(D2.D2_EMISSAO,6),
  F2.F2_VEND1,
  SUM((D2.D2_TOTAL-dbo.UCOMPRA(D2.D2_COD, D2.D2_EMISSAO)*D2.D2_QUANT)) AS  LUCRO_LIQ
  FROM SD2010 D2                
  INNER JOIN SF2010 F2 
      ON D2.D2_DOC = F2.F2_DOC AND D2.D2_SERIE = F2.F2_SERIE AND F2.D_E_L_E_T_= ''
  WHERE 
  D2.D2_EMISSAO &gt;=  '20171201'
  AND D2.D2_TES IN ('502','506')
  GROUP BY 
  LEFT(D2.D2_EMISSAO,6),
  F2.F2_VEND1
  ORDER BY F2.F2_VEND1 DESC
  </code></pre>
  
  <p>this is the function:</p>
  
  <pre><code>ALTER FUNCTION dbo.UCOMPRA( @CODIGO VARCHAR(25), @DATE VARCHAR(8))
  RETURNS float
  
   BEGIN
   DECLARE @UPRECO float;
  
      SELECT TOP 1 @UPRECO = ROUND(D1.D1_CUSTO/D1.D1_QUANT,2)
      FROM SD1010 D1  
     WHERE D1.D_E_L_E_T_ = ''        
         AND D1.D1_TES  IN('015','202','205','206')
             AND D1.D1_COD = @CODIGO 
             AND D1.D1_EMISSAO &lt;= @DATE 
        ORDER BY D1.D1_EMISSAO DESC ;  
  
  RETURN ROUND(@UPRECO,2);
  END;
  GO
  </code></pre>
  
  <p><strong>Result of the query with the function</strong></p>
  
  <p><a href="https://i.stack.imgur.com/Yeswv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yeswv.png" alt="enter image description here"></a></p>
  
  <p>I tried with this:</p>
  
  <pre><code>SELECT  D2.D2_EMISSAO,
  D2.D2_DOC,
  D2.D2_COD,
  D2.D2_QUANT,
  D2.D2_PRCVEN, 
  D2.D2_TOTAL, 
  ENTRADA.CUSTO,
  ((D2.D2_TOTAL/D2.D2_QUANT-ENTRADA.CUSTO)*D2.D2_QUANT) AS  LUCRO_LIQ
  
  FROM SD2010 D2
  LEFT JOIN SD1010 D1 ON D2.D2_COD=D1.D1_COD
  INNER JOIN (SELECT D1.D1_COD AS COD,D1.D1_TOTAL AS D1TOTAL, 
  D1.D1_TOTAL/D1.D1_QUANT AS CUSTO
          FROM SD1010 D1 
          WHERE 
          D1.D1_EMISSAO &lt;= '20171201'
          AND D1.D1_TES IN ('015','202','205','206')
          AND D1.D_E_L_E_T_='') ENTRADA
          ON D2.D2_COD = ENTRADA.COD
  
  
    WHERE D2.D2_COD = '000712S3B70340'
    AND D1.D1_EMISSAO &lt;= D2.D2_EMISSAO
    AND D2.D2_TES IN ('502','506')
    ORDER BY D2.D2_DOC DESC
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/O7ru3.png" rel="nofollow noreferrer">Result of the code without You can see that the value from "lucro_liq" is diferent</a></p>