none
Otimização de uma consulta usando sql server RRS feed

  • Discussão Geral

  • Boa tarde pessoal, 

    A consulta abaixo está deixando uma página mais lenta. Não estou conseguindo otimizar.

    SELECT U.schoolID,COUNT(DISTINCT G.user_assessmentID) gradeAmount, COUNT(DISTINCT C.user_assessmentID) compositionAmount  
    FROM Evaluations E
    	INNER JOIN Users_Assessments UA ON  UA.assessmentID = E.assessmentID
    	INNER JOIN Users U ON U.id = Ua.userID
    	LEFT JOIN Grades G ON G.user_assessmentID = UA.id
    	LEFT JOIN Compositions C ON C.user_assessmentID = UA.id
    WHERE UA.assessmentID = @assessmentId
    AND U.schoolID IN @schoolsIds
    GROUP BY U.schoolID

    As tabelas users_assessments, users e grades possuem mais de um milhão de registros.

    @schoolsIds é uma lista com os ids das escolas, estes valores são em relação ao parâmetro @assessmentId.

    Segue abaixo o plano de execução em xml.

    Plano de Execução

    Alguém teria alguma sugestão ? Obrigada




    terça-feira, 28 de maio de 2019 16:19

Todas as Respostas

  • Jéssica,

    Obrigado pelo plano de execução, mas particularmente falando eu prefiro no formato de imagem ao invés de XML.

    Pois bem, gostaria de entender o porque você esta utilizando a função de agregação Count em conjunto com o comando Distinct?

    Vamos começar inicialmente removendo os Distincts deixando somente a função Count(), mesmo que o resultado esperado não seja o que você deseja.

    Poderia fazer este teste?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de maio de 2019 17:27
  • Oi Junior, obrigada pela resposta.

    Caso não use o distinct, os valores das quantidades serão alterados.

    Vou inserir o plano de execução como imagem na resposta.

    Realizei o teste, porém como vc já falou os resultados foram alterados.

    Tempo antes do teste => Tempo de CPU = 266 ms, tempo decorrido = 2283 ms

    Tempo após o teste => Tempo de CPU = 172 ms, tempo decorrido = 1576 ms
    terça-feira, 28 de maio de 2019 18:05
  • Deleted
    terça-feira, 28 de maio de 2019 18:47
  • Oi José,

    Obrigada pela resposta.

    A consulta está lenta nos dois ambientes.

    Sobre a questão que vc levantou o código da consulta estava errado, seria inner join e não left join.

    @schoolIds é uma lista com os ids das escolas, estes valores são em relação ao parâmetro @assessmentId.

    terça-feira, 28 de maio de 2019 19:09
  • Boa tarde,

    Jéssica, não sei se entendi corretamente a sua consulta, mas experimente fazer uns testes com a versão abaixo:

    with CTE_SchoolUA as
    (
        SELECT 
            U.schoolID,
            UA.id
        FROM Evaluations E
        INNER JOIN Users_Assessments UA ON UA.assessmentID = E.assessmentID
        INNER JOIN Users U ON U.id = Ua.userID
        WHERE 
            E.assessmentID = @assessmentId AND 
            U.schoolID IN @schollIds  
        GROUP BY 
            U.schoolID,
            UA.id
    )
    
    SELECT
        C.SchoolID,
        COUNT(G.user_assessmentID) gradeAmount, 
        COUNT(C.user_assessmentID) compositionAmount
    FROM CTE_SchoolUA C
    LEFT JOIN Grades G ON G.user_assessmentID = C.id
    LEFT JOIN Compositions C ON C.user_assessmentID = C.id
    GROUP BY 
        C.schoolID
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 28 de maio de 2019 19:22
  • Deleted
    terça-feira, 28 de maio de 2019 19:26
  • Oi gapimex,

    Obrigada pela resposta.

    Testei com a sua consulta, porém as quantidades tiveram seus valores alterados.

    Mas vou aproveitar a sua ideia e tentar fazer de outra forma.


    terça-feira, 28 de maio de 2019 19:46

  • Oi José,

    Não esta como texto e sim como uma lista de inteiros.

    Se o que @schooslIds contém é um filtro para a coluna schoolID => é isso mesmo, porém é uma lista do tipo int que recebe vários ids. Eu não declaro esta variável, a consulta só recebe a minha lista de inteiros no meu método do c#.

    Não entendi o ganho em declarar a lista de inteiros como variável de tabela. Porque eu recebo várias listas diferentes por assessmentId, acha que mesmo assim é válido inserir a variável de tabela ?

    Isso o plano de execução foi obtido em ambiente de desenvolvimento, vou realizar o update das estatísticas.



    terça-feira, 28 de maio de 2019 19:56
  • Deleted
    terça-feira, 28 de maio de 2019 20:35
  • Deleted
    terça-feira, 28 de maio de 2019 21:01
  • Jéssica,

    Ok, já tivemos uma mudanças, mesmo não tendo o resultado correto, temos a possibilidade de observar que o Distinct dentro do Count não esta sendo vem visto.

    Pois bem, vamos analisar mais um pouco, as suas junções, você esta fazendo uso de Inner e Left, o que você deseja realmente  obter de retorno?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de maio de 2019 22:21
  • Jéssica,

    Outro ponto que gostaria que você se possível me informe, são as taxas de fragmentação das respectivas tabelas utilizadas na query.

    Para tal, vamos utilizer o código de exemplo abaixo:

    Select object_name(ddips.object_id) As 'Tabela', 
           si.name As 'Índice', 
           convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As '% Média de Fragmentação', 
           ddips.page_count As 'Páginas', 
           ddips.compressed_page_count As 'Páginas compactadas', 
           ddips.record_count As 'Registros', 
           ddips.ghost_record_count As 'Registros Fantasmas' 
    From sys.dm_db_index_physical_stats(db_id(), object_id('queimadas2018'),null, null, 'detailed') ddips Inner Join sys.indexes si 
          on si.object_id = ddips.object_id 
    Where ddips.avg_fragmentation_in_percent > 0 
    Go


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de maio de 2019 22:24
  • Deleted
    quarta-feira, 29 de maio de 2019 12:04
  • Oi José,

    Obrigada pela sua explicação, entendi o ponto que vc falou sobre a tabela.

    Usando a sugestão do gapimex,  eu consegui otimizar a consulta usando uma tabela temporária.

    Segue o código abaixo.

    SELECT U.schoolID,UA.id INTO #Temp from Evaluations E
                                INNER JOIN Users_Assessments UA ON UA.assessmentID = E.assessmentID
                                INNER JOIN Users U ON U.id = Ua.userID
                                WHERE 
                                    E.assessmentID = @assessmentId 
    		                        AND U.schoolID IN @schoolsIds
                                    GROUP BY U.schoolID,UA.id
    
                            SELECT
                                T.SchoolID,
                                COUNT(DISTINCT G.user_assessmentID) gradeAmount, 
                                COUNT(DISTINCT C.user_assessmentID) compositionAmount
                            FROM #Temp T
                            LEFT JOIN Grades G ON G.user_assessmentID = T.id
                            LEFT JOIN Compositions C ON C.user_assessmentID = T.id
                            GROUP BY 
                                T.schoolID

    Segue abaixo o novo plano de execução.

    https://gist.github.com/jeh-dias/0957f9d22c702a256ef85aeed0bba1fa

    quarta-feira, 29 de maio de 2019 16:32
  • Jéssica,

    Que bom, mas vale a pena analisar os pontos identificados pelo José Diz, no que se relaciona ao uso do TempDB.

    Um dos pontos de atenção é identificarmos o quanto de memória era necessário para o SQL Server processar esta query, no momento em que ela foi direcionado para o TempDB.

    Outro ponto importante, é também analisar a distribuição de dados neste seu respectivo banco de dados, bem como, a possibilidade de utilizarmos mais arquivos de dados.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 29 de maio de 2019 16:46
  • Jéssica, pode existir mais de uma linha com o mesmo user_assessmentID nas tabelas Grades e Compositions?

    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 29 de maio de 2019 17:22
  • Oi José,

    Vou ler o que vc indicou e tentar descobrir a falta de memória, obrigada.

    quarta-feira, 29 de maio de 2019 18:06
  • Junior,

    Já estou analisando este ponto mesmo e tentando descobrir a causa do uso do TempDB.

    Mais arquivos de dados, seria mais banco de dados ? 

    quarta-feira, 29 de maio de 2019 18:08
  • Oi gapimex,

    Não pode porque user_assessmentID é chave estrangeira nas duas tabelas.

    quarta-feira, 29 de maio de 2019 18:13
  • Jéssica,

    Certo, certo.

    Mais arquivos de dados não seriam mais bancos de dados, mas sim, mais arquivo no mesmo banco, justamente para distribuir toda estrutura de dados e melhorar caso venha a existir alguma possível sobrecarga durante os processos de coleta de dados.

    Utilizar mais de um arquivo de dados tanto para os bancos de dados de usuário e principalmente para o TempDB é um boa prática reconhecida e documentada.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 29 de maio de 2019 18:19
  • Jéssica, não sei se você entendeu corretamente a minha pergunta, mas se a query que você postou retornou o resultado esperado acredito que seja possível obter o mesmo resultado sem utilizar o Distinct dentro do Count com a versão abaixo:

    with CTE_SchoolUA as
    (
        SELECT 
            U.schoolID,
            UA.id
        FROM Evaluations E
        INNER JOIN Users_Assessments UA ON UA.assessmentID = E.assessmentID
        INNER JOIN Users U ON U.id = Ua.userID
        WHERE 
            E.assessmentID = @assessmentId AND 
            U.schoolID IN @schollIds  
        GROUP BY 
            U.schoolID,
            UA.id
    )
    
    SELECT
        C.SchoolID,
        COUNT(GOA.user_assessmentID) gradeAmount, 
        COUNT(COA.user_assessmentID) compositionAmount
    FROM CTE_SchoolUA C
    OUTER APPLY
    (
        SELECT TOP(1) G.user_assessmentID FROM Grades G ON G.user_assessmentID = C.id
    ) GOA
    OUTER APPLY
    (
        SELECT TOP(1) C.user_assessmentID FROM Compositions C ON C.user_assessmentID = C.id
    ) COA
    GROUP BY 
        C.schoolID

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex quarta-feira, 29 de maio de 2019 20:31 correção (palavra reservada GO utilizada como alias de forma involuntária)
    quarta-feira, 29 de maio de 2019 18:27
  • Oi,

    Não consegui executar a sua query.

    Segue a imagem abaixo.

    quarta-feira, 29 de maio de 2019 20:52
  • Erro meu, esqueci de trocar o On para Where:

    with CTE_SchoolUA as
    (
        SELECT 
            U.schoolID,
            UA.id
        FROM Evaluations E
        INNER JOIN Users_Assessments UA ON UA.assessmentID = E.assessmentID
        INNER JOIN Users U ON U.id = Ua.userID
        WHERE 
            E.assessmentID = @assessmentId AND 
            U.schoolID IN @schollIds  
        GROUP BY 
            U.schoolID,
            UA.id
    )
    
    SELECT
        C.SchoolID,
        COUNT(GOA.user_assessmentID) gradeAmount, 
        COUNT(COA.user_assessmentID) compositionAmount
    FROM CTE_SchoolUA C
    OUTER APPLY
    (
        SELECT TOP(1) G.user_assessmentID FROM Grades G WHERE G.user_assessmentID = C.id
    ) GOA
    OUTER APPLY
    (
        SELECT TOP(1) C.user_assessmentID FROM Compositions C WHERE C.user_assessmentID = C.id
    ) COA
    GROUP BY 
        C.schoolID

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 29 de maio de 2019 21:00
  • Oi, funcionou o seu código para o resultado esperado.

    Pode existir mais de uma linha com o mesmo user_assessmentID nas tabelas Grades e Composition ?

    Em relação a sua pergunta, segue abaixo o resultado. 

    quinta-feira, 30 de maio de 2019 12:19
  • Não sei se vai haver diferença no desempenho mas pelos resultados acima você pode utilizar o Left Join com a tabela Compositions em vez do trecho com o Outer Apply onde a mesma é utilizada.

    Não tenho a princípio mais nenhuma sugestão, espero que os demais colegas possam sugerir outras modificações.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 30 de maio de 2019 13:22
  • quebra o codigo em 2 gerando resultado intermediario em tabela temporaria.

    clica aqui -> https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/

    quinta-feira, 13 de junho de 2019 18:46
  • DBA,

    Por mais que o mestre Brent Ozar faça esta comparação no link que você indicou, acredito que para o cenário apresentado pela Jéssica, as observações e orientações aqui apresentadas são satisfatórias para resolucionar a dúvida dela.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 14 de junho de 2019 10:20