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.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • by djberg96 (2603) on 2004.06.16 17:29 (#31613) Journal
    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 can be returned, which may be unacceptable for web page interfaces (although you can futz with your optimizer to alter how this behaves).

    This is really the main reason I avoid ORDER BY clauses - it hammers I/O and pisses off the DBA, assuming I avoid the I/O limit at all. So, if Perl can do it reasonably quickly then I'm happy to handle it on the client side and earn a few brownie points with the DBA. It's often a case of trial and error - try it with the ORDER BY clause, then try the SQL without it and see how each performs.

    Also note that ORDER BY clauses are not necessary if an index exists on some or all of the columns in the clause. Oracle can use the index to fetch the rows in the required order and eliminate the need for a sort operation, although in some cases this approach can be worse than using a sort. Again, trial and error.

    So, I guess what I'm saying is that you have to know your database, not just your goal, before you can make a blanket assumption about how SQL should be constructed. Hell, there are whole companies that *specialize* in SQL tuning.

    Anyway, that's my 2 cents for the day.

    • 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 somet