in reply to Replacing SQL with perl

Woah! Hold on there! The real answer is: none of the above! Faster? DBengines are specifically designed to optimize lookups, changes, and other operations. They store data in heavily optimized hash-like tables which beat the socks off flat files. Easier? Personal preference i guess, but isn't writing one SQL statement with all of its helpful stuff like placeholders (DBI) and *s easier than looping through hashes or arrays? Neater? connect, grab, close, use data- and you're ready to fly!

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.

Replies are listed 'Best First'.
Re: Re: Replacing SQL with perl
by marvell (Pilgrim) on Dec 06, 2000 at 22:15 UTC
    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

[reply]
      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.
[reply]
      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)

[reply]
Re: Re: Replacing SQL with perl
by cadfael (Friar) on Dec 07, 2000 at 20:17 UTC
    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"

[reply]

In Section Seekers of Perl Wisdom