ESPECIALLY for the complex queries are you more interested in using built-in DB optimizations like functions, cacheing, locking, and the such- why would you NOT want to use it? Storing info in your cgi can even be dangerous since it would never know when the data would become outdated. In short- USE AN SQL SERVER if you WANT speed, fast devel time, and neatness- you just can't lose!

What kind of situations do you imagine that would benefit from dropping the db? I would imagine only insanley simple things like an included file to print to the user but that not may even benefit. AND, with the DB, you get a great way to organize and even PROTECT information from prying eyes. A well-organized table beats recursive searches through the filesystem anyday! The only time you want to do any processing on the data after you have it is when you may have data that you need to compare it to or its something that the db can't handle or doesn't support or you're worried about DBengine load (speed) if a complex operation is taking too much time.

AgentM Systems nor Nasca Enterprises nor Bone::Easy nor Macperl is responsible for the comments made by AgentM. Remember, you can build any logical system with NOR.

I didn't mean replace the database with file for all applications, just some. I've worked on projects with over powered, over indexed and over network databases. In those situations, the overheads have been huge. The dataset was small and it just didn't need it.

Again, I'm not suggesting ditching databases, as a rule, just ditching complex queries.

Obviously, write queries will have a very special arrangement. As you say, all the locking and stuff would be impossible. I suppose my question would be more aimed at read only queries.

--
Brother Marvell

Even so, I'm in favor of keeping my data together, even across a network. A small optimization by hard-coding in data is hardly worth spreading it around to 30 different scripts that will need it. Also, keeping data centralized allows for maximum expandability. If all of the REAL variables in your program are stored in the db, then you have no problem changing it, if, let's say, to new entries must be accomodated for.

But, if you have 100% static data, then, by all means, put it whereever you want (though I would still like to keep the data centralized). This occurs so rarely, that this may not even be useful. Using perl on flat-files (especially CGIs on the same file) are dangerous since race conditions may occur and corrupt data. In this case, you may need to use a non-perl mutex server (like the one I'm writing now). So, 100% static, read-only data it is, then! Well, you might as well make it a constant var, then, eh?


AgentM Systems nor Nasca Enterprises nor Bone::Easy nor Macperl is responsible for the comments made by AgentM. Remember, you can build any logical system with NOR.

Actually, complex queries are the ones NOT to ditch. In fact, the answer is still no to all your questions.

If you have a case where you think I'm wrong, I'd bet that a local cache would work better than sprucing up your perl with DB-like code. Let the DB do its job. Lots of people worked real hard to make it fast, efficient, and simple.

--
$you = new YOU;
honk() if $you->love(perl)

Well, this may be a case where you'd want to use the right tool for the job.

You can use sql to do string manipulation (trim, substring, soundex, and so forth), but the sql server has to process those in addition to any joins, restriction, ordering, and so on.

I have found that passing a sql query using DBI and then using perl to do any text processing is far more efficient in terms of development and speed of execution than trying to do it all in sql. Especially if you are trying to create a dynamic web page with up-to-date information from a relational database.

Bottom line: SQL is the tool for querying relational databases; Perl is the tool for formatting the results. They work synergistically, and complement each other's strengths and limitations.

-----
"Computeri non cogitant, ergo non sunt"

For small data volumes - OK. Great. For mass data, where will you store all the stuff you "select into perl", e.g., to group it: in memory?

That said, for a lot of situations it *is* a good thing to do some of the processing in Perl, if only to stay database vendor independent.

(Updated to use the right id link syntax): See also Storing Complex Structures in a database.

Christian Lemburg
Brainbench MVP for Perl
http://www.brainbench.com

Notice that I left out subselects as a special case. This in my opinion, is the one type of query mentioned that I think could be handled capably by both Perl and SQL. Of course, it holds true to the "depends" hunch, in that Perl would only be more capable if the single SELECT queries were significantly less complex than the case of using a sub-select statement.

Parsing-wise and other operations with text, Perl will win hands down, but looking at scale, the larger the returned dataset from an SQL query, the more load you throw at Perl. So, then you're looking at hardware issues.

Perl can win, but only if your Perl code is optimized and your SQL isn't. Disclaimer: I haven't run any tests to see if I am right, but the only fair test would be to use identical machines (or the same machine for both the perl and the DBMS) in order to fairly test based on code. But after working with the DBI and numerous databases for a year, I just go with what will provide an adequate response from the application. My reply shows what I tend to do in my own code, and in the past, others familiar with the DBI have reviewed my code favorably.

I'll finish by saying that short of trying every variation, you may not find a decent answer. There are optimization tricks in perl, the DBI, and SQL that will all be handy, and as such, it's not an easy question.

ALL HAIL BRAK!!!

whoa there! ORDER BY makes almost any Database, especially Oracle, crawl. And if you have multiple app servers, you might want to do your sorting outside the database. depends of course, but ORDER BY is a killer.

Not if the indexes are set up correctly. Anything you ORDER BY on often enough to optimize, optimize with an index.

--
$you = new YOU;
honk() if $you->love(perl)

my $unsorted = $dbh->selectall_arrayref($FOOSQL) or 
  die "Fooey, foo failed ($FOOSQL) $DBI::errstr\n";
for (
sort {
  $Q::SortBy == 1 ? 
    ($a->[1] cmp $b->[1] or $a->[4] cmp $b->[4] or $a->[2] cmp $b->[2]
+) :
  $Q::SortBy == 2 ? 
    ($a->[1] cmp $b->[1] or $a->[2] cmp $b->[2]) : 
    ($b->[3] <=> $a->[3] or $a->[2] cmp $b->[2])
}
map {[$_, lc $_->[5], substr($_->[4],3,3), int $_->[1], lc $_->[3]]} @
+$unsorted) {
  ## Do something with $_ here...
}
[download]

For instance, I have on numerous occasions loaded an entire table into a hash and refrenced data in it directly instead of doing a join in the SQL query. This in some cases has helped my program run as much as 20 times faster. This is only appropriate in some situations, such as when your program doesn't have to take into account any changes that might occur in the table in the DB after it was loaded.
I firmly believe that all the data should be stored inside a RDMBS, just that sometimes you can achieve a big performance boost by doing "more work in perl, and less work in SQL".

"running with scissors! running with scissors!" :)(g,d,r)

I would be especially nervous trying to do all the behind the scenes stuff (referential integrity, type enforcing,etc.) let alone trying to optimize ways to store and look up columns.

There might be benefits to simple databases of small sizes, but for hefty applications, I'll stick with the Sybases, Oracles, and DB2s.

For some applications I'm looking at, with 10,000 to 20,000, the time involved in reading the flat file and parsing it is significant. The well structured, binary format of db files makes reading them quicker, in a sense they are preparsed.

And finally, to repeat something someone mentioned earlier, it will be vastly easier to handle multiple scripts making changes at one time with a db then without.

Or to have stored procedures in perl? For queries SQL is good, but for whiles, ifs and declares, perl's a lot better!

Maybe this doesn't add anything you can't do by calling SQL from perl, but if you have to call the DB from, say, java. And for the next generation of query languages, I'm sure that perl would have some features to lend.

What do you think, my brothers?

Yours,
Poet (first-time poster ;-)

Implenting essential functionality like indexes, foreign keys, triggers and other niceties would be perhaps fun to work, but it would be quite the effort to replace the value-added that comes with a professional RDMS.

if the data involved is small, and would equate to a couple of isolated (no relationships) tables, then perhaps a flatfile scenario would be ok. but if you want to restrict columns to domains of data that would reside elsewhere (another table, file) then not leveraging what others have spent decades building would seem to be a bit illogical.

Even lightweight databases with minimal advanced capabilities (i.e no stored procedures, triggers, replication, recovery) in most cases would still be advantages (IMHO) to using flat files. Today it's only one or two tables/files, but when does any meaningful project ever stay static?

but I agree with your friends....it would very well "depend"

I think this is a great topic. thanks for posting it...

A good dba will look at the application requirements, and make sure there are the correct indexes across the whole database. Database optimisers (like those used in the above mentioned databases) are normally very good at squeezing the last bit of performance out of the engine and the hardware. After all, that to a large degree is what you are paying for - the top end performance.