MySQL is not a flat file, it is a relational database. It is good at things that relational databases do well, such as retrieving related rows from multiple tables at once.

Flat files are not relational databases. They are good at things such as writing data sequentially to the end of a table or retrieving data with ad-hoc structure.

Don't use flat files when you need a relational database; you'll spend endless time implementing things that you can have for free by using the right data manager. Similarly, avoid making your relational database stand in for a simple flat file unless you will need relational capabilities on the data stored in the file later.

Thank you for saying what I wanted to say but couldn't form into a coherent bit of text.

Alot of users do NOT realize the truth of what you say, myself included when I first discovered Mysql and other relational databases. Without your addendum I fear my post may have helped confuse such people more than it helped.

Thanks, James

MySQL doesn't do subqueries, nor does it implement commit/rollback (and a host of other features, but I sure would miss these two were I required to use it).

It's great if you consider it as flatfiles-on-steroids, which is what the gist of this benchmark seems to be about. On the other hand, if you're looking at MySQL as an Oracle-on-the cheap, then there's a good article on why that is a Bad Idea.

PosgreSQL forever!


--
g r i n d e r

Definitely not a surprise - PSV (pipe-separated) files are definitely easier to generate/parse than CSV files (and you don't seem to be dealing with the problem of escaping pipes within your data). A more interesting comparison would be to attempt a 'join' between two extremely large PSV files, and compare that to MySql. I'll bet MySql will give you a better run for the money, and memory usage would be better.

We benchmarked Text::CSV a long time ago at my last job, as we were getting a lot of CSV files from the outside world. It's performance (topped out at 12-14K rows/minute on a p2-400 with 512M) caused us to re-architect our software to minimize the CSV data we were working on. It was quite a surprise at the time, but in retrospect, parsing CSV data can be quite problematic (especially when the damn users don't follow the rules!)

Definitely not a surprise - PSV (pipe-separated) files are definitely easier to generate/parse than CSV files

Until you have to deal with a pipe in your data. Then, there's no difference.

-- Randal L. Schwartz, Perl hacker

Dropping Text::CSV was a good idea. If your data had any returns in them, it would not have handled that.

I believe it is slow, but I wouldn't have minded seeing Text::xSV in the above comparison because it does handle that...

I agree with TheoPeterson's points, and I will add on to warn that there are more important things than speed:

• MySQL (or other relational databases) usually hold up better when you start increasing the number of records in your tables. Flat files with 1000s of records starts to become very very slow. MySQL can hold up quite well even when you get into 5 digits+. (There have been projects where I've used text files thinking that there'd only be a couple hundred records. Several years later, let's just say that the client exceeded my expectations.)

• MySQL is a lot easier to work with when your tables need to mutate. If your client decides that they want to add/drop fields, then it's easier to do using a few ALTER statements, rather than hacking scripts together to manually rearrange your database tables. It's better during the development process as well.

• With flat files you have to be a lot more careful about file locking and data integrity. That's not as much of a concern with most RDBMs.

• With flat files you have to be a lot more careful about security. With MySQL, etc., you have a lot of flexibility, and can assign access privledges to each action (SELECT,INSERT,DELETE,etc.).

...and so on. I know that you weren't using your benchmarks to start a war about MySQL vs flat files, but you did mention that you were looking for the "best way". In my humble opinion, the fastest way isn't the best way in this case. For a lot of projects, messing with flat files or CSVs are just too much of a headache.

It is good to see the benchmarks though. Thanks for taking the initiative!

First, your method of inserting into the mysql DB is the SLOWEST of the three options:

Benchmark: timing 500 iterations of dbh_insert, dbh_insert_values, sth
+_insert...
dbh_insert: 28 wallclock secs ( 5.99 usr +  1.68 sys =  7.67 CPU) @ 65
+.19/s (n=500)
dbh_insert_values: 27 wallclock secs ( 6.02 usr +  1.82 sys =  7.84 CP
+U) @ 63.78/s (n=500)
sth_insert: 59 wallclock secs (28.48 usr +  2.41 sys = 30.89 CPU) @ 16
+.19/s (n=500)
[download]

I used your database format with a small change of adding an index on ID which slows down the inserts.

For those curious, here are the BM stats of the three insert formats without an index on ID.

Benchmark: timing 500 iterations of dbh_insert, dbh_insert_values, sth
+_insert...
dbh_insert: 20 wallclock secs ( 5.30 usr +  1.41 sys =  6.71 CPU) @ 74
+.52/s (n=500)
dbh_insert_values: 23 wallclock secs ( 5.62 usr +  1.45 sys =  7.07 CP
+U) @ 70.72/s (n=500)
sth_insert: 55 wallclock secs (27.53 usr +  2.50 sys = 30.03 CPU) @ 16
+.65/s (n=500)
[download]

Second problem is the actual import subroutines.

Your import routine is skewed in favor of flatfile since it is simulating a mass import, not periodic imports.

Your code:

sub insert_flatfile {
        # Note: files opened in > mode again to prevent disk space iss
+ues

        open(FF, ">$flatfile") || die "Unable to open '$flatfile': $!"
+;
        foreach(@rows) {
                print FF join('|', ($_->{ID}, $_->{name}, $_->{address
+}, $_->{city}, $_->{state}, $_->{zip})), "\n";
        }
        close(FF);
}
[download]

Now, to simulate a production enviorment it should be more like this:

sub insert_flatfile {
        # Note: files opened in > mode again to prevent disk space iss
+ues

        open(FF, ">$flatfile") || die "Unable to open '$flatfile': $!"
+;
        close(FF);
        foreach(@rows) {
                open(FF, ">>$flatfile") || die "Unable to open '$flatf
+ile': $!";
                flock(FF,LOCK_EX);
                print FF join('|', ($_->{ID}, $_->{name}, $_->{address
+}, $_->{city}, $_->{state}, $_->{zip})), "\n";
                flock(FF,LOCK_UN);
                close(FF);
        }

}
[download]

Note: There is still a problem with this routine since in a production enviorment, you would either have to read the flatfile to find out the next ID to store ( or another file where you store the next id ) while MySQL could use an auto_increment on the ID column. But for this benchmarks needs its really overkill.

Note2:If simulating the mass importation of data, MySQL can be greatly speed up using

insert into table (blah,blah2,blah3) values ('vbla1','vbla2','vbla3'),
('vbla1b','vbla2b','vbla3b'),
ect...
[download]

Note3: Depending on how the data is used.. weither it would need to be instantly retrieved or not insert delayed can be used which makes the inserting APPEAR to be near instantanious.

Here are my results for the benchmarks using my 2 changes WITHOUT an index on ID

Benchmark: timing 500 iterations of Flatfile_Insert, Mysql_Insert...
Flatfile_Insert:  8 wallclock secs ( 4.29 usr +  3.30 sys =  7.59 CPU)
+ @ 65.88/s (n=500)
Mysql_Insert: 24 wallclock secs ( 5.32 usr +  1.30 sys =  6.62 CPU) @ 
+75.53/s (n=500)
========================================

Benchmark: timing 500 iterations of Flatfile_Select, Mysql_Select...
Flatfile_Select: 15 wallclock secs ( 7.99 usr +  5.81 sys = 13.80 CPU)
+ @ 36.23/s (n=500)
Mysql_Select: 127 wallclock secs (35.89 usr +  4.20 sys = 40.09 CPU) @
+ 12.47/s (n=500)
========================================
 
Benchmark: timing 500 iterations of Flatfile_Update, Mysql_Update...
Flatfile_Update: 175 wallclock secs (146.59 usr + 24.77 sys = 171.36 C
+PU) @  2.92/s (n=500)
Mysql_Update: 189 wallclock secs (10.51 usr +  1.92 sys = 12.43 CPU) @
+ 40.23/s (n=500)
========================================
 
We can only do 1 iteration of the delete because it necessarily destro
+ys our datasource.
Benchmark: timing 1 iterations of Flatfile_Delete, Mysql_Delete...
Flatfile_Delete:  0 wallclock secs ( 0.17 usr +  0.01 sys =  0.18 CPU)
+ @  5.56/s (n=1)
            (warning: too few iterations for a reliable count)
Mysql_Delete:  0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
            (warning: too few iterations for a reliable count)
[download]

And now with an index on ID:

Benchmark: timing 500 iterations of Flatfile_Insert, Mysql_Insert...
Flatfile_Insert:  8 wallclock secs ( 4.02 usr +  3.36 sys =  7.38 CPU)
+ @ 67.75/s (n=500)
Mysql_Insert: 26 wallclock secs ( 5.74 usr +  1.93 sys =  7.67 CPU) @ 
+65.19/s (n=500)
========================================
 
Benchmark: timing 500 iterations of Flatfile_Select, Mysql_Select...
Flatfile_Select: 14 wallclock secs ( 8.25 usr +  5.39 sys = 13.64 CPU)
+ @ 36.66/s (n=500)
Mysql_Select: 85 wallclock secs (34.61 usr +  5.33 sys = 39.94 CPU) @ 
+12.52/s (n=500)
========================================
 
Benchmark: timing 500 iterations of Flatfile_Update, Mysql_Update...
Flatfile_Update: 179 wallclock secs (147.56 usr + 25.98 sys = 173.54 C
+PU) @  2.88/s (n=500)
Mysql_Update: 37 wallclock secs (10.73 usr +  2.33 sys = 13.06 CPU) @ 
+38.28/s (n=500)
========================================
 
We can only do 1 iteration of the delete because it necessarily destro
+ys our datasource.
Benchmark: timing 1 iterations of Flatfile_Delete, Mysql_Delete...
Flatfile_Delete:  0 wallclock secs ( 0.13 usr +  0.04 sys =  0.17 CPU)
+ @  5.88/s (n=1)
            (warning: too few iterations for a reliable count)
Mysql_Delete:  0 wallclock secs ( 0.01 usr +  0.01 sys =  0.02 CPU) @ 
+50.00/s (n=1)
            (warning: too few iterations for a reliable count)
[download]

Basically your selecting each row once , just in a random order. A better way to simulate a production enviorment would be to crank up the iterations ( $number_of_rows * 500 for example ) and have the subroutine pick a random row to select from. $a = int(rand($number_of_rows))

Pete
insert into pete values('red hair','near green eyes','overinflated ego');