Performance Query

Question

Bom dia,

Galera!

Preciso de uma ajuda para melhorar a performance desta query abaixo, a mesma está muito lenta.

SELECT	CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR(10), LogPedVendaStatDataHora, 120),1,10)) AS Data, '20170401' AS dataini
FROM	LOG_PED_VENDA_STAT WITH(NOLOCK)
where   CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR(10), LogPedVendaStatDataHora, 120),1,10)) BETWEEN '20170401' AND '20170404'
OR	CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR(10), LogPedVendaStatDataHora, 120),1,10)) IN
	(SELECT	PED_VENDA.PedVendaData
	FROM	PED_VENDA WITH(NOLOCK)
	WHERE 	PED_VENDA.StatPedVendaCod IN ('07','01','02')
	AND	PED_VENDA.PedVendaTipoNota = 'Saída'
	AND	PED_VENDA.PedVendaTipo = 'Total'
	GROUP BY PED_VENDA.PedVendaData)
GROUP BY CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR(10), LogPedVendaStatDataHora, 120),1,10))
ORDER BY CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR(10), LogPedVendaStatDataHora, 120),1,10))

Answer 1

Bom dia,

josé!

As duas estão declaradas como DATETIME

abs.

Answer 2

Bom dia,

Poste aqui o plano de execução.

Answer 3

Bom dia,

Leandro, segue uma sugestão para testes:

with 
    CTE_Log as
    (
        SELECT CAST(LogPedVendaStatDataHora AS DATE) as Data
        FROM LOG_PED_VENDA_STAT WITH(NOLOCK)
        GROUP BY CAST(LogPedVendaStatDataHora AS DATE)
    ),

    CTE_PedVenda as
    (
        SELECT PedVendaData
        FROM PED_VENDA WITH(NOLOCK)
        WHERE
            StatPedVendaCod IN ('07','01','02') AND
            PedVendaTipoNota = 'Saída' AND
            PedVendaTipo = 'Total'
        GROUP BY PedVendaData
    )

SELECT
    l.Data,
    '20170401' AS dataini
FROM CTE_Log as l
LEFT JOIN CTE_PedVenda as p 
    on p.PedVendaData = l.Data
where
    l.Data between '20170401' AND '20170404' or
    p.PedVendaData is not null
ORDER BY l.Data

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br