Andreas Ferrer

Tenho uma query que está muito extensa e precisava reduzi-la para ficar mais leve e ter menos tempo de execução dentro do sistema, alguém poderia me ajudar? Código SQL: DECLARE @DTINICIAL VARCHAR(50), @CODVEND VARCHAR(50), @DTFINAL VARCHAR(50) SET @DTINICIAL = '20220101' SET @DTFINAL = '20220131' SET @CODVEND = '000203' SELECT CARTEIRA, COALESCE(( SELECT SUM(RESULTADO.VENDA_REAL) FROM(SELECT DISTINCT SF2.F2_DOC as 'doc', RTRIM(A3_NREDUZ) AS 'VENDEDOR', (F2_VALBRUT - F2_SEGURO) AS 'VENDA_REAL', A1_GRPVEN Segmento FROM SC5010 INNER JOIN SD2010 SD2 ON D2_PEDIDO = C5_NUM AND D2_FILIAL = C5_FILIAL AND SD2.D_E_L_E_T_ = '' INNER JOIN SF2010 SF2 ON F2_DOC = SD2.D2_DOC AND F2_FILIAL = C5_FILIAL AND SF2.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND = '000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' WHERE SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') AND F2_TIPO = 'N' AND CONVERT(DATETIME,F2_EMISSAO,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,F2_EMISSAO,103) <= CONVERT(DATETIME,@DTFINAL,103) AND A3_COD = COD_VEND UNION ALL SELECT '' as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',SUM(-D1_TOTAL) AS 'VENDA_REAL',A1_GRPVEN FROM SD1010 INNER JOIN SC5010 ON C5_NOTA = D1_NFORI AND C5_FILIAL = D1_FILORI AND SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND = '000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' AND A3_COD = COD_VEND WHERE SD1010.D_E_L_E_T_ = '' AND D1_TIPO = 'D' AND (D1_NFORI >'000077289' AND D1_FILORI='0101') OR (D1_NFORI>'000080130' AND D1_FILORI='0102') AND CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL,103) GROUP BY C5_VEND1 , A3_NREDUZ, SA3010.A3_XDTAB, A1_GRPVEN, D1_DTDIGIT HAVING CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL ,103)) RESULTADO),0) VENDAS_VAR , ISNULL((SELECT SUM(ZX1_META) FROM ZX1010 WHERE ZX1010.D_E_L_E_T_ = '' AND ZX1_ANO >= DATEPART(YEAR,@DTINICIAL ) AND ZX1_ANO<=DATEPART(YEAR,@DTFINAL) AND ZX1_MES >= DATEPART(MONTH,@DTINICIAL) AND ZX1_MES<=DATEPART(MONTH,@DTFINAL) AND ZX1_TPCOM='000002' AND ZX1_CODVEN = COD_VEND GROUP BY ZX1_CODVEN),0) META_VAR,ROUND (CASE WHEN (ISNULL(( SELECT SUM(ZX1_META) FROM ZX1010 WHERE ZX1010.D_E_L_E_T_ = '' AND ZX1_ANO >= DATEPART(YEAR,@DTINICIAL) AND ZX1_ANO <= DATEPART(YEAR,@DTFINAL) AND ZX1_MES >= DATEPART(MONTH,@DTINICIAL) AND ZX1_MES <= DATEPART(MONTH,@DTFINAL) AND ZX1_TPCOM = '000002' AND ZX1_CODVEN = COD_VEND GROUP BY ZX1_CODVEN),0)<>0) THEN ((((COALESCE(( SELECT SUM(RESULTADO.VENDA_REAL) FROM(SELECT DISTINCT SF2.F2_DOC as 'doc' ,RTRIM(A3_NREDUZ) AS 'VENDEDOR' ,(F2_VALBRUT - F2_SEGURO) AS 'VENDA_REAL',A1_GRPVEN Segmento FROM SC5010 INNER JOIN SD2010 SD2 ON D2_PEDIDO = C5_NUM AND D2_FILIAL = C5_FILIAL AND SD2.D_E_L_E_T_ = '' INNER JOIN SF2010 SF2 ON F2_DOC = SD2.D2_DOC AND F2_FILIAL = C5_FILIAL AND SF2.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND ='000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' WHERE SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') AND F2_TIPO='N' AND CONVERT(DATETIME,F2_EMISSAO,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,F2_EMISSAO,103) <= CONVERT(DATETIME,@DTFINAL,103) AND A3_COD = COD_VEND UNION ALL SELECT '' as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',SUM(-D1_TOTAL) AS 'VENDA_REAL',A1_GRPVEN FROM SD1010 INNER JOIN SC5010 ON C5_NOTA = D1_NFORI AND C5_FILIAL = D1_FILORI AND SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND = '000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' AND A3_COD = COD_VEND WHERE SD1010.D_E_L_E_T_ = '' AND D1_TIPO = 'D' AND (D1_NFORI >'000077289' AND D1_FILORI='0101') OR (D1_NFORI>'000080130' AND D1_FILORI='0102') AND CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL,103) GROUP BY C5_VEND1 , A3_NREDUZ, SA3010.A3_XDTAB, A1_GRPVEN, D1_DTDIGIT HAVING CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL,103)) RESULTADO),0))/( ISNULL(( SELECT SUM(ZX1_META) FROM ZX1010 WHERE ZX1010.D_E_L_E_T_ = '' AND ZX1_ANO >= DATEPART(YEAR,@DTINICIAL) AND ZX1_ANO <= DATEPART(YEAR,@DTFINAL) AND ZX1_MES >= DATEPART(MONTH,@DTINICIAL) AND ZX1_MES <= DATEPART(MONTH,@DTFINAL) AND ZX1_TPCOM = '000002' AND ZX1_CODVEN = COD_VEND GROUP BY ZX1_CODVEN),0))) * 100)) ELSE (0) END,2) "% ATINGIDO VAREJO",COALESCE(( SELECT SUM(RESULTADO.VENDA_REAL) FROM(SELECT DISTINCT SF2.F2_DOC as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',(F2_VALBRUT - F2_SEGURO) AS 'VENDA_REAL',A1_GRPVEN Segmento FROM SC5010 INNER JOIN SD2010 SD2 ON D2_PEDIDO = C5_NUM AND D2_FILIAL = C5_FILIAL AND SD2.D_E_L_E_T_ = '' INNER JOIN SF2010 SF2 ON F2_DOC = SD2.D2_DOC AND F2_FILIAL = C5_FILIAL AND SF2.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND <>'000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' WHERE SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') AND F2_TIPO = 'N' AND CONVERT(DATETIME,F2_EMISSAO,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,F2_EMISSAO,103) <= CONVERT(DATETIME,@DTFINAL,103) AND A3_COD = COD_VEND UNION ALL SELECT '' as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',SUM(-D1_TOTAL) AS 'VENDA_REAL',A1_GRPVEN FROM SD1010 INNER JOIN SC5010 ON C5_NOTA = D1_NFORI AND C5_FILIAL = D1_FILORI AND SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND <>'000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' AND A3_COD = COD_VEND WHERE SD1010.D_E_L_E_T_ = '' AND D1_TIPO = 'D' AND (D1_NFORI >'000077289' AND D1_FILORI='0101') OR (D1_NFORI >'000080130' AND D1_FILORI='0102') AND CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL,103) GROUP BY C5_VEND1 , A3_NREDUZ, SA3010.A3_XDTAB, A1_GRPVEN, D1_DTDIGIT HAVING CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL,103) ) RESULTADO),0) VENDAS_AT ,ROUND (CASE WHEN (ISNULL(( SELECT SUM(ZX1_META) FROM ZX1010 WHERE ZX1010.D_E_L_E_T_ = '' AND ZX1_ANO >= DATEPART(YEAR,@DTINICIAL) AND ZX1_ANO <= DATEPART(YEAR,@DTFINAL) AND ZX1_MES >= DATEPART(MONTH,@DTINICIAL) AND ZX1_MES <= DATEPART(MONTH,@DTFINAL) AND ZX1_TPCOM <>'000002' AND ZX1_CODVEN = COD_VEND GROUP BY ZX1_CODVEN),0)<>0) THEN ((((COALESCE(( SELECT SUM(RESULTADO.VENDA_REAL) FROM(SELECT DISTINCT SF2.F2_DOC as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',(F2_VALBRUT - F2_SEGURO) AS 'VENDA_REAL',A1_GRPVEN Segmento FROM SC5010 INNER JOIN SD2010 SD2 ON D2_PEDIDO = C5_NUM AND D2_FILIAL = C5_FILIAL AND SD2.D_E_L_E_T_ = '' INNER JOIN SF2010 SF2 ON F2_DOC = SD2.D2_DOC AND F2_FILIAL = C5_FILIAL AND SF2.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND <> '000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' WHERE SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') AND F2_TIPO = 'N' AND CONVERT(DATETIME,F2_EMISSAO,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,F2_EMISSAO,103) <= CONVERT(DATETIME,@DTFINAL,103) AND A3_COD = COD_VEND UNION ALL SELECT '' as 'doc',RTRIM(A3_NREDUZ) AS 'VENDEDOR',SUM(-D1_TOTAL) AS 'VENDA_REAL',A1_GRPVEN FROM SD1010 INNER JOIN SC5010 ON C5_NOTA = D1_NFORI AND C5_FILIAL = D1_FILORI AND SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' AND A1_VEND <>'000044' INNER JOIN SA3010 ON A3_COD = C5_VEND1 AND SA3010.D_E_L_E_T_ = '' AND A3_COD = COD_VEND WHERE SD1010.D_E_L_E_T_ = '' AND D1_TIPO = 'D' AND (D1_NFORI >'000077289' AND D1_FILORI='0101') OR (D1_NFORI >'000080130' AND D1_FILORI='0102') AND CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL ,103) GROUP BY C5_VEND1 , A3_NREDUZ, SA3010.A3_XDTAB, A1_GRPVEN, D1_DTDIGIT HAVING CONVERT(DATETIME,D1_DTDIGIT,103) >= CONVERT(DATETIME,@DTINICIAL,103) AND CONVERT(DATETIME,D1_DTDIGIT,103) <= CONVERT(DATETIME,@DTFINAL ,103)) RESULTADO),0)) /(COALESCE(ZX1_META,0)))*100)) ELSE (0) END,2) "% ATINGIDO ATACADO",COALESCE(ZX1_META,0) AS 'META_AT' FROM(select distinct SZ6.Z6_DOC,(SELECT TOP 1 UPPER(SA3.A3_COD) AS CARTEIRA FROM SA1010 SA1 INNER JOIN SA3010 SA3 ON SA3.A3_COD = SA1.A1_VEND AND SA3.D_E_L_E_T_ = '' WHERE SA1.D_E_L_E_T_ = '' AND SA1.A1_COD = SZ6.Z6_CLIENTE)AS COD_VEND, (SELECT TOP 1 UPPER(SA3.A3_NREDUZ) AS CARTEIRA FROM SA1010 SA1 INNER JOIN SA3010 SA3 ON SA3.A3_COD = SA1.A1_VEND AND SA3.D_E_L_E_T_ = '' WHERE SA1.D_E_L_E_T_ ='' AND SA1.A1_COD = SZ6.Z6_CLIENTE) AS CARTEIRA from SZ6010 SZ6 INNER JOIN SA1010 ON A1_COD = Z6_CLIENTE AND A1_LOJA = Z6_LOJA AND SA1010.D_E_L_E_T_ = '' INNER JOIN SA3010 ON A3_COD = A1_VEND AND SA3010.A3_MSBLQL = '2' AND SA3010.D_E_L_E_T_ ='' WHERE SZ6.D_E_L_E_T_ = '' AND (SELECT TOP 1 UPPER(SA3.A3_NREDUZ) AS CARTEIRA FROM SA1010 SA1 INNER JOIN SA3010 SA3 ON SA3.A3_COD = SA1.A1_VEND AND SA3.D_E_L_E_T_ = '' WHERE SA1.D_E_L_E_T_ = '' AND SA1.A1_COD = SZ6.Z6_CLIENTE) = @CODVEND UNION all SELECT distinct SF2.F2_DOC as 'AUX',A3_COD COD_VEND,UPPER(A3_NREDUZ) FROM SC5010 INNER JOIN SD2010 SD2 ON D2_PEDIDO = C5_NUM AND D2_FILIAL = C5_FILIAL AND SD2.D_E_L_E_T_ = '' INNER JOIN SF2010 SF2 ON F2_DOC = SD2.D2_DOC AND F2_FILIAL = C5_FILIAL AND SF2.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' INNER JOIN SA3010 ON A3_COD = A1_VEND AND SA3010.A3_MSBLQL = '2' AND SA3010.D_E_L_E_T_ ='' WHERE SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') AND F2_TIPO = 'N' AND SA3010.A3_COD = @CODVEND UNION ALL SELECT ' ' AS AUX,A3_COD COD_VEND,UPPER(A3_NREDUZ) FROM SD1010 INNER JOIN SC5010 ON C5_NOTA = D1_NFORI AND C5_FILIAL = D1_FILORI AND SC5010.D_E_L_E_T_ = '' AND C5_X_TPOPE IN ('01','09','18') INNER JOIN SA1010 ON A1_COD = C5_CLIENTE AND A1_LOJA = C5_LOJACLI AND SA1010.D_E_L_E_T_ = '' INNER JOIN SA3010 ON A3_COD = A1_VEND AND SA3010.A3_MSBLQL = '2' AND SA3010.D_E_L_E_T_ = '' WHERE SD1010.D_E_L_E_T_ = '' AND D1_TIPO = 'D' AND (D1_NFORI >'000077289' AND D1_FILORI = '0101') OR (D1_NFORI >'000080130' AND D1_FILORI = '0102') GROUP BY C5_VEND1 , A3_NREDUZ, SA3010.A3_XDTAB, D1_DTDIGIT,A3_COD having SA3010.A3_COD = @CODVEND UNION ALL SELECT ' ' AS AUX,A3_COD COD_VEND,UPPER(A3_NREDUZ) FROM SD1010 INNER JOIN SZ6010 ON Z6_DOC = D1_NFORI AND Z6_CLIENTE = D1_FORNECE AND Z6_SERIE = D1_SERIORI AND SZ6010.D_E_L_E_T_ = '' INNER JOIN SA1010 ON A1_COD = Z6_CLIENTE AND A1_LOJA = Z6_LOJA AND SA1010.D_E_L_E_T_ = '' INNER JOIN SA3010 ON A3_COD = A1_VEND AND SA3010.A3_MSBLQL = '2' AND SA3010.D_E_L_E_T_ = '' WHERE SD1010.D_E_L_E_T_ ='' AND D1_TIPO = 'D' AND (D1_NFORI <='000077289' AND D1_FILORI='0101') OR (D1_NFORI <='000080130' AND D1_FILORI='0102') GROUP BY A3_NREDUZ, D1_DTDIGIT,A3_COD HAVING SA3010.A3_COD = @CODVEND) RESULTADO LEFT OUTER JOIN ZX1010 ON ZX1_CODVEN = RESULTADO.COD_VEND AND ZX1_MES = MONTH(CONVERT(DATETIME,@DTFINAL,103)) AND ZX1_ANO = YEAR(CONVERT(DATETIME,@DTFINAL,103)) AND ZX1010.D_E_L_E_T_ = '' AND ZX1_TPCOM in ('000001','000003') GROUP BY CARTEIRA, COD_VEND, ZX1_META ORDER BY CARTEIRA