Optimization of SQL-SERVER query

Question

I need to get the field EMISSION_DATE of TLX_VENDA and the countryside B9_DATA table TLX_ESTOQUE_INI, but I need to do this within the OUTER APPLY. I managed to get the result but it’s too slow.

Down goes the last query that I made:

    SELECT  FN.COMPANY,
            FN.FILIAL,
            FN.DOC, 
            FN.SERIE,
            FN.TIPONF,
            NF.NFORI,
            ESTORNO,        
            PRODUTO,
            P.DESCR,        
            FN.LOCAL,   
            ISNULL (A2.A2_COD,A1_COD) [COD CLIENTE],
            ISNULL (A2.A2_NOME,A1_NOME) [NOME CLIENTE],
        --  ISNULL (A2.A2_NOME,A1_NOME) [NOME CLIENTE], 
        --  TM,     
            FN.ORIGEM,      
            NF.XXOPER,
        --  QUANT,      
            FN.NUMSERI,     
        --  V.EMISSION_DATE,
        --  E.B9_DATA
            R.B9_DATA,
            VE.EMISSION_DATE,
            CAST(CONCAT(DATA, ' ', HRINI) AS DATETIME) DT,
    --      DATAFIM,
            CASE FN.FLAG_TM WHEN 'E' THEN 'ENTRADA' WHEN 'S' THEN 'SAIDA' END AS FLAG       
            FROM TLX_INVENTORY_TRANS_HIST FN 
    LEFT JOIN TLX_PRODUTOS_INFO P WITH (NOLOCK)
            ON P.COD = FN.PRODUTO
    LEFT JOIN TLX_NFI_ENTRADASAIDA NF WITH (NOLOCK)
            ON NF.COMPANY = FN.COMPANY 
            AND NF.FILIAL = FN.FILIAL 
            AND NF.DOC = FN.DOC 
            AND NF.SERIE = FN.SERIE AND NF.NUMSEQ = FN.NUMSEQ   
            AND NF.COD = FN.PRODUTO AND NF.FLAG = FN.FLAG_TM
    LEFT JOIN SA1010 A1  WITH (NOLOCK)
            ON A1_COD = FN.CLIFOR 
            AND A1_LOJA = FN.LOJA 
            AND A1.D_E_L_E_T_ = '' 
            AND (
                (FN.FLAG_TM = 'S' AND FN.TIPO NOT IN ('B', 'D')) 
                OR (FN.FLAG_TM = 'E' AND FN.TIPO IN ('B', 'D'))
            ) 
    LEFT JOIN  SA2010 A2  WITH (NOLOCK)
            ON A2_COD = FN.CLIFOR 
            AND A2_LOJA = FN.LOJA 
            AND A2.D_E_L_E_T_ = '' 
            AND (
               (FN.FLAG_TM = 'S' AND FN.TIPO IN ('B', 'D')) 
               OR (FN.FLAG_TM = 'E' AND FN.TIPO NOT IN ('B', 'D'))
            ) 
  OUTER APPLY (
               SELECT B9_COD,B9_DATA 
               FROM TLX_ESTOQUE_INI AS E 
               WHERE E.COMPANY = FN.COMPANY 
               AND E.B9_FILIAL = FN.FILIAL 
               AND E.B9_COD = FN.PRODUTO
   ) AS R
   OUTER APPLY (
               SELECT EMISSION_DATE 
               FROM TLX_VENDAS AS V     
               WHERE V.COMPANY = FN.COMPANY 
               AND V.SITE = FN.FILIAL 
               AND  V.SERIAL_NO = FN.NUMSERI 
               AND V.PART_NO = FN.PRODUTO
   ) AS VE
   WHERE FN.LOCAL IN ('17','18') 
   AND FN.NUMSERI IN ('46774197','41206809','26831647','2925','41552704','42825514','45893206','3528','46774208','42963745','48137580','41601079','41210287','3015192','48860830','44179326','47910283','48031190','48687321','45923658','47962949','49318825','40802021','46618247','47752985','46444583','44104175','46444470','43378548','28297475','47701660','23088358','43381319','23352097','46774677','4822','4703','47881900','47881175','47728987','48318326','47680861','46427019','40401425','44341744','44341745','48521742','27284199','46106793','46103491','47215205','46106792','46106764','46103628','46103712','46103627','47385562','46106710','46103588','46106755','46106744','46106732','44763863','27077336','26994715','40688897','42571588','26698791','46106745','46106708','46106763','46103701','46103625','46106718','26831254','47383688','46106719','46103431','46103429','26690540','46080142','28180776','40720771','28210636','41394485','46559885','46560735',   '46566134','27475370','46561815','17206014','41306310','28014050','28193191','45625293','44561283','26753448','26754226','15557861','19489152','47373318','47781575','42769992','44969218','43422275','42772450','45335787','44278083','44272414','44054486','27920321','45106301','48430893','49283529','48978523','48569659','48573357','48125550','25825863','28036290','25319734','25280187','26337586','26192567','26261904','43799292','26598096','25761297','25749913','25825930','25749477','45470401',    '25259627','25092311','47223168','47109181','47588116','47587625','47587038','666874','689435','692731','637409','680056','691476','692735','691523','691481','445948','691498','691486','27108831','27585283','27585126','27108132','27901937','27941437','27941449','27585197','27901951','27916993','27108051','27585103','27902120','27901920','27901903','27107040','27108048','27941436','27901904','102610','106134','106106','2285310')

Answer 1

I need to get the EMISSION_DATE field from TLX_VENDA and the B9_DATA field from TLX_ESTOQUE_INI table

Therefore, I do not think it is necessary to use APPLY. I suggest that instead of

  OUTER APPLY (
               SELECT B9_COD,B9_DATA 
               FROM TLX_ESTOQUE_INI AS E 
               WHERE E.COMPANY = FN.COMPANY 
               AND E.B9_FILIAL = FN.FILIAL 
               AND E.B9_COD = FN.PRODUTO
   ) AS R
   OUTER APPLY (
               SELECT EMISSION_DATE 
               FROM TLX_VENDAS AS V     
               WHERE V.COMPANY = FN.COMPANY 
               AND V.SITE = FN.FILIAL 
               AND  V.SERIAL_NO = FN.NUMSERI 
               AND V.PART_NO = FN.PRODUTO
   ) AS VE

utilize

   left join TLX_ESTOQUE_INI as R
              on R.COMPANY = FN.COMPANY 
                 and R.B9_FILIAL = FN.FILIAL 
                 and R.B9_COD = FN.PRODUTO
   left join TLX_VENDAS AS VE
              on VE.COMPANY = FN.COMPANY 
                 and VE.SITE = FN.FILIAL 
                 and VE.SERIAL_NO = FN.NUMSERI 
                 and VE.PART_NO = FN.PRODUTO

The simpler, the better.

---

Regarding the improvement of performance, after the above proposed modification, manage the implementation plan and analyze it. If you have no knowledge of execution plans, save the execution plan in XML format and open another topic with a link to it, requesting support in the execution plan analysis.

-- for https://lnkd.in/daWmnx7