none
Performance Query RRS feed

  • Pergunta

  • 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))

    terça-feira, 4 de abril de 2017 13:31

Respostas

  • 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

    terça-feira, 4 de abril de 2017 13:58
  • Deleted
    terça-feira, 4 de abril de 2017 15:10

Todas as Respostas