none
Consulta lenta com variável Datetime RRS feed

  • Pergunta

  • Olá Pessoal,

    estou com um problema "estranho", ao menos para mim.

    Na minha procedure eu faço um busca numa tabela durante um período usando duas variáveis datetime. com isso ela fica muitooooo lenta, porém seu eu fixar a mesma data ela fica no tempo desejado.

    alguém tem ideia do que possa ser?


    modo rápido:
    DECLARE @PAC TABLE (PAC INT, FTP INT, DAT DATETIME, ID_RET INT , MPS INT,NPF INT,RF4 VARCHAR(255))
    INSERT @PAC
    SELECT P.PAC,P.FTP,P.DAT,P.ID_RET, P.MPS, P.NPF, P.RF4 FROM PACIENTES P 
    WHERE P.DAT >= '09/01/2009' AND  P.DAT < '09/30/2009'
    AND ISNULL(P.ID_RET,0)=CASE WHEN @IDRET=1 THEN 1 ELSE ISNULL(P.ID_RET,0) END
    AND CASE @MPS WHEN 0 THEN 0 ELSE P.MPS END = @MPS 
    modo lento:
    DECLARE @D1 DATETIME, @D2 DATETIME
    
    SELECT D1='09/01/2009',@D2='09/30/2009'
    
    DECLARE @PAC TABLE (PAC INT, FTP INT, DAT DATETIME, ID_RET INT , MPS INT,NPF INT,RF4 VARCHAR(255))
    INSERT @PAC
    SELECT P.PAC,P.FTP,P.DAT,P.ID_RET, P.MPS, P.NPF, P.RF4 FROM PACIENTES P 
    WHERE P.DAT >= @D1 AND  P.DAT < @D2
    AND ISNULL(P.ID_RET,0)=CASE WHEN @IDRET=1 THEN 1 ELSE ISNULL(P.ID_RET,0) END
    AND CASE @MPS WHEN 0 THEN 0 ELSE P.MPS END = @MPS 
    obrigado.

    Luan
    quinta-feira, 11 de março de 2010 19:38

Respostas

  • Da uma olhada no artigo do Fabiano.

    http://fabianosqlserver.spaces.live.com/blog/cns!52EFF7477E74CAA6!1440.entry?sa=150835363


    Tente colocar no final do select o hint option(recompile)

    DECLARE @D1 DATETIME, @D2 DATETIME

    SELECT D1='09/01/2009',@D2='09/30/2009'

    DECLARE @PAC TABLE (PAC INT, FTP INT, DAT DATETIME, ID_RET INT , MPS INT,NPF INT,RF4 VARCHAR(255))
    INSERT @PAC
    SELECT P.PAC,P.FTP,P.DAT,P.ID_RET, P.MPS, P.NPF, P.RF4 FROM PACIENTES P
    WHERE P.DAT >= @D1 AND  P.DAT < @D2
    AND ISNULL(P.ID_RET,0)=CASE WHEN @IDRET=1 THEN 1 ELSE ISNULL(P.ID_RET,0) END
    AND CASE @MPS WHEN 0 THEN 0 ELSE P.MPS END = @MPS
    option (recompile)

    Fabrício França Lima | MCP, MCTS, MCITP | http://fabriciodba.spaces.live.com/
    • Marcado como Resposta Luan Santos sexta-feira, 12 de março de 2010 17:44
    quinta-feira, 11 de março de 2010 20:45
  • Luan,

    quando há problemas de lentidão de consulta específica, o primeiro passo é analisar o plano de execução.

    Compare os planos de execução usando valor fixo e uso de variável e verifique se são usados os mesmos índices, se no caso de valor fixo são usados table scans e também se as estatísticas dos índices e colunas usados na consulta estão atualizadas.

    Você também pode verificar quanto tempo é gasto no parse de cada um, mas acredito que a análise do plano de execução te dará mais indicação do problema.

    Procedure é compilada na primeira execução não na criação. Na primeira execução, é feita compilação e é criado um plano de execução que é reutilizado sempre que estiver no cache. Caso a primeira execução seja lenta e as posteriores rápidas, pode ser problema de parse.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    • Marcado como Resposta Luan Santos sexta-feira, 12 de março de 2010 17:44
    sexta-feira, 12 de março de 2010 12:21

Todas as Respostas

  • Da uma olhada no artigo do Fabiano.

    http://fabianosqlserver.spaces.live.com/blog/cns!52EFF7477E74CAA6!1440.entry?sa=150835363


    Tente colocar no final do select o hint option(recompile)

    DECLARE @D1 DATETIME, @D2 DATETIME

    SELECT D1='09/01/2009',@D2='09/30/2009'

    DECLARE @PAC TABLE (PAC INT, FTP INT, DAT DATETIME, ID_RET INT , MPS INT,NPF INT,RF4 VARCHAR(255))
    INSERT @PAC
    SELECT P.PAC,P.FTP,P.DAT,P.ID_RET, P.MPS, P.NPF, P.RF4 FROM PACIENTES P
    WHERE P.DAT >= @D1 AND  P.DAT < @D2
    AND ISNULL(P.ID_RET,0)=CASE WHEN @IDRET=1 THEN 1 ELSE ISNULL(P.ID_RET,0) END
    AND CASE @MPS WHEN 0 THEN 0 ELSE P.MPS END = @MPS
    option (recompile)

    Fabrício França Lima | MCP, MCTS, MCITP | http://fabriciodba.spaces.live.com/
    • Marcado como Resposta Luan Santos sexta-feira, 12 de março de 2010 17:44
    quinta-feira, 11 de março de 2010 20:45
  • Fabricio, 

    estou usando sql 2000 nesse cliente, daí este comando não funciona.

    obrigado.

    Luan
    sexta-feira, 12 de março de 2010 11:34
  • Luan,

    quando há problemas de lentidão de consulta específica, o primeiro passo é analisar o plano de execução.

    Compare os planos de execução usando valor fixo e uso de variável e verifique se são usados os mesmos índices, se no caso de valor fixo são usados table scans e também se as estatísticas dos índices e colunas usados na consulta estão atualizadas.

    Você também pode verificar quanto tempo é gasto no parse de cada um, mas acredito que a análise do plano de execução te dará mais indicação do problema.

    Procedure é compilada na primeira execução não na criação. Na primeira execução, é feita compilação e é criado um plano de execução que é reutilizado sempre que estiver no cache. Caso a primeira execução seja lenta e as posteriores rápidas, pode ser problema de parse.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    • Marcado como Resposta Luan Santos sexta-feira, 12 de março de 2010 17:44
    sexta-feira, 12 de março de 2010 12:21

  • Ok. Ja tive um problema desse no 2005 e o option (recompile) me ajudou.

    No meu caso as querys estavam usando indices diferentes com o valor fixo e a variável.

    Faça o que o alex disse:
    "Compare os planos de execução usando valor fixo e uso de variável e verifique se são usados os mesmos índices, se no caso de valor fixo são usados table scans e também se as estatísticas dos índices e colunas usados na consulta estão atualizadas."


    Se depois disso ainda continuar lenta, tenta forçar o índice da primeira query na segunda incluindo o hint with(index=Nome_Indice).



    DECLARE @D1 DATETIME, @D2 DATETIME

    SELECT D1='09/01/2009',@D2='09/30/2009'

    DECLARE @PAC TABLE (PAC INT, FTP INT, DAT DATETIME, ID_RET INT , MPS INT,NPF INT,RF4 VARCHAR(255))
    INSERT @PAC
    SELECT P.PAC,P.FTP,P.DAT,P.ID_RET, P.MPS, P.NPF, P.RF4 FROM PACIENTES P with(index=Nome_Indice)
    WHERE P.DAT >= @D1 AND  P.DAT < @D2
    AND ISNULL(P.ID_RET,0)=CASE WHEN @IDRET=1 THEN 1 ELSE ISNULL(P.ID_RET,0) END
    AND CASE @MPS WHEN 0 THEN 0 ELSE P.MPS END = @MPS

    Dizem que isso não é aconselhável fazer, mas no meu ambiente ja tive que usar muito isso na hora do aperto e resolveu.




    Fabrício França Lima | MCP, MCTS, MCITP | http://fabriciodba.spaces.live.com/
    sexta-feira, 12 de março de 2010 13:15
  • Luan,

    os hints não são recomendados porque teoricamente o SQL Server faz o melhor plano de acordo com as distribuições de valores dos índices e colunas(sem índice).

    Se for o caso de usar o hint, faça-o apenas para teste. Depois verifique se sua consulta pode ser alterda de modo que o plano seja o melhor possível.
    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    sexta-feira, 12 de março de 2010 15:26
  • Alex, aproveitando a discurssão do post...

    Tenho uma tabela com 38 milhões de registros.

    De uma hora para a outra uma fnc parou de utilizar um índice.
    Não existe nenhum índice com mais de 5% de fragmentação. 1 Update Statitics FULL_SCAN a cada 15 dias com uma inserção de 500 mil registros nesse período.

    Uma SP que roda em 1 segundo passa a rodar em mais de 1 minuto no ambiente de produção ocasionando reclamações dos meus usuários (telas paradas).

    Minhas solução imediata foi colocar o hint para forçar o índice, na hora ela passou a rodar em 1 segundo e o sistema voltou ao normal.

    Teria uma outra solução? Já li que isso não é aconselhável, mas não vejo uma outra forma de resolver meu problema.


    Fabrício França Lima | MCP, MCTS, MCITP | http://fabriciodba.spaces.live.com/
    sexta-feira, 12 de março de 2010 15:57