From: | Charles Tassell <ctassell(at)isn(dot)net> |
---|---|
To: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com>, pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Postgres INSERTs much slower than MySQL? |
Date: | 1999-10-20 05:56:56 |
Message-ID: | 4.1.19991020025131.009949b0@mailer.isn.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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;
> }
> }
>
>
>
>************
>
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 1999-10-20 07:38:12 | Re: [GENERAL] Postgres INSERTs much slower than MySQL? |
Previous Message | Lincoln Yeoh | 1999-10-20 04:25:50 | Re: [GENERAL] Postgres INSERTs much slower than MySQL? |
From | Date | Subject | |
---|---|---|---|
Next Message | Gene Sokolov | 1999-10-20 06:09:58 | Creating operators |
Previous Message | Tom Lane | 1999-10-20 05:47:36 | Re: [HACKERS] Readline use in trouble? |