CODEWITHSUNDEEP
sqlsql-server
cthulhu
cthulhu

Reputation: 169

how can i substitute a function to make a query faster?

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.

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 >=  '20171201'
AND D2.D2_TES IN ('502','506')
GROUP BY 
LEFT(D2.D2_EMISSAO,6),
F2.F2_VEND1
ORDER BY F2.F2_VEND1 DESC

this is the function:

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 <= @DATE 
      ORDER BY D1.D1_EMISSAO DESC ;  

RETURN ROUND(@UPRECO,2);
END;
GO

Result of the query with the function

enter image description here

I tried with this:

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 <= '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 <= D2.D2_EMISSAO
  AND D2.D2_TES IN ('502','506')
  ORDER BY D2.D2_DOC DESC

Result of the code without You can see that the value from "lucro_liq" is diferent

Upvotes: 1

Views: 77

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Difference in result is because you are using Order by in function, but in sub-select there is no order by. Also adding order by inside sub-select isn't going to make any sense.

I will prefer inline table valued function over scalar function

DROP FUNCTION dbo.Ucompra

go

CREATE FUNCTION dbo.Ucompra(@CODIGO VARCHAR(25),
                            @DATE   VARCHAR(8))
RETURNS TABLE
    RETURN
      (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 <= @DATE
       ORDER  BY D1.D1_EMISSAO DESC)

GO

SELECT LEFT(D2.D2_EMISSAO, 6),
       F2.F2_VEND1,
       Sum(( D2.D2_TOTAL - oa.UPRECO * 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_ = ''
       OUTER apply dbo.Ucompra(D2.D2_COD, D2.D2_EMISSAO) oa
WHERE  D2.D2_EMISSAO >= '20171201'
       AND D2.D2_TES IN ( '502', '506' )
GROUP  BY LEFT(D2.D2_EMISSAO, 6),
          F2.F2_VEND1
ORDER  BY F2.F2_VEND1 DESC

Upvotes: 2

Related Questions