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
--Larry Wall in perl.c from the perl source code
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.
Optimization (Score:2)
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
Re:Optimization (Score:2)
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 blanket statement (if the DBA is only discussing problematic SQL, I can buy that,) If it turns out that individual queries are causing issues, it's OK to optimize those particular queries and deal with the potential extra complexity; sometimes delaying logic is important for optimization reasons and I'd wouldn't object to that so long as there is a demonstrated need for optimization. I would much rather maintain clean code than "optimized" code.
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? I'd like to learn more about that. However, if you rely on that (and I don't know enough about it to know if that's a good idea), then you're still pushing some logic down to the database layer and that fits with the "apply the logic as early as possible" rule, though I confess to feeling a bit dubious about a relatively invisible feature having a great impact on my results as it feels like action at a distance. It also seems to overload the meaning of "index", something else that I also view dubiously, though I'd be happy to be convinced otherwise.
Reply to This
Parent
Re:Optimization (Score:1)
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