Re: perl & SQL best practices

[BrowserUk]

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.