Re: Query performance: DivideAndConquer vs. BruteForce

Tushar,

It depends. It always depends.

My comments are not about version 9i, but apply pretty much across the board, and should extend to 9i reasonably well.

For a well designed and well behaved database, and a well formed query, the brute force method nearly always yields performance that [Quoted] is good enough. Given the extra programming effort needed to implement "divide and conquer", and the extra maintenance effort that this approach may create for the future, the brute force method is probably the one to try first, and use divide and conquer in those cases where performance is really a problem.

Let me change the phrasing. In this case, I prefer, "free the optimizer" to "brute force". The optimizer is a lot more subtle than [Quoted] a mere brute force solution. The cost based optimizer will come up with an execution plan that reflects available indexes, various join algorithms, table cardinalities (size expressed in rows), and other considerations.

[Quoted] If, for example, there is one huge table, with selection criteria that will narrow the search down to a few dozen rows, and the other tables can all be
searched using a index for table lookup, the response will be blazingly fast, just using the optimizer.

There are several things that can upset this rosy picture.

[Quoted] The database design might be illogical or clumsy. The database may be overpopulated, compared to what was originally planned for. The query [Quoted] may be poorly phrased, or in some cases, logically incorrect. The database may be using the RBO, and the phrasing of the query may lead to an unfortunate execution plan. The query may be correct, but outside the scope of what was thought of when the database was designed. I'm thinking particularly of index design.

My plan usually, is to make the query as sensible and well formed as [Quoted] possible, and to try it out. Often, this yields satisfactory performance, and I move on to the next issue. In cases where the performance is aproblem, I look at the execution plan. If it's a terrible plan, like for instance one that involves a cartesian join between large tables, I look for several things.

One thing I look for is logical errors in the query. Then, I make sure the CBO is in use. Then, I make sure the tables have been analyzed so that the size is accurately cataloged. Then, I take a look at the indexes.

19 out of 20 queries I write never get beyond this point.

If I haven't found the problem by then, I'll use hints, or rephrase the
query. As a last resort, I'll program it in "divide and conquer" fashion.

I realize this is all kind of vague and hand wavy, but I hope it helps. Received on Wed Jan 16 2002 - 15:52:19 CET