Section 2.3. Strategy Before Tactics


2.3. Strategy Before Tactics

Strategy defines the tactics, not the other way round. A skillful developer doesn't think of a process in terms of little steps, but in terms of the final result. The most efficient way to obtain that result may not be to proceed in the order specified in the business rules, but rather to follow a less obvious approach. The following example will show how paying too much attention to the procedural processes within a business can distract ones' attention from the most efficient solution.

Some years ago I was given a stored procedure to try to optimize; "try" is the operative word here. Two attempts at optimization had already been made, once by the original authors, and secondly by a self-styled Oracle expert. Despite these efforts, this procedure was still taking 20 minutes to run, which was unacceptable to the users.

The purpose of the procedure was to compute quantities of raw materials to be ordered by a central factory unit, based on existing stocks and on orders that were coming from a number of different sources. Basically, the data from several identical tables for each data source had to be aggregated inside one master table. The procedure consisted of a succession of similar statements simplified as follows. First, all data from each distinct source table were inserted into the single master table. Second, an aggregate/update was applied to each instance of raw material in that master table. Finally, the spurious data not relevant to the aggregate result was deleted from the table. These stages were repeated in sequence inside the procedure for every distinct source table. None of the SQL statements were particularly complex, and none of them could be described as being particularly inefficient.

It took the better half of a day to understand the process, which eventually prompted the question: why was this process being done in multiple steps? A subquery in a from clause with a union operator would allow the aggregation of all the various sources. A single select statement could provide in one step the result set that had to be inserted into the target table. The difference in performance was so impressivefrom 20 minutes down to 20 secondsthat it took some time to verify that the final result was indeed identical to that previously obtained.

Extraordinary skills were not required to achieve the tremendous performance improvement just described, but merely an ability to think outside the box. Previous attempts to improve this process had really been hindered by the participants allowing themselves to get too close to the problem. One needed to take a fresh look, to stand back, and try to see the bigger picture. The key questions to ask were "What do we have when we enter this procedure?" and "Which result do we want when we return from it?" Together with some fresh thinking, the answers to those questions led to a dramatically improved process.

Stand back from your problem to get the wider picture before plunging into the details of the solution.




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net