none
Performance em consultar tabelas muito grandes RRS feed

  • Pergunta

  • Não sou profissional de programação mas posso me considerar um curioso avançado, ok?

    Fiz um aplicativo onde em C# onde acesso varias tabelas de um banco de dados, consultando seus registros, tratando-os e inserindo novos registros em uma nova tabela baseados nessas consultas.

    O problema é que essas tabelas possuem cerca de 54 milhoes de registros cada uma. A grosso modo minha rotina é assim para cada tabela:

    1. Faço uma consulta (WHERE) na TABELA_ORIGEM para montar um dataset no C#.
    2. Trabalho esse dataset da consulta para tratar os dados;
    3. Consulto (WHERE) numa TABELA_DESTINO se um timestamp já existe.
    4. Caso não exista, insiro (INSERT) um novo registro na tabela destino;
    5. Caso exista, atualizo (UPDATE) a informação na tabela destino;

    Acontece que a medida que a tabela destino vai crescendo a consulta (item 3) vai ficando grande também e o sistema vai ficando muito lento.

    Pergunta: Esse tipo de serviço pode ser feito dessa forma atraves de um aplicativo desenvolvido no C#, como eu estou fazendo?

    Pergunta: Este fato das tabelas enormes, tenho que me preocupar com alguma questão de performance? nas consultas?

    Obrigado

    quarta-feira, 24 de agosto de 2016 20:39

Respostas

  • Deleted
    terça-feira, 30 de agosto de 2016 17:41
  • O código final ficou assim:

    use RELACAO
    
    declare @HorárioInicial datetime, @HorárioFinal datetime;
    
    set dateformat dmy;
    set @HorárioInicial= '1/1/2011 0:00:00';
    set @HorárioFinal= '30/7/2016 23:59:59.997';
    
    
    -- gera estrutura temporária 
    IF Object_ID('TempDB..#Origem_Única', 'U') is not null
      DROP TABLE #Origem_Única;
      
    CREATE TABLE #Origem_Única (
        Horário datetime not null primary key,
        Media float null
        /*Temperatura_Descarga float null,
        Vazão_Biogás float null*/
    );
    
    --
    with
    Média_1 as (
    SELECT DateAdd(minute, DateDiff(minute, 0, MEVA_DT_MEDICAO), 0) as Horário, 
           Avg(MEVA_MD_VAZAO_NOMINAL) as Media
           
      from MEDICAO_VAZAO_1
      where MEVA_DT_MEDICAO between @HorárioInicial and @HorárioFinal and ITMO_SQ_ITEM_MONITOR = 48 and MEVA_MD_VAZAO_NOMINAL > 0
      group by DateAdd(minute, DateDiff(minute, 0, MEVA_DT_MEDICAO), 0)
    )
    
    INSERT into #Origem_Única
      SELECT T1.Horário, 
           T1.Media
        from Média_1 as T1;
             /*inner join Média_Biogás as T2 on T1.Horário = T2.Horário*/
    
    --
    MERGE
      into CER_VIEW_1 as CV
      using #Origem_Única as OU
      on CV.E3TimeStamp = OU.Horário
      when matched
           then UPDATE set Pressao_Descarga= OU.Media
                           
      when not matched by target
           then INSERT (E3TimeStamp, Pressao_Descarga) 
                values (OU.Horário, OU.Media);
    
    DROP TABLE #Origem_Única;

    Mais um, muito obrigado

    Pestana

    • Marcado como Resposta BetoPestana sexta-feira, 2 de setembro de 2016 15:03
    sexta-feira, 2 de setembro de 2016 15:03

Todas as Respostas

  • Deleted
    quarta-feira, 24 de agosto de 2016 22:48
  • Obrigado pelo retorno José Diz,

    na tabela destino o primeiro campo é o timestamp. Mas como disse, sou usuário curioso avançado e não sei o que é clustered ou nonclustered. eiQuanto ao MERGE nunca usei. veja a imagem abaixo.

    Obrigado

    quinta-feira, 25 de agosto de 2016 16:19
  • Detalhando o item: "Trabalho o dataset", seria o seguinte:

    O trabalho que tenho a fazer é pegar algumas tabelas que possuem um histórico de leituras a cada 15 segundos. Todas as tabelas possuem a mesma estrutura: uma campo time stamp e o campo de leitura com os valores. A tabela destino possui da mesma forma o primeiro campo de timestamp e mais todas as colunas refeerentes as leituras que serão feitas em cada tabela individualmente. Cada tabela individual dessas possui cerca de 54 milhoes de registros. E a tabela destino ira concentrar as leituras de todas as individuais, em cada minuto de timestamp existente.

    Como nas tabelas individuais tenho mais de uma leitura dentro do mesmo minuto eu preciso fazer uma media do valor lido e escrever esse valor no time stamp deste minuto na tabela destino.

    Se o minuto não existir na tabela destino, que inicia vazia, eu insiro este valor medio na coluna desejada. As demais colunas no momento da inserção desse registro são inseridos com "zero".

    Se eu já tiver um minuto timestamp criado na coluna destino eu faço um update do valor da coluna desejada.

    Tudo acontece numa velocidade, no meu modo amador de entender razoável, mas a medida que a tabela destino vai crescendo, parece que o sistema perde muito tempo para consultar se o time stamp já existe.

    A lógica funciona perfeitamente e não trava, mas a performance inviabiliza, pois vai levar uns 6 meses trabalhando.

    Obrigado

    quinta-feira, 25 de agosto de 2016 16:42
  • Deleted
    quinta-feira, 25 de agosto de 2016 19:24
  • Ok José Diz,

    Realmente percebo que o porte da dúvida é incompatível com o status de "curioso avançado" com o qual me apresentei, certo?

    Estou estudando seus links indicados.

    Uma última dúvida: Em meu aplicativo C# possuo um loop de cerca de 5 milhoes que varre todos os registros consultados na tabela origem com cerca de 54 milhoes de registros. A cada rodada desse loop eu executo esta lógica:

    if (instBD.verifExisteTIME(PARÂMETROS...) == 0)
                                        {
                                            instBD.insereNovoReg(PARÂMETROS...);
                                        }
                                        else instBD.updateReg(PARÂMETROS...);

    Onde no IF eu vejo se o timestamp já existe na tabela destino.

    Ou seja, no inicio onde temos uma tabela destino vazia tudo acontece bem rápido, mas ao longo do tempo acredito que este "verifExisteTIME" esteja ficando lento por ser executado muitas vezes. Procede meu raciocínio?

    Obrigado

    sexta-feira, 26 de agosto de 2016 20:08
  • Deleted
    terça-feira, 30 de agosto de 2016 12:47
  • Deleted
    terça-feira, 30 de agosto de 2016 17:41
  • José Diz, realmente perfeita sua abordagem no exemplo. É exatamente isso que acontece. O exemplo funcionou perfeitamente mas ainda estou dando algumas cabeçadas para replicá-lo na aplicação real pois como vc utilizou os mesmos nomes entre variáveis, alias e colunas no exemplo, a confusão ainda está grande.

    De qualquer forma acho que vou conseguir chegar lá com esse exemplo que vc enviou. Pergunta: EU CONSIGO ATRAVÉS DO CÓDIGO #4 FAZER COM QUE EU POSSA TER TABELAS ORIGENS QUE NÃO POSSUAM TODOS OS MINUTOS?

    Desde já muito obrigado pela ajuda.

    Forte abraço

    Pestana

    quinta-feira, 1 de setembro de 2016 16:05
  • Deleted
    sexta-feira, 2 de setembro de 2016 11:25
  • Funcionou perfeito. Simplesmente fantástico!

    Muito obrigado José Diz.

    Forte abraço

    Pestana

    sexta-feira, 2 de setembro de 2016 14:31
  • O código final ficou assim:

    use RELACAO
    
    declare @HorárioInicial datetime, @HorárioFinal datetime;
    
    set dateformat dmy;
    set @HorárioInicial= '1/1/2011 0:00:00';
    set @HorárioFinal= '30/7/2016 23:59:59.997';
    
    
    -- gera estrutura temporária 
    IF Object_ID('TempDB..#Origem_Única', 'U') is not null
      DROP TABLE #Origem_Única;
      
    CREATE TABLE #Origem_Única (
        Horário datetime not null primary key,
        Media float null
        /*Temperatura_Descarga float null,
        Vazão_Biogás float null*/
    );
    
    --
    with
    Média_1 as (
    SELECT DateAdd(minute, DateDiff(minute, 0, MEVA_DT_MEDICAO), 0) as Horário, 
           Avg(MEVA_MD_VAZAO_NOMINAL) as Media
           
      from MEDICAO_VAZAO_1
      where MEVA_DT_MEDICAO between @HorárioInicial and @HorárioFinal and ITMO_SQ_ITEM_MONITOR = 48 and MEVA_MD_VAZAO_NOMINAL > 0
      group by DateAdd(minute, DateDiff(minute, 0, MEVA_DT_MEDICAO), 0)
    )
    
    INSERT into #Origem_Única
      SELECT T1.Horário, 
           T1.Media
        from Média_1 as T1;
             /*inner join Média_Biogás as T2 on T1.Horário = T2.Horário*/
    
    --
    MERGE
      into CER_VIEW_1 as CV
      using #Origem_Única as OU
      on CV.E3TimeStamp = OU.Horário
      when matched
           then UPDATE set Pressao_Descarga= OU.Media
                           
      when not matched by target
           then INSERT (E3TimeStamp, Pressao_Descarga) 
                values (OU.Horário, OU.Media);
    
    DROP TABLE #Origem_Única;

    Mais um, muito obrigado

    Pestana

    • Marcado como Resposta BetoPestana sexta-feira, 2 de setembro de 2016 15:03
    sexta-feira, 2 de setembro de 2016 15:03