SQLTeam.com | Weblogs | Forums

Otimizar Query

Boa Tarde Pessoal,

Gostaria da ajuda de vocês para tentar otimizar está query abaixo se possivel.

SELECT
'REMESSAS' AS NOME_IMPOSTO
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '16'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_16
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '30'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_30
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '40'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_40
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '46'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_46
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '51'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_51
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '52'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_52
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '60'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_60
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '70'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_70
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '72'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_72
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI = '73'
THEN ZB0.ZB0_VLTRAN
else 0
END)) F_73
, SUM(CONVERT(DECIMAL(18,2),CASE
WHEN ZB0.ZB0_FILORI IN('16'
,'30'
,'40'
,'46'
,'51'
,'52'
,'60'
,'70'
,'72'
,'73')
THEN ZB0.ZB0_VLTRAN
else 0
END)) TOTAL
FROM
ZB0010 ZB0(NOLOCK)
WHERE
1 =1
AND ZB0.D_E_L_E_T_ = ' '
AND ZB0.ZB0_HISTOR IN('SE2-ESTORN')
AND ZB0.ZB0_DATA BETWEEN '20211101' AND '20211117'

Desde já agradeço.

The only way to optimize it would be to add an index(es) on the ZB0010 table. Likely the best index structure would be: ( ZB0_DATA, ZB0_HISTOR, ZB0_D_E_L_E_T_ ).

Another option would be to make the clustered index on the table start with column ZB0_DATA.