none
Otimizar uma query de consulta RRS feed

  • Pergunta

  • olá, 

    estou com uma query grande e ta demorando muito pra retornar os resultados, preciso deixar ela mais rapida. desde ja obrigada

    SELECT 
    empresa as Prest
    ,CRM as CrmReq
    ,Ano_ref as Ano
    ,Mes_ref as Mes
    ,OBSTETRICIA as Obst
    ,mesma_via as Via
    ,Plano as Plano
    ,Urgencia as Urg
    ,Ambulatorial as Amb
    ,Guia as TpGuia
    ,Recurso_Glosa as RecGlosa
    ,Exportado_Cardio as ExportadoCardio
    , Total_Cobrado as valor
    ,Total_Cobrado_Matmed as VlMatMed
    ,Total_Matmed as TotMatMed
    ,sum (Total_Cobrado) as valor
    ,sum (Total_Cobrado_Matmed) as VlMatMed
    ,sum (Total_Matmed) as TotMatMed
    , count (distinct(CM_Gih.Conta)) as Qtdnotas 
    , count (distinct(Usu_Cod)) as QtdUsu

    FROM 
    Sisaudit.dbo.CM_Gih CM_Gih WITH (NOLOCK)
    INNER JOIN Sisaudit.dbo.PREST PREST WITH (NOLOCK) ON CM_Gih.Empresa=PREST.PREST
    WHERE
     CM_Gih.Ano_Ref in (2015)
    and PREST.GR_PREST in (1,2,3,4,5)
    group by 
    empresa
    ,CRM 
    ,Ano_ref 
    ,Mes_ref 
    ,Usu_Acomod 
    ,OBSTETRICIA 
    ,mesma_via 
    ,Plano 
    ,Urgencia
    ,Ambulatorial 
    ,Guia 
    ,Recurso_Glosa 
    ,Exportado_Cardio
    , Total_Cobrado

    ,Total_Cobrado_Matmed

    ,Total_Matmed

    UNION

    SELECT 
    empresa as Prest
    ,CRM as CrmReq
    ,Ano_ref as Ano
    ,Mes_ref as Mes
    ,OBSTETRICIA as Obst
    ,mesma_via as Via
    ,Plano as Plano
    ,Urgencia as Urg
    ,Ambulatorial as Amb
    ,Guia as TpGuia
    ,Recurso_Glosa as RecGlosa
    ,Exportado_Cardio as ExportadoCardio
    ,sum (Total_Cobrado) as valor
    ,sum (Total_Cobrado_Matmed) as VlMatMed
    ,sum (Total_Matmed) as TotMatMed
    , count (distinct(CM_Gih.Conta)) as Qtdnotas 
    , count (distinct(Usu_Cod)) as QtdUsu
    FROM 
    Sisaudit.dbo.CM_Gih CM_Gih WITH (NOLOCK)
    INNER JOIN Sisaudit.dbo.PREST PREST WITH (NOLOCK) ON CM_Gih.Empresa=PREST.PREST
    WHERE 
     CM_Gih.Ano_Ref in (2014)
    and PREST.GR_PREST in (1,2,3,4,5,6,7,8,9,124,125)
    group by 
    empresa
    ,CRM 
    ,Ano_ref 
    ,Mes_ref 
    ,Usu_Acomod 
    ,OBSTETRICIA 
    ,mesma_via 
    ,Plano 
    ,Urgencia
    ,Ambulatorial 
    ,Guia 
    ,Recurso_Glosa 
    ,Exportado_Cardio

    quinta-feira, 7 de maio de 2015 14:23

Respostas

Todas as Respostas

  • Danielle,

    Você pode colocar o plano de execução aqui? Com ele podemos ver o que está acontecendo na execução dessa query, se está faltando indices...

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quinta-feira, 7 de maio de 2015 14:43
  • Já verificou em qual das consultas está a demora? Se é nas duas?

    Pode ser que você adicionando um filtro diferente, você consiga melhorar.


    Att, Arthur Augusto

    quinta-feira, 7 de maio de 2015 14:44
  • Danielle bom dia,

    Ajudar assim apenas analisando a sua query e complicado, mas tipo valida se existe indice nos seus joins, nas cláusulas where.

    Também é valido vc dar uma olhada no plano de execução e dar uma analisada.

    Lembrando que nem sempre criar um indice é bom pois ele ocupa espaço.


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves - acesse o meu blog http://www.tiagoneves.net

    quinta-feira, 7 de maio de 2015 14:45
  • Olá Mariana, tentei enviar o plano de execução mais o site diz que minha conta ainda não pode enviar imagem. Existem ja índices criados, a demora principal é na operação (sum), porém muito necessária e não sei como desmembra-la ou torna-la mais rápida.

    Obrigada

    quinta-feira, 7 de maio de 2015 17:08
  • Existem ja índices criados, a demora principal é na operação (sum), porém muito necessária e não sei como desmembra-la ou torna-la mais rápida.
    quinta-feira, 7 de maio de 2015 17:08
  • ambas as consultas são na mesmas tabelas, porem uma busca em uma é pra buscar valores de anos e meses anteriores e outra pra buscar no ano atual até o mês corrente. Lembrando a demora no plano de execução aparece no (sum)

    obrigada

    quinta-feira, 7 de maio de 2015 17:11
  • Deleted
    • Marcado como Resposta danielle arruda quinta-feira, 7 de maio de 2015 18:24
    quinta-feira, 7 de maio de 2015 17:39
  • a query está funcionando,porem demorar muito, todos os index que vc citou estão criados e acredito que corretamente.

    ambas as consultas são na mesmas tabelas, porem uma busca  é para buscar valores de anos e meses anteriores e outra para buscar no ano atual até o mês corrente. 

    Tentei colocar em uma unica consulta, porem não consegui fazer a condição citada acima ( buscar valores de anos e meses anteriores e outra para buscar no ano atual até o mês corrente )

    Tipo -> 2014 todos os meses / 2015 até mes de 05 (menos 1) que é abril.

    quinta-feira, 7 de maio de 2015 18:19
  • Deleted
    quinta-feira, 7 de maio de 2015 18:37
  • Olha como ficou:

    SELECT 
    empresa as Prest
    ,CRM as CrmReq
    ,Ano_ref as Ano
    ,Mes_ref as Mes
    ,OBSTETRICIA as Obst
    ,mesma_via as Via
    ,Plano as Plano
    ,Urgencia as Urg
    ,Ambulatorial as Amb
    ,Guia as TpGuia
    ,Recurso_Glosa as RecGlosa
    ,Exportado_Cardio as ExportadoCardio
    ,sum (Total_Cobrado) as valor
    ,sum (Total_Cobrado_Matmed) as VlMatMed
    ,sum (Total_Matmed) as TotMatMed
    , count (distinct(CM_Gih.Conta)) as Qtdnotas 
    , count (distinct(Usu_Cod)) as QtdUsu
    FROM 
    Sisaudit.dbo.CM_Gih CM_Gih WITH (NOLOCK)
    INNER JOIN Sisaudit.dbo.PREST PREST WITH (NOLOCK) ON CM_Gih.Empresa=PREST.PREST


    where  (CM_Gih.Ano_Ref = 2015
             and PREST.GR_PREST between 1 and 5
            )  
    or
            (CM_Gih.Ano_Ref = 2014
             and ((PREST.GR_PREST between 1 and 9) or (PREST.GR_PREST between 124 and 125))
            )

    group by 
    empresa
    ,CRM 
    ,Ano_ref 
    ,Mes_ref 
    ,Usu_Acomod 
    ,OBSTETRICIA 
    ,mesma_via 
    ,Plano 
    ,Urgencia
    ,Ambulatorial 
    ,Guia 
    ,Recurso_Glosa 
    ,Exportado_Cardio

    ficou mais limpo e claro, porem muito demorado! 

    FIZ UNS TESTE E A DEMORA TA NO SUM() E COUNT(), SÃO NECESSÁRIOS, COMO POSSO OTIMIZA-LOS?


    • Editado danielle arruda quinta-feira, 7 de maio de 2015 19:30 ACRESCENTEI
    quinta-feira, 7 de maio de 2015 19:26
  • Deleted
    quinta-feira, 7 de maio de 2015 19:47
  • Pude notar alguns erros básicos como :

    CM_Gih.Ano_Ref in (2015) ou CM_Gih.Ano_Ref in (2014)

    quando o correto seria :

    CM_Gih.Ano_Ref =2015

    Uma sugestão é executar essa querie no SQL Profiler. Ele dá boas dicas de custo e melhorias.

    Notei ainda que você esta utilizando a cláusula NOLOCK.

    Isso é uma decisão questionável porque você esta desligando o lock de registros e com isso pode obter informações irreais.

    Cito um exemplo :

    Usuário A : Executo as seguintes operações : Abro uma transação, dou um update gigantesco, aguardo

    Usuário B : Nesse momento, se você fizer uma pesquisa vai pegar os dados das páginas sujas ( dirty pages ) e obter resultados que, na realidade nunca existiram. Ai pega esses dados e publica.

    Usuário A : Dá roolback e com isto cancela o update.

    Esse é o problema do nolock...você faz um sql server virar um bloco de notas.

    Veja as propriedades ACID de banco de dados.

    Nota : O NOLOCK do MS SQL Server não tem nada haver com o NOLOCK do Oracle. Usando a querie hint NOLOCK no Oracle ele não faz o lock do registro para uma cláusula SELECT enquanto o NOLOCK no MS SQL Server desativa o lock de registros.

    Como costumo dizer sempre, SQL é uma teia de aranha ... quando você puxa de um lado, do outro estica e assim por diante...ou seja, uma opção que seja só vantagem...tudo tem seu custo.

    sexta-feira, 8 de maio de 2015 14:36
  • Danielle, como comentado anteriormente, para uma análise completa dos possíveis gargalos auxilia (e muito) obter o plano de execução. Há como salvá-lo e disponibilizá-lo em algum repositório público (OneDrive, DropBox etc)?

    Seguem algumas observações a partir da análise do código fonte.

    (1) Há diferença no número de colunas entre as duas subconsultas. Se analisei corretamente o código fonte, a primeira subconsulta possui 20 colunas e a segunda possui 17 colunas. Me parece que esse comando não funcionaria...

    (2) O uso de distinct geralmente força que o otimizador insira o operador Sort no plano de execução, caso não exista índice pela coluna. Desta forma, no trecho
        ,count (distinct(CM_Gih.Conta)) as Qtdnotas
        ,count (distinct(Usu_Cod)) as QtdUsu
    é provável que tenham sido montadas duas cadeias de processamento, em paralelo, cada uma com um operador Sort, caso não existam índices individuais pelas colunas CM_Gih.Conta e Usu_Cod.

    (3) Quando encontro um comando com a instrução SELECT em que a cláusula GROUP BY tem quase que todas as colunas que fazem parte da lista de colunas exibidas, sei que ali há algo que pode ser otimizado. Mas é necessário ter informações sobre o contexto para propor alternativas.

    (4) Nas duas subconsultas com a instrução SELECT a cláusula FROM é idêntica. Talvez tudo possa ser reunido em um único comando, reduzindo assim a dupla leitura das tabelas.

    (5) Na tabela CM_Gih existe índice pela coluna Empresa?
        Na tabela PREST existe índice pela coluna PREST?

    (6) Construções do tipo
        and PREST.GR_PREST in (1,2,3,4,5,6,7,8,9,124,125)
    geralmente criam algo do tipo
        and (PREST.GR_PREST = 1 or PREST.GR_PREST = 2 or ... or PREST.GR_PREST = 125)
    o que pode, dependendo de outros fatores, tornar a consulta de processamento mais lento. Se isto ocorreu, há formas de alterar esse comportamento do otimizador.

    (7) Há índices individuais pelas colunas CM_Gih.Ano_Ref e PREST.GR_PREST?


        José Diz     Belo Horizonte, MG - Brasil



    Jose.Diz,

    Ótima explanação e comentários.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 8 de maio de 2015 17:13