Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Clarification to Database Programming Rule #1 3 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Unless Perl's sorting offers something the database can't (as phillup implied) it's probably not going to be as efficient as the database sorting. It's optimized for that sort of thing.

    With Oracle (and others?), a sort operation is guaranteed to increase CPU usage and can significantly affect query performance. In addition, there can be extra I/O overhead, which can cause you to slam into your I/O limits, especially if a disk sort is required. Plus, all rows must be accessed and sorted before the first

    • You've mentioned great examples of where optimization should still be done after the fact. If I don't know that a query (and I frequently create huge queries) is going to cause a problem with performance, I am not going to worry about it up front. The simplest thing to do is simply to have the SQL specify the order. That's what SQL is there for, after all. If some DBA tells me "I don't want you using SQL the way it's intended to be used," I would suggest that the DBA is sorely mistaken -- if that's a bl

      • Also note that ORDER BY clauses are not necessary if an index exists on some or all of the columns in the clause.

        To be honest, I was not aware of that. Can you provide some documentation?

        It's common sense, if an index is being used and there's no ORDER BY clause, then rows will be returned in the order of the index. But I wouldn't generally rely on it in production application code and I would explicitly specify the ORDER BY if that's the order I wanted.

        I have run across production code that had something like 'where field1 = ?', and there was a composite index on 'field1, field2', and the code relied on field2 being in order without the SQL statement explicitly specifying it. An 'ORDER BY field2' clause actually made the sql less efficient, because the optimizer thought there needed to be a temp table for a sort. An 'ORDER BY field1, field2' fixed that. Though that doesn't mean it would behave the same in all databases

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.