Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: faster with threads?

by Ryszard (Priest)
on Jun 09, 2004 at 12:27 UTC ( #362699=note: print w/ replies, xml ) Need Help??

in reply to faster with threads?

Updates are slow, in my experience (at least with Oracle) putting data in a database using inserts is way slow.

If i were to build your system there are a couple of things i would do:

  1. Research on what you think is the best item.
  2. Develop prototypes for all appropriate options
  3. Run the prototypes against a limited data set
  4. Extrapolate the timings
  5. Develop further the winner.

Comment on Re: faster with threads?
Replies are listed 'Best First'.
Re^2: faster with threads?
by jaa (Friar) on Jun 09, 2004 at 13:03 UTC

    Use the mysql bulk insert statement format if possible.

    We found that using the traditional prepare/execute cycle was about 10 times slower to insert 2 million records, than using several bulk sql statements of about 500K.

    my @rows = ( [qw(a b c d)], [qw(e f g h)], [qw(i j k l)], [qw(m n o p)], [qw(q r s t)], [qw(u v w x)], [qw(y z a b)], [qw(c d e f)], [qw(h i j k)], [qw(l m n o)], ); # ------------------------------------- # slower # ------------------------------------- my $sth = $db->prepare("INSERT INTO mytable VALUES(?,?,?,?)") or die "prepare failed: " . $db->errstr; for my $row ( @rows ) { $sth->execute(@$row); } # ------------------------------------- # faster # ------------------------------------- my $sql = "INSERT INTO mytable VALUES "; for my $row ( @rows ) { $sql .= "('" . join("','",@$row) . '),"; } substr($sql,-1)=''; $db->do( $sql );
      And if using a bulk insert isn't possible for some reason, first drop the indices on the table(s) involved, insert your data, then rebuild the indices. Unless the tables already contain billions of rows, rebuilding the indices is faster than modifying them millions of times.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://362699]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (9)
As of 2015-11-25 10:47 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (673 votes), past polls