• Login

Mathias Grimm - beta

Sede pelo conhecimento

• Home
• Sobre

BuscaPé, líder em comparação de preços na América Latina

POSTGRESQL : Prepare X Query X COPY para importação de csv

Posted by Mathias Grimm on agosto 16, 2008 in PHP

Bom, este é um artigo simple, apenas para manter o blog “atualizado” enquanto preparo algo mais interessante.

Tenho um arquivo csv com um milhão de linhas e preciso inserir todas linhas no banco de dados.
Poderia fazer isso de 2 formas: COPY do postgresql ou criando um script (php por exemplo) e ler o arquivo gerando um comando insert para cada linha. No script php temos como inserir os registros usando uma query passando o insert como argumento ou também podemos usar prepared statements (querys preparadas). Fiz alguns testes aqui com um arquivo de 100 mil linhas e o php estava demorando cerca de 1:20 ( um minuto e vinte segundos) com ligeira melhora de perfomance quando usado com prepare statements. Achei muito demorado e então fui dar uma lida na documentação do postgresql. Cheguei a fazer uma pgplsql que abria o arquivo e tal, mas a manipulação não tava muito fácil. Foi então que vi o quanto a função COPY do postgresql é útil e eficiente. Esse mesmo arquivo de 100 mil linhas foi importado em 12 segundos, ou seja, 1000% mais rápido, ou 10 vezes mais rápido!!! impressionante! Quando executei o comando para o arquivo de 1 milhão de linhas é que fiquei mais surpreso ainda... “Query returned successfully with no result in 26597 ms.” ou seja 26,5 segundos... para 1 milhão de linhas, lembrando que o script php inseriu 100 mil linhas em 1:20 s (um minuto e vinte segundos).
Algo a se considerar é o fato de que para validar os dados utilizando o metodo COPY é necessário criar uma trigger.. enquanto no php, no scrip mesmo você pode validar mais facilmente.

Na verda percebi uma coisa agora enquanto revisava o artigo. O comando de insert do php era gerado na hora, ou seja, nem dava um fopen no arquivo nem nada... o que com certeza
deixaria MUITO mais lento o script php.

Bom... o comando copy é simples:
COPY usuario FROM '/var/lib/postgresql/8.2/main/mathias.csv' WITH DELIMITER ',' .

usuario é a tabela na qual vou inserir os dados
/var/lib/postgresql/8.2/main/mathias.csv é o caminho do meu arquivo
WITH DELIMITER ',' especifica que o delimitador do arquivo é a vírgula. Poderia ter sido usado | (pipe) ou algum outro como ; (ponto e vírgula).

Esse comando COPY pode ser executado no pgadmin3 (meu caso) ou (eu acho, não testei) ser executado via query,psql, phppgadmin e por aí vai...

segue abaixo os códigos php que utilizei para gerar o arquivo csv de teste e os dois scripts para inserir, caso alguem queira testar.

1. Script que gera o csv com 1 milao de linhas.

<?php
	for($i=1;$i<=1000000;$i++)
	{
		file_put_contents
                (
                    '/var/lib/postgresql/8.2/main/mathias.csv',
                    "$i,mathiasgrimm,a@b.c,c4ca4238a0b923820dcc509a6f75849b\n",
                    FILE_APPEND
               );
	}
?>

2.Script de insert com Prepared Statement

<?php
	$con = pg_connect
                    (
                        "
                            host=localhost
                            port=5432
                            dbname=mathiasgrimm.com.br
                            user=postgres
                            password=postgres
                       "
                   );
	$result = pg_prepare
                        (
                            $con,
                            "my_query",
                            'INSERT INTO USUARIO (NM_USUARIO,TE_EMAIL,TE_PASSWORD) VALUES($1,$2,$3)'
                        );
 
	for($i=1;$i<=1000000;$i++)
	{
		$result = pg_execute
                                (
                                    $con,
                                    "my_query",
                                    array
                                    (
                                        "mathiasgrimm",
                                        "a@b.c",
                                       "c4ca4238a0b923820dcc509a6f75849b"
                                   )
                               );
	}
?>

3.Script de insert com query simples

<?php
	$con = pg_connect
                    (
                        "
                            host=localhost
                            port=5432
                            dbname=mathiasgrimm.com.br
                            user=postgres
                            password=postgres
                       "
                    );
 
	for($i=0;$i<1000000;$i++)
	{
		$result = pg_query
                                (
                                    $con ,
                                    'INSERT INTO
                                         USUARIO
                                         (
                                             NM_USUARIO,
                                             TE_EMAIL,
                                             TE_PASSWORD
                                         )
                                         VALUES
                                         (
                                             \'mathiasgrimm\',
                                             \'a@b.c\',\'c4ca4238a0b923820dcc509a6f75849b\'
                                          )'
                                );
	}
?>

Obs.: Os testes que fiz utilizei o php-cli ("php -f insert.php" e php -f insertps.php"), e não via browser.

A intensão do artigo não é mostrar se insert com prepared statement é mais rápida do que utilizando query, mas talvez seja um tema interessante para ser escrito em outro artigo.

Como sempre meus artigos são feitos meio na correria, desculpem alguma besteira que possa ter falado, e se possível contribuam com idéias para que eu possa estar escrevendo com mais frequencia.

Como de costume, clique aqui para baixar os arquivos utilizados.

Obrigado a todos e voltem sempre!