Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: Which is quicker - writing to a file or inserting into a db?

by naChoZ (Curate)
on Dec 14, 2007 at 03:37 UTC ( #656977=note: print w/ replies, xml ) Need Help??


in reply to Which is quicker - writing to a file or inserting into a db?

I recently did a poor man's benchmark just for kicks. The results might at least be interesting to you.

I wrote this little script to populate a db with 50,000 rows into a db table of three columns (one just an autoinc id).

#!/usr/bin/perl use strict; use warnings; use Data::Dumper; $Data::Dumper::Indent = 3; use Digest::MD5 qw/md5_base64/; use base qw/DBIx::Class::Schema::Loader/; DBIx::Class::Schema::Loader->loader_options( relationships => 1 ); my $schema = DBIx::Class::Schema::Loader->connect('dbi:SQLite:/tmp/tes +t'); #my $schema = DBIx::Class::Schema::Loader->connect('dbi:Pg:dbname=test +', 'postgres', '' ); #my $schema = DBIx::Class::Schema::Loader->connect('dbi:mysql:dbname=t +est', 'mysql', '' ); my @inserts; for ( 1 .. 50000 ) { push @inserts, { foo => md5_base64( $_ . 'foo' ), bar => md5_base64( $_ . 'bar' ) }; } my $new_row = $schema->resultset('TestTable'); $new_row->populate( \@inserts );

Loading to postgresql-server-8.1.9-2.1 took just under 8 minutes, mysql-5.0.26-12 about 35 seconds, sqlite-3.3.8-14 even with synchronous mode turned off couldn't even seem to handle 1000 rows a minute (I didn't wait for it to finish all 50,000 rows). I reused this script just now for writing 50,000 lines to a flat text file and it was done almost before I pressed enter.

Obviously YMMV. My workstation is a sun ultra 40 dual opteron with a pair of sata drives that I striped into a raid 0 and I'm running opensuse 10.2.

--
naChoZ

Therapy is expensive. Popping bubble wrap is cheap. You choose.


Comment on Re: Which is quicker - writing to a file or inserting into a db?
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (2)
As of 2015-07-06 04:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (70 votes), past polls