Your hunch about "depends" seems right. To me it's really a matter of optimization. You don't want to
SELECT * FROM table
if you're only going to use data that would have been better limited by adding a WHERE clause in your statement. Furthermore, SQL is better at handling data in databases, so beyond the situation with subselects, SQL is going to perform better in those other situations unless your Perl code does some wicked tricks, in which case, will you share?

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!!!

In reply to Re: Replacing SQL with perl by PsychoSpunk
in thread Replacing SQL with perl by marvell

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.