Re: [GENERAL] Postgres INSERTs much slower than MySQL?

Try turning off Autocommit: MySQL doesn't support transactions, so that
might be what's causing the speed boost.   Just change the connect line from:
$pg_con=DBI->connect("DBI:Pg:....
to
$pg_con=DBI->connect("DBI:Pg(AutoCommit=>0):....

and add 

$pg_con->commit

before you disconnect.  I may have the syntax wrong, so double check the
docs for the DBI and PG modules (perldoc DBD::Pg and perldoc DBI)

At 01:25 AM 10/20/99, Lincoln Yeoh wrote:
>Hi everyone,
>
>Should inserts be so slow?
>
>I've written a perl script to insert 10 million records for testing
>purposes and it looks like it's going to take a LONG time with postgres.
>MySQL is about 150 times faster! I don't have any indexes on either. I am
>using the DBI and relevant DBD for both.
>
>For Postgres 6.5.2 it's slow with either of the following table structures.
>create table central ( counter serial, number varchar (12), name text,
>address text );
>create table central ( counter serial, number varchar (12), name
>varchar(80), address varchar(80));
>
>For MySQL I used:
>create table central (counter int not null auto_increment primary key,
>number varchar(12), name varchar(80), address varchar(80));
>
>The relevant perl portion is (same for both):
>		$SQL=<<"EOT";
>insert into central (number,name,address) values (?,?,?)
>EOT
>		$cursor=$dbh->prepare($SQL);
>
>	while ($c<10000000) {
>		$number=$c;
>		$name="John Doe the number ".$c;
>		$address="$c, Jalan SS$c/$c, Petaling Jaya";
>		$rv=$cursor->execute($number,$name,$address) or die("Error executing
>insert!",$DBI::errstr);
>		if ($rv==0) {
>			die("Error inserting a record with database!",$DBI::errstr);
>		};
>		$c++;
>		$d++;
>		if ($d>1000) {
>			print "$c\n";
>			$d=1;
>		}
>	}
>
>
>
>************
>