0

I am a system programmer and entered the forum to understand a little more of the database. Currently I picked a query with a lot of data and I had to check there is a gap between today's date and the date of my table. So I used the function datediff(dd,GetDate(),MyDate) that generated me enorma slow. What can I do?

My query

SELECT DISTINCT CALClientes.Descricao,
                CAL.IDCal,
                TiposNormas.Tipo,
                CAL.Numero,
                CONVERT(VARCHAR(12), CAL.DataPublicacao, 103)                  AS DataPublicacao,
                TiposNormas.Ordem                                              AS OrdemTipo,
                Origens.Ordem                                                  AS OrdemOrigem,
                dbo.fncDescricaoNorma(CAL.IDCal)                               AS norma,
                Orgaos.Orgao,
                Origens.Origem,
                EST.Estado,
                M.Municipio,
                dbo.fncEscoposCal(CAL.IDCal)                                   AS escopos,
                CAL.AssuntoResumo,
                dbo.fncTemasCal(Cal.[IDCal])                                   AS temas,
                VerificacoesClientes.IDVerificacao,
                VerificacoesClientes.IDArea,
                dbo.fncDescricaoArea(VerificacoesClientes.IDArea)              AS area,
                Verificacoes.Pergunta,
                VerificacoesClientes.StatusVerificacao,
                Acoes.Acao,
                Acoes.IDAcao,
                Acoes.Tipo                                                     AS TipoAcao,
                Acoes.Prazo                                                    AS Prazo,
                CASE
                  WHEN datediff(dd, getdate(), Acoes.Prazo) < 0
                       AND DataConclusao IS NULL
                    THEN 'Vencido'
                  WHEN datediff(dd, getdate(), Acoes.Prazo) >= 0
                       AND DataConclusao IS NULL
                    THEN 'A Vencer'
                  WHEN Acoes.Prazo IS NULL
                       AND DataConclusao IS NULL
                    THEN 'Sem Prazo'
                END                                                            AS statusPrazo,
                Usuarios.Nome,
                ResponsaveisPorAcao.IdFuncao,
                dbo.fncAreasResponsaveisPorAcao(Acoes.IDAcao, Usuarios.UserID) AS AreaAcao,
                Acoes.Status,
                Acoes.DataConclusao,
                Observacoes.Observacao
INTO   #temp
FROM   CALClientes
       INNER JOIN NormasCALClientes
         ON CALClientes.IDCALCliente = NormasCALClientes.IDCALCliente
       INNER JOIN Cal
         ON Cal.IDCal = NormasCALClientes.IDCAL
       INNER JOIN TiposNormas
         ON TiposNormas.IDTipo = CAL.IDTipo
       INNER JOIN Origens
         ON Origens.IDOrigem = CAL.IDOrigem
       LEFT JOIN Municipios M
         ON Cal.IDMunicipio = M.Codigo
       LEFT JOIN Estados EST
         ON EST.UF = CAL.UF
       LEFT OUTER JOIN Orgaos
         ON CAL.IDOrgao = Orgaos.IDOrgao
       INNER JOIN AreasEmCalCliente
         ON NormasCALClientes.IDCALCliente = AreasEmCalCliente.IDCALCliente
            AND NormasCALClientes.IDCAL = AreasEmCalCliente.IDCal
       INNER JOIN VerificacoesClientes
         ON VerificacoesClientes.IDCALCliente = AreasEmCalCliente.IDCALCliente
            AND VerificacoesClientes.IDCAL = AreasEmCalCliente.IDCal
            AND VerificacoesClientes.IDArea = AreasEmCalCliente.IDArea
       INNER JOIN Verificacoes
         ON Verificacoes.IDVerificacao = VerificacoesClientes.IDVerificacao
       INNER JOIN Acoes
         ON VerificacoesClientes.IDVerificacaoCliente = Acoes.IDVerificacaoCliente
       LEFT OUTER JOIN ResponsaveisPorAcao
         ON ResponsaveisPorAcao.IDAcao = Acoes.IDAcao
       INNER JOIN Usuarios
         ON ResponsaveisPorAcao.IDResponsavel = Usuarios.UserID
       LEFT JOIN Observacoes
         ON Observacoes.IDAcao = Acoes.IDAcao
WHERE  CalClientes.IDCALCliente IN ( 1172 )
       AND SituacaoInicial IN ( 'Migração', 'Inicial' )
       AND CAL.IDTIPO != -1
       AND CAL.IDTIPO != 5061
       AND CAL.IDTIPO != 5062
       AND VerificacoesClientes.IDArea IN ( 18212 )
       AND NormasCALClientes.Requisitos = 'Sim'
       AND StatusVerificacao NOT IN ( 'Oculto' )
       AND StatusVerificacao NOT IN ( 'Não Aplicável' )
       AND Acoes.Status IN ( 'Não Concluída' )
       AND CALClientes.OcultarAcoes <> 'S'
       AND (( datediff(dd, getdate(), Prazo) < 0 ))

SELECT DENSE_RANK() OVER(ORDER BY Descricao, OrdemOrigem, OrdemTipo, Estado, municipio, DataPublicacao, IDVerificacao, IDArea, IDAcao DESC) AS 'Total Ações',
       Descricao                                                                                                                            AS CAL,
       norma                                                                                                                                AS Norma,
       IDCal                                                                                                                                AS 'Cód. Norma',
       Orgao                                                                                                                                AS Órgão,
       Estado                                                                                                                               AS 'Estado',
       Municipio                                                                                                                            AS Município,
       escopos                                                                                                                              AS Escopos,
       AssuntoResumo                                                                                                                        AS 'Assunto/Obrigação',
       temas                                                                                                                                AS Temas,
       IDVerificacao                                                                                                                        AS 'Cód. Verificação',
       area                                                                                                                                 AS 'Área da Verificação',
       Pergunta                                                                                                                             AS Verificação,
       StatusVerificacao                                                                                                                    AS 'Status da Verificação',
       Acao                                                                                                                                 AS Ação,
       TipoAcao                                                                                                                             AS 'Tipo de Ação',
       CONVERT(VARCHAR(12), Prazo, 103)                                                                                                     AS Prazo,
       statusPrazo                                                                                                                          AS 'Status Prazo',
       Nome                                                                                                                                 AS Responsável,
       IdFuncao                                                                                                                             AS 'Tipo Responsável',
       AreaAcao                                                                                                                             AS 'Áreas',
       Status                                                                                                                               AS 'Status da Ação',
       DataConclusao                                                                                                                        AS 'Data de conclusão',
       Observacao                                                                                                                           AS 'Evidências/Observações'
FROM   #temp
ORDER  BY Descricao,
          OrdemOrigem,
          OrdemTipo,
          Estado,
          municipio,
          DataPublicacao;

DROP TABLE #temp 
  • @MartinSmith my query is too large. I explain the context? – user3845038 Jun 26 '15 at 18:04
  • 3
    Is that in a where clause? If so, SQL Server will be forced to scan. If you show the actual query we might be able to suggest more efficient alternatives. – Aaron Bertrand Jun 26 '15 at 18:09
  • 2
    I'm feeling a link to some "bad habits" blog. – Max Vernon Jun 26 '15 at 18:15
  • 4
  • 4
    Try refactoring the predicate to Prazo < CAST(getdate() AS date) – Dan Guzman Jun 27 '15 at 12:15
1

There are performance implications in the way one expresses a predicate in a WHERE or JOIN clause. Consider the analogy of a list of current students and alumni in chronological order by graduation date. To find alumni, you could calculate the difference in days between now and each individual's graduation date and consider only those with a negative result (in the past). Nothing logically wrong with that method but you would need to evaluate every person on the list. That could be a lot of work many thousands of students.

The same question can be asked in a more efficient way; find the students with a graduation date before today. That allows one to leverage the ordered list and stop when today's date is reached, thus avoid looking at current students. Furthermore, an estimate of how may alumni there are can be gleaned by knowing about how far down in the list today's date is.

This predicate is basically the same as the first example that requires searching the entire list:

AND (( datediff(dd, getdate(), Prazo) < 0 ))

Refactoring the logical expression to the semantically identical one below provides a sargable expression (https://en.wikipedia.org/wiki/Sargable). SQL Server may then choose to use an index on Prazo as an ordered list to efficiently touch only those rows needed. Also, SQL Server will be able to get a better estimate of how many rows qualify, which may allow it to generate a more efficient execution plan overall, which may or may not even use the index.

AND Prazo < CAST(getdate() AS date)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.