Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Query with better performance

I have an SQL query running too slow because a ‘custom column’. how can i do it in a better way?

there’s the code:

select
    VBI.ID_acesso,
    VBI.data_hora  AS Data_Hora_Entrada,
    VBI.data      AS Data_Entrada,
    VBI.Sentido   AS Sentido,
    VBI.Unidade    AS Unidade,
    replace(replace(VBI.Documento,'.',''),'-', '') AS Documento,
    VBI.Tipo_Documento,
    VBI.Nome,
    VBI.Tipo_Usuario AS Tipo_Usuario,
    VBI.Categoria AS Tipo_Pessoa,
    VBI.Empresa_Usuario,
    VBI.Visitado,
    VBI.Tipo_Documento_Visitado,
        (
        SELECT
            MIN(VBI2.DATA_HORA)
        FROM
            Ses.dbo.VIEW_Mand_Dashboard_BI VBI2
        WHERE
            VBI2.documento = VBI.documento
            AND VBI2.ID_acesso > VBI.ID_acesso
            AND VBI2.Tipo_Documento = VBI.Tipo_Documento
            AND VBI2.Sentido = 'Saída'
            AND VBI2.data_hora >= VBI.data_hora
         ) as Data_hora_Saida
FROM
    Ses.dbo.VIEW_Man_Dashboard_BI VBI
WHERE
    VBI.[data] >= CONVERT(DATE,'2021-10-01')
      and VBI.Sentido = 'Entrada'
    and VBI.Empresa_Usuario  NOT IN ('CAMPSEG')
order by
    VBI.Unidade, VBI.data_hora;

The part that is taking my query slow is the column: Data_hora_Saida

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Maybe try

(SELECT TOP 1 *
  FROM Ses.dbo.VIEW_Mand_Dashboard_BI VBI2
  WHERE VBI2.documento = VBI.documento
  AND VBI2.ID_acesso > VBI.ID_acesso
  AND VBI2.Tipo_Documento = VBI.Tipo_Documento
  AND VBI2.Sentido = 'Saída'
  AND VBI2.data_hora >= VBI.data_hora
  ORDER BY VBI2.DATA_HORA
) as Data_hora_Saida

If VBI2.DATA_HORA is an Int Column it will probably work.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading