none
Muito tempo para insert - solução: criar índice? RRS feed

  • Pergunta

  • Ola, pessoal

    Estava tendo um problema com inserir dados em uma tablea, usando o Entity Framework. 

    Analisando, foi resolvido, criando um índice (criei a PK, na verdade) para a tabela (um campo sequencial identity). 

    A performance aumentou absurdamente (antes levava 1 a 2 segundos para gravar um registro - algo absurdo, e passou a gravar uns 100 registros por segundo).

    Fiquei em dúvida, e pensando em situações hipóticas similares: 

    - uma tabela que "apenas cresce" (só tem inserts, não tem delete nem update), tem lógica essa necessidade de um índice, ou o problema era outro, que pode ter acabado por ser resolvido indiretamente, com a criação do índice?

    - como a tabela apenas recebe inserts, há alguma vantagem em criar o índice com fillfactor diferente de 100%?

    - e pensando além, aqui: se fosse considerar SSD, teria lógica a performance em um insert ter sido tão afetada, pela falta de um índice (ou PK) para a tabela? e é necessário dar rebuild / reorganize em índice, no contexto de disco sólido?

    Obrigado!!


    Julio C.

    sexta-feira, 21 de junho de 2019 11:49

Respostas

  • Fiquei em dúvida, e pensando em situações hipóticas similares: 

    - uma tabela que "apenas cresce" (só tem inserts, não tem delete nem update), tem lógica essa necessidade de um índice, ou o problema era outro, que pode ter acabado por ser resolvido indiretamente, com a criação do índice?

    Sem considerar o contexto de Entity Framework:

    • Para gravação, a melhor situação são as tabelas do tipo heap, que não possuem qualquer índice. Ou seja, a situação anterior por você relatada, mas que estava com lentidão na gravação;
    • Como você criou um índice por coluna cujo valor é crescente à medida que novas linhas são acrescentadas na tabela, e informa que não haverá nem remoções nem alterações na tabela, então o valor de 100% no FILLFACTOR é recomendado sim.

    Mas se você relatou que sem o índice a inclusão estava lenta, algo precisa ser analisado para encontrar a causa desse comportamento.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sexta-feira, 21 de junho de 2019 13:08
    Moderador
  • Julio,

    Vamos lá, vou tentar responder as suas dúvidas:

    1 - uma tabela que "apenas cresce" (só tem inserts, não tem delete nem update), tem lógica essa necessidade de um índice, ou o problema era outro, que pode ter acabado por ser resolvido indiretamente, com a criação do índice?

    O índice além de ser um elemento utilizado para permitir a localização de dado de forma mais performática, também é utilizado como elemento de ordenação física e lógica dos dados que estão sendo inseridos em uma tabela, ainda mais quando utilizamos como coluna com a ordenação sequencial.

    Agora, deixando o conceito de índice propriamente dito, a utilização da chave primária seja ela clustered ou non clustered visa justamente garantir a unicidade do referido dado na respectiva coluna, a partir deste momento em que garantimos que este dado será único e exclusivo toda sua forma de armazenamento, ordenação e pesquisa é aplicada Storage Engine e Database Engine de forma específica ao papel deste recurso.

    2 - como a tabela apenas recebe inserts, há alguma vantagem em criar o índice com fillfactor diferente de 100%?

    Por padrão, o valor do fill factor nas versões mais atuais do SQL Server vem definido com a taxa de 80% do valor de preenchimento de uma página de dados, este é um assunto e conceito bastante discutido e vale uma análise para cada cenário.

    Normalmente quando de define o valor de 100% para o fillfactor estamos dizendo para o SQL Server que ele poderá ocupar por completo todo espaço existente e definido para alocação de dados em cada página de dados. o que vai representar uma diminuição de páginas de dados criadas fisicamente, que por consequência podemos dizer, mas não afirmar que teremos uma taxa possivelmente menor de fragmentação de dados.

    Por outro lado, quanto mais preenchido e ocupada for uma página de dados, maior poderá ser considerado ou até mesmo demandado o tempo de processamento para se localizar, identificar e apresentar o referido dado. Como eu costume dizer tudo tem que ser analisado em cenários de estudo, testes, para depois aplicarmos em produção.

    3 - e pensando além, aqui: se fosse considerar SSD, teria lógica a performance em um insert ter sido tão afetada, pela falta de um índice (ou PK) para a tabela? e é necessário dar rebuild / reorganize em índice, no contexto de disco sólido?

    Em relação ao rebuild ou reorganize a partir do momento que você criou esta chave primária com os dados já armazenados nesta tabela, de forma automática o SQL Server realizou o processo de construção desta estrutura de armazenamento de dados, estabelecendo um ponteiro único e exclusivo para cada linha de registro existente na tabela, ou seja, o processo de rebuild foi realizado.

    No que diz respeito ao SSD, sabemos que esta é uma tecnologia para armazenamento e acesso a dados com alta performance para busca de dados, mas o uso de índices, chaves primárias, chaves estrangeiras e até mesmo constraints Unique, são recursos existentes em tecnologias de bancos de dados relacionais que existem e independente do recurso físico ou virtual de armazenamento são necessários.

    Mesmo que você esteja utilizando um SSD é recomendável o uso de uma chave primária, para que o Storage Engine, em conjunto com o Database Engine e principalmente no momento que o Query processor for executar a sua query reconheçam e utilizem os operadores mais indicados pelo plano de execução.

    Quando nossa tabela não possui pelo menos uma chave primária definida, ela é reconhecida como uma Heap Table, o que vai obrigar o query processor e o execution plan a utilizarem o operador Table Scan, fazendo assim uma leitura direta em disco rígido, o que é algo muito ruim no que se refere performance para obtenção 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]



    • Editado Junior Galvão - MVPMVP sexta-feira, 21 de junho de 2019 13:28 correção de erros de português
    • Marcado como Resposta Julio Costi sexta-feira, 21 de junho de 2019 23:22
    sexta-feira, 21 de junho de 2019 13:24

Todas as Respostas

  • Fiquei em dúvida, e pensando em situações hipóticas similares: 

    - uma tabela que "apenas cresce" (só tem inserts, não tem delete nem update), tem lógica essa necessidade de um índice, ou o problema era outro, que pode ter acabado por ser resolvido indiretamente, com a criação do índice?

    Sem considerar o contexto de Entity Framework:

    • Para gravação, a melhor situação são as tabelas do tipo heap, que não possuem qualquer índice. Ou seja, a situação anterior por você relatada, mas que estava com lentidão na gravação;
    • Como você criou um índice por coluna cujo valor é crescente à medida que novas linhas são acrescentadas na tabela, e informa que não haverá nem remoções nem alterações na tabela, então o valor de 100% no FILLFACTOR é recomendado sim.

    Mas se você relatou que sem o índice a inclusão estava lenta, algo precisa ser analisado para encontrar a causa desse comportamento.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sexta-feira, 21 de junho de 2019 13:08
    Moderador
  • Julio,

    Vamos lá, vou tentar responder as suas dúvidas:

    1 - uma tabela que "apenas cresce" (só tem inserts, não tem delete nem update), tem lógica essa necessidade de um índice, ou o problema era outro, que pode ter acabado por ser resolvido indiretamente, com a criação do índice?

    O índice além de ser um elemento utilizado para permitir a localização de dado de forma mais performática, também é utilizado como elemento de ordenação física e lógica dos dados que estão sendo inseridos em uma tabela, ainda mais quando utilizamos como coluna com a ordenação sequencial.

    Agora, deixando o conceito de índice propriamente dito, a utilização da chave primária seja ela clustered ou non clustered visa justamente garantir a unicidade do referido dado na respectiva coluna, a partir deste momento em que garantimos que este dado será único e exclusivo toda sua forma de armazenamento, ordenação e pesquisa é aplicada Storage Engine e Database Engine de forma específica ao papel deste recurso.

    2 - como a tabela apenas recebe inserts, há alguma vantagem em criar o índice com fillfactor diferente de 100%?

    Por padrão, o valor do fill factor nas versões mais atuais do SQL Server vem definido com a taxa de 80% do valor de preenchimento de uma página de dados, este é um assunto e conceito bastante discutido e vale uma análise para cada cenário.

    Normalmente quando de define o valor de 100% para o fillfactor estamos dizendo para o SQL Server que ele poderá ocupar por completo todo espaço existente e definido para alocação de dados em cada página de dados. o que vai representar uma diminuição de páginas de dados criadas fisicamente, que por consequência podemos dizer, mas não afirmar que teremos uma taxa possivelmente menor de fragmentação de dados.

    Por outro lado, quanto mais preenchido e ocupada for uma página de dados, maior poderá ser considerado ou até mesmo demandado o tempo de processamento para se localizar, identificar e apresentar o referido dado. Como eu costume dizer tudo tem que ser analisado em cenários de estudo, testes, para depois aplicarmos em produção.

    3 - e pensando além, aqui: se fosse considerar SSD, teria lógica a performance em um insert ter sido tão afetada, pela falta de um índice (ou PK) para a tabela? e é necessário dar rebuild / reorganize em índice, no contexto de disco sólido?

    Em relação ao rebuild ou reorganize a partir do momento que você criou esta chave primária com os dados já armazenados nesta tabela, de forma automática o SQL Server realizou o processo de construção desta estrutura de armazenamento de dados, estabelecendo um ponteiro único e exclusivo para cada linha de registro existente na tabela, ou seja, o processo de rebuild foi realizado.

    No que diz respeito ao SSD, sabemos que esta é uma tecnologia para armazenamento e acesso a dados com alta performance para busca de dados, mas o uso de índices, chaves primárias, chaves estrangeiras e até mesmo constraints Unique, são recursos existentes em tecnologias de bancos de dados relacionais que existem e independente do recurso físico ou virtual de armazenamento são necessários.

    Mesmo que você esteja utilizando um SSD é recomendável o uso de uma chave primária, para que o Storage Engine, em conjunto com o Database Engine e principalmente no momento que o Query processor for executar a sua query reconheçam e utilizem os operadores mais indicados pelo plano de execução.

    Quando nossa tabela não possui pelo menos uma chave primária definida, ela é reconhecida como uma Heap Table, o que vai obrigar o query processor e o execution plan a utilizarem o operador Table Scan, fazendo assim uma leitura direta em disco rígido, o que é algo muito ruim no que se refere performance para obtenção 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]



    • Editado Junior Galvão - MVPMVP sexta-feira, 21 de junho de 2019 13:28 correção de erros de português
    • Marcado como Resposta Julio Costi sexta-feira, 21 de junho de 2019 23:22
    sexta-feira, 21 de junho de 2019 13:24
  • Ola, muito obrigado pelas explicações e sugestões.

    Testando, dando um Insert direto nesta tabela direto no management é rápido.

    porém, pelo Entity fica bem lento, debugando no visual studio, é mais de 1 segundo (2, 3, 4 as vezes), enfim, inaceitável para um insert. E acho que algo está relacionado ao Entity (já fugindo do contexto aqui do SQL Server, mas se alguém souber de alguma possível interação).

    Essa tabela tem uns 600.000 registros (nada demais, mas já é uma quantidade significante  de dados).

    Suspeitei de que o numero de registros estava causando lentidão, e trunquei a tabela. 

    Ficou muito rapido para inserir (mesmo com o Entity). 

    Teoricamente, seria mais rapido inserir sem chave e índice nenhum , correto? (heap)

    Porém, conforme relatei, fiz outro teste na tabela com os 600.000 registros, criando a PK e ficou um foguete (tanto pelo entity quanto pelo comando no management).

    --

    Até fiz um print do que estava acontecendo com os comandos. Fazendo um laço de 1.000 gravações no contexto da minha situação, vários comandos ficavam em "suspended".

    https://uploaddeimagens.com.br/imagens/comandos_suspended-png

    (não sei qual a diferença do comando ficar SUSPENDED ou em WAIT)

    

    ------

    - Criando a primary key (não sei se por causa da ordenação "física", ou pela consequente criação do índice), ficou MUITO RÁPIDO.


    Julio C.

    terça-feira, 25 de junho de 2019 11:52
  • ** só para melhor compreensão... onde eu digo PK, quero dizer índice non clustered. no nosso contexto não tem chave de não repetição, apenas um ID, que seria para organizar e ordenar os dados.

    Julio C.

    terça-feira, 25 de junho de 2019 11:55
  • Julio,

    Conforme o José Diz, já havia relatado, provavelmente existe alguma cenário relacionado ao Entity Framework que esta justamente impactando no seu ambiente, neste caso, eu não tenho como ajudar no que se relaciona ao EF.

    Respondendo as suas perguntas no post anterior:

    Teoricamente, seria mais rapido inserir sem chave e índice nenhum , correto? (heap)
    Sim, quando estamos manipulando uma massa de dados ou somente vamos realizar procedimentos de inserts ou importação de dados, não usar índices ajuda em muito nas questões relacionadas a performance.

    Até fiz um print do que estava acontecendo com os comandos. Fazendo um laço de 1.000 gravações no contexto da minha situação, vários comandos ficavam em "suspended".

    Tomando como base experiências que tive em linguagens como Delphi, VB e C#, mas principalmente no que trabalho no SQL Server, existe sim diferenças entre suspended e wait, de forma básica vou tentar explicar:

    - Suspended: Significa que o comando que estava sendo executado por algum motivo acabou sendo suspenso, ou seja, provavelmente sua execução foi interrompida.

    - Wait: Teoricamente indica que o referido comando encontra-se em estado de espero, ou seja, aguardando a execução ou encerramento da execução de algum outro comando para que ele possa ser executado ou finalizado.

    Pois bem, eu imagino que você esteja trabalhando com algum loop ou laço de execução, talvez seja o caso de analisar justamente o que esta sendo processado dentro de trecho de código.

    Ao meu ver sua dúvida a partir deste momento esta mais relacionado ao Entity Framework do que ao SQL Server, talvez seja o caso de mover este post para os fóruns de desenvolvimento relacionados ao Visual Studio.


    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, 25 de junho de 2019 12:03
  • Testando, dando um Insert direto nesta tabela direto no management é rápido.
    porém, pelo Entity fica bem lento, debugando no visual studio, é mais de 1 segundo (2, 3, 4 as vezes), enfim, inaceitável para um insert. E acho que algo está relacionado ao Entity

    Júlio, fiz uma pesquisa rápida sobre como o EF manipula tabelas sem índice (heap table) e o que encontrei em vários tópicos de fóruns foi que no modelo ele considera a existência de chave primária.

    Sugiro que verifique na documentação do EF se há como modelar de forma diferente. Enquanto isso, avalie criar a chave primária por coluna de data de inclusão da linha, o que facilitará posteriores pesquisas na tabela de auditoria. Já que é para criar índice, então por algo que seja útil e que não impacte negativamente nas inclusões. Afinal, quando se necessita analisar a tabela de auditoria quase sempre um dos filtros do código SQL é por período.

    Avalie também como o EF se comporta se optar por manter a tabela de auditoria como heap table mas com índice nonclustered pela coluna de data/hora de inclusão da linha.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    terça-feira, 25 de junho de 2019 12:33
    Moderador
  • Júnior Galvão, entendi. Vou dar uma pesquisada mais a fundo nos conceitos de suspended e wait, e ver o que está causando o suspended.

    por enquanto não precisa migrar, vamos analisar melhor o contexto.

    -

    José Diz, entendi, muito provavelmente seja algo relacionado a isso. Vamos verificar mais a fundo a questão do entity e possibilidade de criação de índice.

    -

    Muito obrigado meeeeesmo, pelo auxílio, José  e Júnior  !!


    Julio C.

    terça-feira, 25 de junho de 2019 18:15
  • Julio,

    Ok, obrigado pelo retorno, no início deste post, eu já havia destacado a importância do uso dos índices, tanto clustered como nonclustered, vai sim a pena analisar e identificar qual será o melhor cenário, mesmo que o uso de uma chave primária no seu caso não seja o elemento de unicidade dos dados.

    Abraços.


    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, 25 de junho de 2019 18:38