Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

comment on

( [id://3333] : superdoc . print w/replies, xml ) Need Help??

I think a lot of data gets imported into RDBMSs for the wrong reasons.

But, once you have gone to the bother and expense of putting it in there, it doesn't make a whole lot of sense to export it en-masse, only to sub-select it using Perl (or other programming language). So as far as is practical, have the DB do the selection and only export the data you need to the procedural programming language.

Conversely, SQL has it weaknesses. String manipulations, complex data conversions, and similar data derivations are possible in (some dialects of) SQL, but they are often clumsy and awkward to program and horribly inefficient to process.

A particular case of an SQL routine I saw a few years ago, that took a date in the internal format and returned it formated for human consumption in any of the (from memory, (back then) 22) official EU languages. The SQL ran on for 5 or 6, 50-line screens and a totally incomprehensible morass of nested CASE expressions and repetitious SUBSTR() calls. It was actually quite an engineering achievement; but it ran like a dog and consumed prodigious amount of server resources. Moving that date formatting out of the sql and into the C code that was producing the reports reduced both time, and the server costs immensely.

The lesson is to use the strengths of each language for the appropriate parts of the processing. SQL is really good at picking out the bits of the total dataset that match certain criteria; Perl is really good at massaging data into other forms.

Of course, if you have pl/perl available within the DB it blurs the lines again. But if the results of manipulations are only required client-side, then let the client do it. If they will be used or reused by multiple clients, then it can make sense to do them server-side, where it doesn't impose undue load or require extraordinary feats of SQL to do so.

Full-text searching is another area where it often makes sense to avoid the SQL primatives (like LIKE), by pre-processing the data externally and building index tables as the text is uploaded to the DB.


With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.

The start of some sanity?


In reply to Re: perl & SQL best practices by BrowserUk
in thread perl & SQL best practices by ksublondie

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



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.