SQL-SERVER query optimization

Navigation
0

I need to get the EMISSION_DATE field of TLX_VENDA and the B9_DATA field of the TLX_ESTOQUE_INI table, however I need to do this within OUTER APPLY . I managed to get into the result but it is very slow.

Here is the last% wc% 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')
    
asked by anonymous 02.02.2018 / 13:16

1 answer

0
  

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

For your request, I do not think it 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

use 

   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.

In order to improve performance, after performing the proposed modification above generate the execution plan and analyze it. If you are not aware of execution plans, save the execution plan in XML format and open another topic with a link to it, requesting support in the analysis of the execution plan.

- by link

    
02.02.2018 / 17:57
Owlcarousel: enable paging visualization for different sliders Datagridview does not store information in the table