[16197] in Perl-Users-Digest
Perl-Users Digest, Issue: 3609 Volume: 9
daemon@ATHENA.MIT.EDU (Perl-Users Digest)
Mon Jul 10 20:04:23 2000
Date: Mon, 10 Jul 2000 17:04:12 -0700 (PDT)
From: Perl-Users Digest <Perl-Users-Request@ruby.OCE.ORST.EDU>
To: Perl-Users@ruby.OCE.ORST.EDU (Perl-Users Digest)
Message-Id: <963273852-v9-i3609@ruby.oce.orst.edu>
Content-Type: text
Perl-Users Digest Mon, 10 Jul 2000 Volume: 9 Number: 3609
Today's topics:
Re: Unix database for Perl (Abigail)
Re: Unix database for Perl <bt@nospam.com.>
Re: Unix database for Perl (Jerome O'Neil)
Re: Unix database for Perl (Abigail)
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl (Abigail)
Re: Unix database for Perl (Bart Lateur)
Re: Unix database for Perl <gellyfish@gellyfish.com>
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl (Abigail)
Re: Unix database for Perl (Bart Lateur)
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl <bet@rahul.net>
Re: Unix database for Perl <gellyfish@gellyfish.com>
Re: Unix database for Perl <gellyfish@gellyfish.com>
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl <thunderbear@bigfoot.com>
Re: Unix database for Perl <gellyfish@gellyfish.com>
Re: Unix database for Perl <gellyfish@gellyfish.com>
Re: Unix database for Perl <thunderbear@bigfoot.com>
Digest Administrivia (Last modified: 16 Sep 99) (Perl-Users-Digest Admin)
----------------------------------------------------------------------
Date: 03 Jul 2000 18:11:27 EDT
From: abigail@delanet.com (Abigail)
Subject: Re: Unix database for Perl
Message-Id: <slrn8m24u3.59a.abigail@alexandra.delanet.com>
Bennett Todd (bet@rahul.net) wrote on MMCDXCVII September MCMXCIII in
<URL:news:20000702111558.A471@oven.com>:
''
'' Some database systems have a lot of functionality beyond the bare
'' minimum common feature set shared by pretty much all of them. And
'' some of _those_ have special database-specific access libraries that
'' make some or all of that extra functionality available to Perl.
Having direct access to the vendor supplied client library has another
benefit. While at the cost of losing a lot of functionality, using DBI
makes your life easier if you're replacing the server end. But using
vendor supplied client libraries makes your life easier if you're
replacing your Perl implementation with a C or C++ implementation.
'' If on the other hand you want to confine your perl programming to
'' features that are common to most all databases, to make it as easy
'' as possible to switch out one database system for another with
'' minimal change to your code, then you want to be looking at DBI. And
'' in _that_ case, MySQL is distinctly likely to be the nicest choice
'' out there: it has a reasonably rich query language, and does a
'' nice job of covering the common features shared by all database
'' systems. If you're doing your design from the ground up, MySQL can
'' work great.
Perhaps MySQL covers all the features shared by all database systems,
it certainly misses features I'd call essential for any good database
system. It doesn't even have transactions. It doesn't have triggers
or stored procedures. It doesn't have referential integreties. It
doesn't have nested queries. The documentation (which is tiny, tiny,
tiny compared to say, Sybase), comes with the excuse you don't need that
if your programs are properly coded. That would be on par with the Camel
saying you don't need '-w', '-T', 'use strict;' or ever check the return
values of system calls - all you need is to code well!
Someone once said: "Top 3 reasons MySQL is popular: it doesn't cost
anything, it's fast, and people have no clue what relational databases
are."
I'm sure MySQL works well if all you want is glorified DBM files, with
some kind of query language on top of it. For anything else, MySQL
just doesn't cut it.
Abigail
--
perl5.004 -wMMath::BigInt -e'$^V=Math::BigInt->new(qq]$^F$^W783$[$%9889$^F47]
.qq]$|88768$^W596577669$%$^W5$^F3364$[$^W$^F$|838747$[8889739$%$|$^F673$%$^W]
.qq]98$^F76777$=56]);$^U=substr($]=>$|=>5)*(q.25..($^W=@^V))=>do{print+chr$^V
%$^U;$^V/=$^U}while$^V!=$^W'
------------------------------
Date: Tue, 04 Jul 2000 07:06:07 +0800
From: Brian <bt@nospam.com.>
Subject: Re: Unix database for Perl
Message-Id: <rs62mscc0d5ucccb1fn3ck7p8l20f1936q@4ax.com>
On 03 Jul 2000 18:11:27 EDT, abigail@delanet.com (Abigail) wrote:
>Bennett Todd (bet@rahul.net) wrote on MMCDXCVII September MCMXCIII in
><URL:news:20000702111558.A471@oven.com>:
[deleted for brevity]
>Someone once said: "Top 3 reasons MySQL is popular: it doesn't cost
>anything, it's fast, and people have no clue what relational databases
>are."
>
>I'm sure MySQL works well if all you want is glorified DBM files, with
>some kind of query language on top of it. For anything else, MySQL
>just doesn't cut it.
I have heard this oppinion before also. But the question remains, what is
the best alternative for low-medium DB requirements? When there is no budget
for the expensive commercial databases?
Brian.
------------------------------
Date: Tue, 04 Jul 2000 01:36:18 GMT
From: jerome@activeindexing.com (Jerome O'Neil)
Subject: Re: Unix database for Perl
Message-Id: <mcb85.476$_x3.282149@news.uswest.net>
Brian <bt@nospam.com.> elucidates:
> I have heard this oppinion before also. But the question remains, what is
> the best alternative for low-medium DB requirements? When there is no budget
> for the expensive commercial databases?
postgres. It's full featured, and free.
------------------------------
Date: 03 Jul 2000 23:39:03 EDT
From: abigail@delanet.com (Abigail)
Subject: Re: Unix database for Perl
Message-Id: <slrn8m2o4c.59a.abigail@alexandra.delanet.com>
Brian (bt@nospam.com.) wrote on MMCDXCVIII September MCMXCIII in
<URL:news:rs62mscc0d5ucccb1fn3ck7p8l20f1936q@4ax.com>:
~~ On 03 Jul 2000 18:11:27 EDT, abigail@delanet.com (Abigail) wrote:
~~
~~ >Bennett Todd (bet@rahul.net) wrote on MMCDXCVII September MCMXCIII in
~~ ><URL:news:20000702111558.A471@oven.com>:
~~
~~ [deleted for brevity]
~~
~~ >Someone once said: "Top 3 reasons MySQL is popular: it doesn't cost
~~ >anything, it's fast, and people have no clue what relational databases
~~ >are."
~~ >
~~ >I'm sure MySQL works well if all you want is glorified DBM files, with
~~ >some kind of query language on top of it. For anything else, MySQL
~~ >just doesn't cut it.
~~
~~ I have heard this oppinion before also. But the question remains, what is
~~ the best alternative for low-medium DB requirements? When there is no budget
~~ for the expensive commercial databases?
I'm not sure what you mean by "low-medium DB requirements". In my
book, MySQL fails the essential requirements. MySQL just isn't an
alternative. ;-)
I'm not sure if the question "when there is no budget for the expensive
commercial databases" has an answer you want to hear. I haven't use
postgres, but from what I've gathered is that is does have a full feature
set, but isn't in the same ballpark as Oracle, Sybase or DB2 when it
comes to performance. Perhaps the right answer is "if there's no budget,
use/make something else."
What would you do if there's no budget for computers or desks?
Abigail
--
$_ = "\x3C\x3C\x45\x4F\x54\n" and s/<<EOT/<<EOT/ee and print;
"Just another Perl Hacker"
EOT
------------------------------
Date: Tue, 04 Jul 2000 09:54:38 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <3961983E.F9E78584@bigfoot.com>
Abigail wrote:
> ~~ I have heard this oppinion before also. But the question remains, what is
> ~~ the best alternative for low-medium DB requirements? When there is no budget
> ~~ for the expensive commercial databases?
>
> I'm not sure what you mean by "low-medium DB requirements". In my
> book, MySQL fails the essential requirements. MySQL just isn't an
> alternative. ;-)
I disagree (somewhat).
MySQL is a good database to start with, _because_ it is
simple.
If your application needs more than simple input and simple
output, you will need to upgrade, but the experience you've
had with MySQL makes you more knowledgeable about databases,
and will make you more capable of making the right choice
for what you need. Additionally the DBI drivers are good.
After working with MySQL for 6 months, I hit the "no
sub-select" restriction, and then I knew what I would need
from the replacement. Currently I use Oracle, which can do
what I need. IBM has a evaluation copy of DB2 for Linux
freely available from
http://www-4.ibm.com/software/data/db2/udb/downloads.html
Personally I would still use MySQL for webserver purposes
where all you care about is data in and data out _FAST_.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: 04 Jul 2000 12:14:14 EDT
From: abigail@delanet.com (Abigail)
Subject: Re: Unix database for Perl
Message-Id: <slrn8m44cc.59a.abigail@alexandra.delanet.com>
Thorbjørn Ravn Andersen (thunderbear@bigfoot.com) wrote on MMCDXCIX
September MCMXCIII in <URL:news:3961983E.F9E78584@bigfoot.com>:
;; Abigail wrote:
;;
;; > ~~ I have heard this oppinion before also. But the question remains, what
;; > ~~ the best alternative for low-medium DB requirements? When there is no b
;; > ~~ for the expensive commercial databases?
;; >
;; > I'm not sure what you mean by "low-medium DB requirements". In my
;; > book, MySQL fails the essential requirements. MySQL just isn't an
;; > alternative. ;-)
;;
;; I disagree (somewhat).
;;
;; MySQL is a good database to start with, _because_ it is
;; simple.
Eh, is starting Perl without -w, -T and "use strict;" good because
it's simpler? Let's also throw out arrays, hashes and regexes, all
to make life simpler. What a great language we would get!
;; If your application needs more than simple input and simple
;; output, you will need to upgrade, but the experience you've
;; had with MySQL makes you more knowledgeable about databases,
;; and will make you more capable of making the right choice
;; for what you need. Additionally the DBI drivers are good.
You don't get any good experience with MySQL, because it's lacking too
many essential features. Since there are no transactions, how could you
ever became knowledge about rollback on failure? There is no checking on
referential constraints, so the only "experience" you get about keeping
your data consistent is "oops, my data is hosed!".
;; After working with MySQL for 6 months, I hit the "no
;; sub-select" restriction, and then I knew what I would need
;; from the replacement.
Oddly enough, the no-sub select is just a major nuisance. Most, if not
all, sub selects can be written with joins as well. And if your optimizer
is good, there shouldn't be a difference in speed.
;; Currently I use Oracle, which can do
;; what I need. IBM has a evaluation copy of DB2 for Linux
;; freely available from
;;
;; http://www-4.ibm.com/software/data/db2/udb/downloads.html
;;
;; Personally I would still use MySQL for webserver purposes
;; where all you care about is data in and data out _FAST_.
If all you care about is getting data in and out fast, you don't
need a relational database at all. What would be the point of
using MySQL?
If you actually care about your data, MySQL just doesn't cut it.
Abigail
--
perl -wlne '}print$.;{' file # Count the number of lines.
------------------------------
Date: Tue, 04 Jul 2000 19:37:33 GMT
From: bart.lateur@skynet.be (Bart Lateur)
Subject: Re: Unix database for Perl
Message-Id: <3962389f.995680@news.skynet.be>
Abigail wrote:
>If all you care about is getting data in and out fast, you don't
>need a relational database at all. What would be the point of
>using MySQL?
In such a case,what would you recommend?
--
Bart.
------------------------------
Date: 5 Jul 2000 09:48:44 +0100
From: Jonathan Stowe <gellyfish@gellyfish.com>
Subject: Re: Unix database for Perl
Message-Id: <8juspc$mr8$1@orpheus.gellyfish.com>
On 04 Jul 2000 12:14:14 EDT Abigail wrote:
>
> Oddly enough, the no-sub select is just a major nuisance. Most, if not
> all, sub selects can be written with joins as well. And if your optimizer
> is good, there shouldn't be a difference in speed.
>
On the whole most of these things are done far more quickly in Perl code
than trying to get fancy with your SQL - I did a report recently on a
really rather large dataset (A months worth of Radius logs) and I found
that doing a 'GROUP BY' was infinitely slower than implementing the same
thing using a hash - I say infinitely slower because the hash version took
about ten minutes whereas the GROUP BY ran for more than two hours before
I got bored and killed it thinking there must a quicker way.
Anyhow lets do benchmarks :
Benchmark: timing 10 iterations of DBD::CSV, Informix, MySQL, Postgres, XBase...
DBD::CSV: 8 wallclock secs ( 6.27 usr + 0.56 sys = 6.83 CPU) @ 1.46/s
Informix: 5 wallclock secs ( 1.32 usr + 0.15 sys = 1.47 CPU) @ 6.80/s
MySQL: 2 wallclock secs ( 0.25 usr + 0.06 sys = 0.31 CPU) @ 32.26/s
(warning: too few iterations for a reliable count)
Postgres: 60 wallclock secs ( 0.75 usr + 0.10 sys = 0.85 CPU) @ 11.76/s
XBase: 7 wallclock secs ( 5.93 usr + 0.18 sys = 6.11 CPU) @ 1.64/s
The Results are pretty much as I expected - the Informix is SE 7.24.UC5
if anyone is interested. I guess that the high Postgres Wallclock time
is due to some overhead on connecting and disconnecting - IMO this would
rule it out for web based stuff unless one were to maintain persistent
database connections. Bear in mind also that this is my laptop with an
IDE disk and only 64 Meg of memory.
#!/usr/bin/perl -w
use strict;
use DBI;
use Benchmark;
my $create_table = <<E01;
create table foo
(
blah char(10),
zub integer
)
E01
my $insert = <<E02;
insert into foo
values ( ?,?)
E02
my $select = <<E03;
select * from foo
E03
sub test_db {
my $connect = shift;
my $dbh = DBI->connect($connect) or die $DBI::errstr;
my $sth = $dbh->prepare($create_table) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
$sth->finish;
$sth = $dbh->prepare($insert) or die $dbh->errstr;
for ( 0 .. 200 )
{
$sth->execute('zubzub',$_) or die $dbh->errstr;
}
$sth->finish;
$sth = $dbh->prepare($select) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
while (my $foo = $sth->fetch)
{
my $a = 1;
}
$sth->finish;
$dbh->do('drop table foo') or die $dbh->errstr;
$dbh->disconnect ;
}
timethese 10, {
'DBD::CSV' => sub {test_db('DBI:CSV:f_dir=test')},
Informix => sub {test_db('dbi:Informix:test')},
Postgres => sub {test_db('dbi:Pg:dbname=test')},
XBase => sub {test_db('DBI:XBase:./test')},
MySQL => sub {test_db('DBI:mysql:test')}
};
--
** This space reserved for venue sponsor for yapc::Europe **
<http://www.yapc.org/Europe/>
------------------------------
Date: Wed, 05 Jul 2000 10:10:27 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <3962ED73.D5D00889@bigfoot.com>
Abigail wrote:
> ;; MySQL is a good database to start with, _because_ it is
> ;; simple.
>
> Eh, is starting Perl without -w, -T and "use strict;" good because
> it's simpler? Let's also throw out arrays, hashes and regexes, all
> to make life simpler. What a great language we would get!
Apples to oranges, eh?
Please note how popular PHP3 is for web development.
> You don't get any good experience with MySQL, because it's lacking too
> many essential features. Since there are no transactions, how could you
> ever became knowledge about rollback on failure? There is no checking on
> referential constraints, so the only "experience" you get about keeping
> your data consistent is "oops, my data is hosed!".
Depends -- as always -- on what you need it for.
I am currently working on a project where the intuitive way
to do updates, would be just to issue an "update" command on
the whole table. Unfortunately we have such large tables,
that just _maintaining_ the rollback information makes this
approach unacceptable. In this regard, we would be
perfectly happy with the implementation in MySQL.
> ;; After working with MySQL for 6 months, I hit the "no
> ;; sub-select" restriction, and then I knew what I would need
> ;; from the replacement.
>
> Oddly enough, the no-sub select is just a major nuisance. Most, if not
> all, sub selects can be written with joins as well. And if your optimizer
> is good, there shouldn't be a difference in speed.
Getting complex joins right is not always easy for a
beginner -- which is what I was talking about.
> ;; Personally I would still use MySQL for webserver purposes
> ;; where all you care about is data in and data out _FAST_.
>
> If all you care about is getting data in and out fast, you don't
> need a relational database at all. What would be the point of
> using MySQL?
Having atomic updates and multi-host multi-client
capability?
It was for me.
I am not advocating that MySQL is usable as a full-fledged
database, I do just not agree that it is worthless.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: Wed, 05 Jul 2000 10:27:27 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <3962F16F.434BFDEE@bigfoot.com>
Jonathan Stowe wrote:
> On the whole most of these things are done far more quickly in Perl code
> than trying to get fancy with your SQL - I did a report recently on a
> really rather large dataset (A months worth of Radius logs) and I found
> that doing a 'GROUP BY' was infinitely slower than implementing the same
> thing using a hash - I say infinitely slower because the hash version took
> about ten minutes whereas the GROUP BY ran for more than two hours before
> I got bored and killed it thinking there must a quicker way.
Was the table properly indexed for this operation?
A running time of several hours is normally an indication of
either very complex code or non-indexed tables.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: 05 Jul 2000 04:50:51 EDT
From: abigail@delanet.com (Abigail)
Subject: Re: Unix database for Perl
Message-Id: <slrn8m5up0.ibb.abigail@alexandra.delanet.com>
Thorbjørn Ravn Andersen (thunderbear@bigfoot.com) wrote on MMD September
MCMXCIII in <URL:news:3962ED73.D5D00889@bigfoot.com>:
== Abigail wrote:
==
== > ;; MySQL is a good database to start with, _because_ it is
== > ;; simple.
== >
== > Eh, is starting Perl without -w, -T and "use strict;" good because
== > it's simpler? Let's also throw out arrays, hashes and regexes, all
== > to make life simpler. What a great language we would get!
==
== Apples to oranges, eh?
No, not really.
== Please note how popular PHP3 is for web development.
Popular implies good? Windows 95 is better than VMS or IRIX, just because
more people opt to use Windows 95 than VMS or IRIX? HTML for Dummies is
a better book than Knuth's The Art of Computer Programming because it
sells more copies?
Popularity is irrelevant when it comes to quality. (As I noted before,
one of the reasons MySQL is popular is the cluelessness of its users.)
== > You don't get any good experience with MySQL, because it's lacking too
== > many essential features. Since there are no transactions, how could you
== > ever became knowledge about rollback on failure? There is no checking on
== > referential constraints, so the only "experience" you get about keeping
== > your data consistent is "oops, my data is hosed!".
==
== Depends -- as always -- on what you need it for.
==
== I am currently working on a project where the intuitive way
== to do updates, would be just to issue an "update" command on
== the whole table. Unfortunately we have such large tables,
== that just _maintaining_ the rollback information makes this
== approach unacceptable. In this regard, we would be
== perfectly happy with the implementation in MySQL.
Your data is unimportant enough that you can't spare the money for the
diskspace?
== > If all you care about is getting data in and out fast, you don't
== > need a relational database at all. What would be the point of
== > using MySQL?
==
== Having atomic updates and multi-host multi-client
== capability?
How would you do atomic updates without transactions and isolation levels
if you need more than one statement to do the update?
Abigail
--
BEGIN {$^H {join "" => ("a" .. "z") [8, 13, 19, 4, 6, 4, 17]} = sub
{["", "Just ", "another ", "Perl ", "Hacker\n"] -> [shift]};
$^H = hex join "" => reverse map {int ($_ / 2)} 0 .. 4}
print 1, 2, 3, 4;
------------------------------
Date: Wed, 05 Jul 2000 09:03:02 GMT
From: bart.lateur@skynet.be (Bart Lateur)
Subject: Re: Unix database for Perl
Message-Id: <3964f77b.1125962@news.skynet.be>
Thorbjørn Ravn Andersen wrote:
>I am not advocating that MySQL is usable as a full-fledged
>database, I do just not agree that it is worthless.
Just FYI: in this month's Dr. Dobb's Journal (July), there is an article
comparing MySQL to Oracle. No, the article isn't online
(<http://www.ddj.com/articles/2000/0007/>).
Here, too, the author claims that transactions are not necessary in 99%
of all practical cases.
My idea would be:
* continuus updates on the dB, e-commerce: you need 100% reliability on
the updates, i.e. transactions and rollback.
* read the data a lot, but update not too often (and usually by 1
person) e.g. an online catalog: MySQL will probably suffice.
--
Bart.
------------------------------
Date: Wed, 05 Jul 2000 12:49:26 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <396312B6.B6F7F76@bigfoot.com>
Abigail wrote:
> == Please note how popular PHP3 is for web development.
>
> Popular implies good? Windows 95 is better than VMS or IRIX, just because
> more people opt to use Windows 95 than VMS or IRIX? HTML for Dummies is
> a better book than Knuth's The Art of Computer Programming because it
> sells more copies?
Sorry. You picked a poor example -- having maintained IRIX
for a living I am positive that IRIX is worse (or at least
was then) than Windows 95.
The ACP is _not_ a beginners book.
I wouldn't teach perl from your signatures either.
> Popularity is irrelevant when it comes to quality. (As I noted before,
> one of the reasons MySQL is popular is the cluelessness of its users.)
In your opinion. MySQL is a tool. Some jobs does it do
well enough to be used.
> Your data is unimportant enough that you can't spare the money for the
> diskspace?
Sorry for being unclear. Unacceptable in terms of execution
speed, because of the time to write the rollback segment to
disk.
> == Having atomic updates and multi-host multi-client
> == capability?
>
> How would you do atomic updates without transactions and isolation levels
> if you need more than one statement to do the update?
I didn't. MySQL was okay for that. That was my point.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: Wed, 5 Jul 2000 10:19:58 -0400
From: Bennett Todd <bet@rahul.net>
Subject: Re: Unix database for Perl
Message-Id: <20000705101958.B465@oven.com>
--7ZAtKRhVyVSsbBD2
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
2000-07-05-04:50:51 Abigail:
> As I noted before, one of the reasons MySQL is popular is the
> cluelessness of its users.
Just because people elect to design to avoid the need for complex
features that are more expensive to implement, does not make them
clueless. In fact, if they wanted to call names, they'd argue that
using database features that are expensive to implement, and using
non-portable parts of the database interface (non-portable to other
RDBMS systems, that is) reflects poor design skills. So why don't we
not call names at all, and try and confine ourselves to substantive
matters.
> Your data is unimportant enough that you can't spare the money for
> the diskspace?
Please can you stop being so insulting? The tit-for-tat here would
be, just because your design skills aren't up to doing a good
systems design without using these expensive features, doesn't mean
that the rest of us share this problem.
> How would you do atomic updates without transactions and isolation
> levels if you need more than one statement to do the update?
You wouldn't. So a slap-dash, thoughtless database design could
often end up being unsafe if you don't have crutches like
transactions. Frequently, however, with careful design up front,
tables can be structured to reflect update requirements in such a
fashion as to guarantee all the consistency needs your application
has while avoiding any need for atomic transactions of more than one
statement.
Now there's an interesting design question lurking hereabouts: what
class of problems require transactions of multiple updates to
maintain integrity requirements, no matter how the database is
designed? Note that MySQL can do them too, you just have to code
them manually, using table locks. MySQL probably loses much of its
performance advantage over the big, slow databases when you're
forced to do this, I'd guess. But perhaps not all.
-Bennett
--7ZAtKRhVyVSsbBD2
Content-Type: application/pgp-signature
Content-Disposition: inline
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE5Y0QOL6KAps40sTYRAkyyAJ40X9plzhZnjD6IlxqpbXw8yr1RjACdHuj8
Yhxl24xxNKEw74EWxjXvV1M=
=dD/L
-----END PGP SIGNATURE-----
--7ZAtKRhVyVSsbBD2--
------------------------------
Date: 6 Jul 2000 00:07:57 +0100
From: Jonathan Stowe <gellyfish@gellyfish.com>
Subject: Re: Unix database for Perl
Message-Id: <8k0f4d$8e1$1@orpheus.gellyfish.com>
On Wed, 05 Jul 2000 10:27:27 +0200 Thorbjørn Ravn Andersen wrote:
> Jonathan Stowe wrote:
>
>> On the whole most of these things are done far more quickly in Perl code
>> than trying to get fancy with your SQL - I did a report recently on a
>> really rather large dataset (A months worth of Radius logs) and I found
>> that doing a 'GROUP BY' was infinitely slower than implementing the same
>> thing using a hash - I say infinitely slower because the hash version took
>> about ten minutes whereas the GROUP BY ran for more than two hours before
>> I got bored and killed it thinking there must a quicker way.
>
> Was the table properly indexed for this operation?
>
> A running time of several hours is normally an indication of
> either very complex code or non-indexed tables.
>
Or a multi million row table ... it doesnt matter, MySQL is designed to
do this stuff Index or no - it should be faster than Perl at doing these
operations because thats what it does and it is just a generalized operation
in Perl.
/J\
--
yapc::Europe in assocation with the Institute Of Contemporary Arts
<http://www.yapc.org/Europe/> <http://www.ica.org.uk>
------------------------------
Date: 6 Jul 2000 09:41:48 +0100
From: Jonathan Stowe <gellyfish@gellyfish.com>
Subject: Re: Unix database for Perl
Message-Id: <8k1goc$nv7$1@orpheus.gellyfish.com>
On Wed, 5 Jul 2000 10:19:58 -0400 Bennett Todd wrote:
>
> 2000-07-05-04:50:51 Abigail:
>
>> How would you do atomic updates without transactions and isolation
>> levels if you need more than one statement to do the update?
>
> You wouldn't. So a slap-dash, thoughtless database design could
> often end up being unsafe if you don't have crutches like
> transactions. Frequently, however, with careful design up front,
> tables can be structured to reflect update requirements in such a
> fashion as to guarantee all the consistency needs your application
> has while avoiding any need for atomic transactions of more than one
> statement.
>
But we seem to be talking only about single transactions - updates or inserts
of a single row in multiple tables, but in the kind of programs I commonly
work the crucial stuff might consist of massive updates of many rows in
a number of tables - where it is all or nothing : your billing run, your
rent debit, your housing benefit either worked or they didnt its no use
having something half done - if something goes wrong half way through you
really do want to put it back as it nothing had ever happened - you either
use a 'transaction' or you go to the secure store to recover your tape.
Sure you might implement something like this using temporary tables, very
inefficiently of course, but then at some point you are going to have to
break some eggs and without transactions there is going to be a time
when you have to commit something permanently to the database where you
wont be able to do anything if something goes wrong. You could 'journal'
each update in a bulk update but unless you can roll back if something
happens to the journaling then you are no better off. And so on and so forth.
Likewise with data security. If your cheap and cheerful database goes
tits up five minutes before your daily backup whoosh bang goes a days
worth of data, with a database that does transaction logging you can
get all of the data back up to the last completed transaction. And of
course you cant do more frequent backups because it would mean that you
have to take the database offline while you do it.
And so on and so forth. I think there is a reason why we havent had
the equivalent of the 'Halloween Paper' from one of Larry Ellison's
minions ...
/J\
--
yapc::Europe in assocation with the Institute Of Contemporary Arts
<http://www.yapc.org/Europe/> <http://www.ica.org.uk>
------------------------------
Date: Thu, 06 Jul 2000 11:04:05 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <39644B85.295A67EF@bigfoot.com>
Jonathan Stowe wrote:
> Or a multi million row table ... it doesnt matter, MySQL is designed to
> do this stuff Index or no - it should be faster than Perl at doing these
> operations because thats what it does and it is just a generalized operation
> in Perl.
No.
If you do not index your tables in you database, you should
not allow yourself to use hashes in the Perl program you
compare with.
Do you swap to floppy too?
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: Thu, 06 Jul 2000 11:10:58 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <39644D22.3AB73A00@bigfoot.com>
Jonathan Stowe wrote:
> But we seem to be talking only about single transactions - updates or inserts
> of a single row in multiple tables, but in the kind of programs I commonly
> work the crucial stuff might consist of massive updates of many rows in
> a number of tables - where it is all or nothing : your billing run, your
> rent debit, your housing benefit either worked or they didnt its no use
> having something half done - if something goes wrong half way through you
> really do want to put it back as it nothing had ever happened - you either
> use a 'transaction' or you go to the secure store to recover your tape.
I thought we had recognized that there is a need for the
large database systems, where these features are essential.
Can we agree that there are other tasks (like storing
weblogentries in a database instead of a file) where these
are not crucial?
I maintain that MySQL has a niche where speed, size, and
memory footprint is more important than transactions, and
that Oracle/DB2/whatever has another.
> And so on and so forth. I think there is a reason why we havent had
> the equivalent of the 'Halloween Paper' from one of Larry Ellison's
> minions ...
At least we have a choice.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: Thu, 06 Jul 2000 11:35:49 GMT
From: Jonathan Stowe <gellyfish@gellyfish.com>
Subject: Re: Unix database for Perl
Message-Id: <pa_85.1470$iP2.141592@news.dircon.co.uk>
On Thu, 06 Jul 2000 11:10:58 +0200, Thorbjørn Ravn Andersen Wrote:
> Jonathan Stowe wrote:
>
>> But we seem to be talking only about single transactions - updates or inserts
>> of a single row in multiple tables, but in the kind of programs I commonly
>> work the crucial stuff might consist of massive updates of many rows in
>> a number of tables - where it is all or nothing : your billing run, your
>> rent debit, your housing benefit either worked or they didnt its no use
>> having something half done - if something goes wrong half way through you
>> really do want to put it back as it nothing had ever happened - you either
>> use a 'transaction' or you go to the secure store to recover your tape.
>
> I thought we had recognized that there is a need for the
> large database systems, where these features are essential.
> Can we agree that there are other tasks (like storing
> weblogentries in a database instead of a file) where these
> are not crucial?
>
But this is not what the post I responded to was saying ....
/J\
------------------------------
Date: Thu, 06 Jul 2000 11:45:13 GMT
From: Jonathan Stowe <gellyfish@gellyfish.com>
Subject: Re: Unix database for Perl
Message-Id: <dj_85.1471$iP2.141592@news.dircon.co.uk>
On Thu, 06 Jul 2000 11:04:05 +0200, Thorbjørn Ravn Andersen Wrote:
> Jonathan Stowe wrote:
>
>> Or a multi million row table ... it doesnt matter, MySQL is designed to
>> do this stuff Index or no - it should be faster than Perl at doing these
>> operations because thats what it does and it is just a generalized operation
>> in Perl.
>
> No.
>
> If you do not index your tables in you database, you should
> not allow yourself to use hashes in the Perl program you
> compare with.
>
You brought up indexes, matey. As it happens this table is indexed
appropriately, but I still contend that it shouldnt matter: the database
supports GROUP BY, Perl supports hashes, I dont have to do anything special
to a dataset to use parts of it as keys of a hash - in fact it is not
possible to do anything to the data in any general sense to cause a hash
to perform faster, A GROUP by on an even unindexed column should not be
whole orders of magnitude slower than doing the same thing in Perl with a
hash - it doesnt make any sense to me at all. They are doing the same
thing they are both written in C and should at least be in the same ballpark.
/J\
------------------------------
Date: Thu, 06 Jul 2000 14:26:50 +0200
From: =?iso-8859-1?Q?Thorbj=F8rn?= Ravn Andersen <thunderbear@bigfoot.com>
Subject: Re: Unix database for Perl
Message-Id: <39647B0A.BAEC8FC5@bigfoot.com>
Jonathan Stowe wrote:
> You brought up indexes, matey. As it happens this table is indexed
> appropriately, but I still contend that it shouldnt matter: the database
> supports GROUP BY, Perl supports hashes, I dont have to do anything special
> to a dataset to use parts of it as keys of a hash - in fact it is not
> possible to do anything to the data in any general sense to cause a hash
> to perform faster, A GROUP by on an even unindexed column should not be
> whole orders of magnitude slower than doing the same thing in Perl with a
> hash - it doesnt make any sense to me at all. They are doing the same
> thing they are both written in C and should at least be in the same ballpark.
Yes, I brought up indexes, since that is how you speed up
queries when using databases. Much in the same way that you
speed up linear seaches by using hashes in Perl.
Which database were you using on what equipment? It sounds
like it may be weak in using substrings in group-by clauses.
--
Thorbjørn Ravn Andersen "...plus...Tubular Bells!"
http://bigfoot.com/~thunderbear
------------------------------
Date: 16 Sep 99 21:33:47 GMT (Last modified)
From: Perl-Users-Request@ruby.oce.orst.edu (Perl-Users-Digest Admin)
Subject: Digest Administrivia (Last modified: 16 Sep 99)
Message-Id: <null>
Administrivia:
The Perl-Users Digest is a retransmission of the USENET newsgroup
comp.lang.perl.misc. For subscription or unsubscription requests, send
the single line:
subscribe perl-users
or:
unsubscribe perl-users
to almanac@ruby.oce.orst.edu.
| NOTE: The mail to news gateway, and thus the ability to submit articles
| through this service to the newsgroup, has been removed. I do not have
| time to individually vet each article to make sure that someone isn't
| abusing the service, and I no longer have any desire to waste my time
| dealing with the campus admins when some fool complains to them about an
| article that has come through the gateway instead of complaining
| to the source.
To submit articles to comp.lang.perl.announce, send your article to
clpa@perl.com.
To request back copies (available for a week or so), send your request
to almanac@ruby.oce.orst.edu with the command "send perl-users x.y",
where x is the volume number and y is the issue number.
For other requests pertaining to the digest, send mail to
perl-users-request@ruby.oce.orst.edu. Do not waste your time or mine
sending perl questions to the -request address, I don't have time to
answer them even if I did know the answer.
------------------------------
End of Perl-Users Digest V9 Issue 3609
**************************************
