Manipulação de milhões de registros (otimização de código)

leva meia hora somente pra persistir os dados?

Se isso for algo que só deve ser executado uma vez, e caso esse banco seja só pra isso, você pode montar um script com os 40 milhões de inserts e rodar direto no banco, e aí trabalha disso pra frente.

Você já não tinha um tópico sobre esse problema? Duplicar tópicos não é muito bem visto.

na verdade, antes meu problema era como ler o arquivo … esse problema já foi resolvido …
meu problema agora é que o código ficou lento ( ou não ), só queria a opinião de pessoas mais experientes para saber se tem como melhorar o código ou não

obrigado pela atenção

hamisterbr:

na verdade, antes meu problema era como ler o arquivo … esse problema já foi resolvido …
meu problema agora é que o código ficou lento ( ou não ), só queria a opinião de pessoas mais experientes para saber se tem como melhorar o código ou não

obrigado pela atenção

E quanto à minha pergunta? esse tempo é somente para persistir os dados? A minha sugestão é válida para o seu cenário ?

Bom dia!

Qual o BD que você está usando?

[]'s

Rodrigo Sasaki:

hamisterbr:

na verdade, antes meu problema era como ler o arquivo … esse problema já foi resolvido …
meu problema agora é que o código ficou lento ( ou não ), só queria a opinião de pessoas mais experientes para saber se tem como melhorar o código ou não

obrigado pela atenção

Ah, ok. Entendi.

E quanto à minha pergunta? esse tempo é somente para persistir os dados? A minha sugestão é válida para o seu cenário ?

O tempo seria pra tudo, ler o dado do csv e persistir no banco de dados.
em relação a sua sugestão, se eu conseguisse fazer um script no MySql que lesse um CSV, acho que seria valida sim, mas meu conhecimento não permite isso no momento
vou dar uma estudada a respeito

Bom dia!

Qual o BD que você está usando?

[]'s

estou usando o MySql

Talvez seu problema esteja no momento de inserir os dados, pelo que vi é feito insert a cada iteração, normalmente para este tipo de situação eu crio script, ao invés de salvar direto.
Recentemente precisei ler dados de um excel com muitissimos dados.

public void dados() throws BiffException, IOException {
		Workbook workbook = Workbook.getWorkbook(new File("T:\\xande\\DadosVestibular2005.xls"));
		Sheet sheet = workbook.getSheet(0);
		StringBuilder sql = new StringBuilder();
		PrintWriter pw = new PrintWriter("T:\\xande\\insert-dados.sql");
		try {
			int linhas = sheet.getRows();
			for (int i = 0; i < linhas; i++) {
				
				System.out.println("Iterando linha: " + i);
				
				Cell celulaA = sheet.getCell(0, i);
				Cell celulaB = sheet.getCell(1, i);
				Cell celulaC = sheet.getCell(2, i);
				Cell celulaD = sheet.getCell(3, i);
				Cell celulaE = sheet.getCell(4, i);
				Cell celulaF = sheet.getCell(5, i);
				Cell celulaG = sheet.getCell(6, i);
				Cell celulaH = sheet.getCell(7, i);
				Cell celulaI = sheet.getCell(8, i);
				Cell celulaJ = sheet.getCell(9, i);
				Cell celulaK = sheet.getCell(10, i);
				Cell celulaL = sheet.getCell(11, i);
				Cell celulaM = sheet.getCell(12, i);
				Cell celulaN = sheet.getCell(13, i);
				Cell celulaO = sheet.getCell(14, i);
				Cell celulaP = sheet.getCell(15, i);
				Cell celulaQ = sheet.getCell(16, i);
				
				
				String conteudoA = celulaA.getContents();
				String conteudoB = celulaB.getContents();
				String conteudoC = celulaC.getContents();
				String conteudoD = celulaD.getContents();
				String conteudoE = celulaE.getContents();
				String conteudoF = celulaF.getContents();
				String conteudoG = celulaG.getContents();
				String conteudoH = celulaH.getContents();
				String conteudoI = celulaI.getContents();
				String conteudoJ = celulaJ.getContents();
				String conteudoK = celulaK.getContents();
				String conteudoL = celulaL.getContents();
				String conteudoM = celulaM.getContents();
				String conteudoN = celulaN.getContents();
				String conteudoO = celulaO.getContents();
				String conteudoP = celulaP.getContents();
				String conteudoQ = celulaQ.getContents();

				sql.append("INSERT INTO DadosVestibular2005(ID, sexo, ano_fim_2_grau, ano_nascimento, acertos_total, nome_curso, nome_area, acertos_biologia, acertos_geografia, acertos_matematica, acertos_lingua_estrangeira, acertos_portugues, acertos_redacao, acertos_fisica, acertos_historia, acertos_quimica, aprovado) values (");
				sql.append(conteudoA);
				sql.append(", ");
				sql.append(TextUtils.colocaAspasSimples(conteudoB));
				sql.append(", ");
				sql.append(conteudoC);
				sql.append(", ");
				sql.append(conteudoD);
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoE).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.colocaAspasSimples(conteudoF));
				sql.append(", ");
				sql.append(TextUtils.colocaAspasSimples(conteudoG));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoH).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoI).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoJ).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoK).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoL).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoM).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoN).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoO).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.returnValidNumber(conteudoP).replace(",", "."));
				sql.append(", ");
				sql.append(TextUtils.colocaAspasSimples(conteudoQ));
				sql.append(");");
				sql.append(TextUtils.NOVA_LINHA);
			}
			pw.write(sql.toString());
		} finally {
			workbook.close();
			pw.flush();
			pw.close();
		}
	}

Não é complexo, ao invés de você inserir o dado na mão, monte o INSERT e faça o append em um StringBuilder ou grave em um arquivo, algo do tipo, depois execute o script todo.

Bom, na minha humilde opinião …
eu estou tirando a informação de um csv, portanto se for pra mim tirar a informação de um csv e montar outro igual (com alguns campos a menos) só para poder inserir este outro csv de uma vez no BD não seria inviável ?
se eu montar um StringBuilder no java e não montar um outro csv para armazenar as informações não daria erro de estouro de pilha ?. devido ao tamanho dessa string que seria giganteeeeeesca

hamisterbr:

Bom, na minha humilde opinião …
eu estou tirando a informação de um csv, portanto se for pra mim tirar a informação de um csv e montar outro igual (com alguns campos a menos) só para poder inserir este outro csv de uma vez no BD não seria inviável ?
se eu montar um StringBuilder no java e não montar um outro csv para armazenar as informações não daria erro de estouro de pilha ?. devido ao tamanho dessa string que seria giganteeeeeesca

Você não vai montar outro CSV, você vai criar um INSERT para cada registro do seu CSV, simplesmente para poder inserir tudo diretamente no banco, e trabalhar daí pra frente.

Se você precisa usar o BD, eu acho que essa solução pode ser viável, cortando uma fase do seu processamento.

E claro, se isso for algo que precisa ser executado mais de uma vez, aí talvez não seja uma boa ideia.

Rodrigo Sasaki:

hamisterbr:

Bom, na minha humilde opinião …
eu estou tirando a informação de um csv, portanto se for pra mim tirar a informação de um csv e montar outro igual (com alguns campos a menos) só para poder inserir este outro csv de uma vez no BD não seria inviável ?
se eu montar um StringBuilder no java e não montar um outro csv para armazenar as informações não daria erro de estouro de pilha ?. devido ao tamanho dessa string que seria giganteeeeeesca

Você não vai montar outro CSV, você vai criar um INSERT para cada registro do seu CSV, simplesmente para poder inserir tudo diretamente no banco, e trabalhar daí pra frente.

Se você precisa usar o BD, eu acho que essa solução pode ser viável, cortando uma fase do seu processamento.

E claro, se isso for algo que precisa ser executado mais de uma vez, aí talvez não seja uma boa ideia.

entendi sua ideia Rodrigo Sasaki, mas o que estou querendo dizer é o seguinte … pra mim criar uma string “INSERT xxxx,xx,xx, INTO xx ;INSERT xxxx,xx,xx, INTO xx ;INSERT xxxx,xx,xx, INTO xx ;” para poder inserir todos dados de uma vez só não iria dar estouro de pilha ( Java heap space ) devido ao tamanho da string ?
esse processo não é mais ou menos o que o hibernate faz com o método persist da interface EntityManager ?
desculpa minha ignorância se estiver falando abobrinha kkkkk

hamisterbr:

entendi sua ideia Rodrigo Sasaki, mas o que estou querendo dizer é o seguinte … pra mim criar uma string “INSERT xxxx,xx,xx, INTO xx ;INSERT xxxx,xx,xx, INTO xx ;INSERT xxxx,xx,xx, INTO xx ;” para poder inserir todos dados de uma vez só não iria dar estouro de pilha ( Java heap space ) devido ao tamanho da string ?
esse processo não é mais ou menos o que o hibernate faz com o método persist da interface EntityManager ?
desculpa minha ignorância se estiver falando abobrinha kkkkk

Pode ser que dê sim, ou talvez não.

Mas nesse caso você pode quebrar o arquivo em partes, não precisa fazer tudo de uma vez, desde que execute todas as partes.

Pode tentar quebrar o arquivo em 4, e gerar 4 scripts.

Então tá, vou fazer isso que vc me disse, vou quebrar o arquivo em 4 ( ou mais ) e fazer um INSERT só …
dai posto aqui o resultado ;D

vlw Rodrigo Sasaki pelas dicas !

hamisterbr:

Então tá, vou fazer isso que vc me disse, vou quebrar o arquivo em 4 ( ou mais ) e fazer um INSERT só …
dai posto aqui o resultado ;D

vlw Rodrigo Sasaki pelas dicas !

Sem problemas, se tiver mais alguma dúvida é só perguntar. Vou ajudando com o que posso

Não sei de ferramento para o mysql, mas no sql server eu uso uma ferramento do sql management para importar arquivos grandes. No meu caso importei arquivos com mais de 100 mil registros em poucos segundos, muito mais eficiente do que gerar inserts individuais, mas essa minha proposta é mais manual.

é … neste caso eu teria que abrir um CSV, retirar os dados que preciso e montar outro CSV para fazer o import … isso fica meio inviável para este meu cenário =/

Neste caso não seria melhor fazer o insert em batch? Já pensou no que aconteceria se desse um erro no registro nº 25.000.000?

Só por curiosidade, você leu o tópico todo ?

Qual é o problema de se criar um CSV? É muito fácil, na verdade. Só é um pouco trabalhoso.

Veja se o seu banco requer o formato CSV ou requer algum outro formato - você precisa consultar a documentação do seu banco (procure por “bulk import” ou “bulk insert” ou “import” - não sei que banco você está usando).

Uma outra opção é usar o padrão Produtor-Consumidor.
A ideia é que uma thread irá ler o arquivo linha por linha. Cada linha será incluida numa fila ( um objeto BlockingQueue).
Um outro conjunto de threads irá ler essa fila. Cada thread irá gravar o item no banco. Se der erro, a linha é enviada para uma outra fila (fila de erros).
No final de ler o arquivo a thread principal espera pela fila ficar vazia e pela threads terminarem o processamento. Verifica se houve erros (linha na fila de erros) e avisa o usuário.
Eu tese ao particionar o trabalho vai mais depressa.
Se quiser pode criar grupos de linhas e por os grupos na fila em vez de linhas individuais. Assim vc pode usar os mecanismo de insersão em batch.

Trabalhar com threads pode ser complexo, então é melhor vc utiliza construtos mais modernos como os Executors, ou fork/join (vem no java 7, mas existe uma lib à parte se quiser usar no java 6)

sergiotaborda:

Uma outra opção é usar o padrão Produtor-Consumidor.
A ideia é que uma thread irá ler o arquivo linha por linha. Cada linha será incluida numa fila ( um objeto BlockingQueue).
Um outro conjunto de threads irá ler essa fila. Cada thread irá gravar o item no banco. Se der erro, a linha é enviada para uma outra fila (fila de erros).
No final de ler o arquivo a thread principal espera pela fila ficar vazia e pela threads terminarem o processamento. Verifica se houve erros (linha na fila de erros) e avisa o usuário.
Eu tese ao particionar o trabalho vai mais depressa.
Se quiser pode criar grupos de linhas e por os grupos na fila em vez de linhas individuais. Assim vc pode usar os mecanismo de insersão em batch.

Trabalhar com threads pode ser complexo, então é melhor vc utiliza construtos mais modernos como os Executors, ou fork/join (vem no java 7, mas existe uma lib à parte se quiser usar no java 6)

O grande problema de inserir muitos registros em um banco de dados usando a instrução INSERT (mesmo sendo um BATCH INSERT ou um BATCH UPDATE), em vez de usar um utilitário do banco de dados, é que isso costuma ser muito mais lento porque:

a) o INSERT normalmente participa de uma transação (implícita ou explicita) - ou seja, isso cria entradas no “journal” do banco de dados
b) Normalmente o INSERT também atualiza os índices da tabela (ou tabelas, se algum índice envolver “views” materializadas com múltiplas tabelas)

Para acelerar esse processo, normalmente costuma ser muito mais rápido importar os dados com o utilitário do banco, e talvez dropar os índices antes da importação e recriá-los depois da importação - isso depende muito do banco.

E é por isso que é mais simples criar um CSV (ou o formato que o banco exige para importar os dados) - pra começar, é bem mais fácil conferir se os dados que devem ser importados são realmente esses tendo um CSV, e de qualquer maneira, mesmo usando essa arquitetura com threads e o escambau, você precisa saber exatamente o que foi inserido (mesmo que seja um simples arquivo de log), para saber se não houve algum problema na transferência dos dados.

entanglement:

sergiotaborda:

Uma outra opção é usar o padrão Produtor-Consumidor.
A ideia é que uma thread irá ler o arquivo linha por linha. Cada linha será incluida numa fila ( um objeto BlockingQueue).
Um outro conjunto de threads irá ler essa fila. Cada thread irá gravar o item no banco. Se der erro, a linha é enviada para uma outra fila (fila de erros).
No final de ler o arquivo a thread principal espera pela fila ficar vazia e pela threads terminarem o processamento. Verifica se houve erros (linha na fila de erros) e avisa o usuário.
Eu tese ao particionar o trabalho vai mais depressa.
Se quiser pode criar grupos de linhas e por os grupos na fila em vez de linhas individuais. Assim vc pode usar os mecanismo de insersão em batch.

Trabalhar com threads pode ser complexo, então é melhor vc utiliza construtos mais modernos como os Executors, ou fork/join (vem no java 7, mas existe uma lib à parte se quiser usar no java 6)

O grande problema de inserir muitos registros em um banco de dados usando a instrução INSERT (mesmo sendo um BATCH INSERT ou um BATCH UPDATE), em vez de usar um utilitário do banco de dados, é que isso costuma ser muito mais lento porque:

a) o INSERT normalmente participa de uma transação (implícita ou explicita) - ou seja, isso cria entradas no “journal” do banco de dados
b) Normalmente o INSERT também atualiza os índices da tabela (ou tabelas, se algum índice envolver “views” materializadas com múltiplas tabelas)

Para acelerar esse processo, normalmente costuma ser muito mais rápido importar os dados com o utilitário do banco, e talvez dropar os índices antes da importação e recriá-los depois da importação - isso depende muito do banco.

E é por isso que é mais simples criar um CSV (ou o formato que o banco exige para importar os dados) - pra começar, é bem mais fácil conferir se os dados que devem ser importados são realmente esses tendo um CSV, e de qualquer maneira, mesmo usando essa arquitetura com threads e o escambau, você precisa saber exatamente o que foi inserido (mesmo que seja um simples arquivo de log), para saber se não houve algum problema na transferência dos dados.

Gostei muito da solução dada pelo “sergiotaborda” ! mas pelo jeito vou ter que estudar um pouco mais sobre threads
mas pelo que disse o “entanglement” a melhor solução seria mesmo, ler o csv, criar outro com as informações que eu quero e ai então importar manualmente esse outro csv para o BD …
sem frescura, simples e fácil

sergiotaborda:

Uma outra opção é usar o padrão Produtor-Consumidor.
A ideia é que uma thread irá ler o arquivo linha por linha. Cada linha será incluida numa fila ( um objeto BlockingQueue).
Um outro conjunto de threads irá ler essa fila. Cada thread irá gravar o item no banco. Se der erro, a linha é enviada para uma outra fila (fila de erros).
No final de ler o arquivo a thread principal espera pela fila ficar vazia e pela threads terminarem o processamento. Verifica se houve erros (linha na fila de erros) e avisa o usuário.
Eu tese ao particionar o trabalho vai mais depressa.
Se quiser pode criar grupos de linhas e por os grupos na fila em vez de linhas individuais. Assim vc pode usar os mecanismo de insersão em batch.

Trabalhar com threads pode ser complexo, então é melhor vc utiliza construtos mais modernos como os Executors, ou fork/join (vem no java 7, mas existe uma lib à parte se quiser usar no java 6)

eu ia sugerir justamente isso se a ideia é performance, a ideia é ir inserindo ao mesmo tempo que vai sendo lido, sem o processo leitura aguardar o de inserção na base, de repente seja mais lento do queimportar usando a própria ferramenta do banco de dados, mas seria mais rápido do que criar o arquivo no formato a ser usado para a importação e após o termino disso, importá-lo.
você precisaria provavelmente de um número á ser usado para quantidade de comandos por transação, por exemplo, a cada 1000 comandos dar um commit ou rollback, 1000000, sei la… se for muito, muito grande, vai estourar uma área no banco que eele usa para guardar estas alterações…

se houver algum dba responsável pela base, converse com ele quanto a qual a melhor forma de fazer isso visando desempenho, o que é possível fazer com os indices e coisa assim, se for só uma pq de um campo numérico pro exemplo, nem sei se valeria a pena.

Ok … alguém tem um material para mim dar uma estudada de como implementar a solução (Produtor-Consumidor) dada pelo nosso amigo “sergiotaborda” ?

índio desenvolve em Java agora?

eu ia sugerir justamente isso se a ideia é performance, a ideia é ir inserindo ao mesmo tempo que vai sendo lido, sem o processo leitura aguardar o de inserção na base, de repente seja mais lento do queimportar usando a própria ferramenta do banco de dados, mas seria mais rápido do que criar o arquivo no formato a ser usado para a importação e após o termino disso, importá-lo.
você precisaria provavelmente de um número á ser usado para quantidade de comandos por transação, por exemplo, a cada 1000 comandos dar um commit ou rollback, 1000000, sei la… se for muito, muito grande, vai estourar uma área no banco que eele usa para guardar estas alterações…

A ideia é que a thread consumidora dê commit a cada linha que recebe da queue. Se usar grupos de linhas como falei ( mais o batch ) você pode limitar o numero de comits no batch pelo numero de linhas no grupo.

O conceito de produtor consumidor também pode ser usado se o objetivo for transformar um arquivo CSV em outro. A ideias das threada é a mesma mas funcionaria um pouco diferente.
A thread pordutora iria fazer o mesmo, ler as linhas. As threads consumidoras iriam trasnformas esas linhas em outra informação ( um DTO A que contém os dados do primeiro arquivo, para um DTO B que contém os dado do segundo). A thread consumidora então agiria como produtora de um novo tipo de objeto. Essa nova linha seria enviada para outra queue. Se a linha foi rejeitada por alguma razão, simplesmente não coloca na queue. Uma terceira thread iria ler essa queue e escrever os dados no arquivo final.
A primeira e terceria thread são únicas (uma unica instancia correndo). As threads transformadores podem ser N.

Produtor (Leitor do arquivo) --> Queue A —> { conjunto de threads transformadoras } —> Queue B --> Consumidor final (Escritor do Arquivo)

Este processo em que A vira uma coleção de B e depois é tudo aggreagado em C é mais comum do que parece. hoje em dia é comum chamar este tipo de algoritmos de MapReduce (devido ao nome do algoritmo do google) ou de Fork/join como o framework do java).

Sim, é um pouco avançado isto aqui. Mas é muito mais facil no java 6/7 do que no java 4 hoje existem classes como Executor, BlockingQueue que escondem o uso de threads

Passar de um CSV para outro, ou até para um script SQL é uma opção, mas ai vc está desenhando uma ferramenta de linha de comandos e não um sistema que tem a inteligencia integrada. às vezes a transformação e filtro.
Contudo, se essa for a escolha o padrão Produtor-Consumidor pode ajudar também.