2.16. Succinct SQL

The skillful developer will attempt to do as much as possible with as few SQL statements as possible. By contrast, the ordinary developer tends to closely follow the different functional stages that have been specified; here is an actual example:

    -- Get the start of the accounting period
    select closure_date
    into dtPerSta
    from tperrslt
    where fiscal_year=to_char(Param_dtAcc,'YYYY')
      and rslt_period='1' || to_char(Param_dtAcc,'MM');

    -- Get the end of the period out of closure
    select closure_date
    into dtPerClosure
    from tperrslt
    where fiscal_year=to_char(Param_dtAcc,'YYYY')
      and rslt_period='9' || to_char(Param_dtAcc,'MM');

This is an example of very poor code, even if in terms of raw speed it is probably acceptable. Unfortunately, this quality of code is typical of much of the coding that performance specialists encounter. Two values are being collected from the very same table. Why are they being collected through two different, successive statements? This particular example uses Oracle, and a bulk collect of the two values into an array can easily be implemented. The key to doing that is to add an order by clause on rslt_period, as follows:

    select closure_date
    bulk collect into dtPerStaArray
    from tperrslt
    where fiscal_year=to_char(Param_dtAcc,'YYYY')
      and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
                          '9' || to_char(Param_dtAcc,'MM'))
    order by rslt_period;

The two dates are stored respectively into the first and second positions of the array. bulk collect is specific to the PL/SQL language but the same reasoning applies to any language allowing an explicit or implicit array fetch.

Note that an array is not even required, and the two values can be retrieved into two distinct scalar variables using the following little trick:^[*]

^[*] The Oracle function decode( ) works like case. What is compared is the first argument. If it is equal to the second argument, then the third one is returned; if there is no fifth parameter, then the fourth one corresponds to else; otherwise, if the first argument is equal to the fourth one, the fifth one is returned and so on as long as we have pairs of values.

    select max(decode(substr(rslt_period, 1, 1), -- Check the first character
                       '1', closure_date,
                            -- If it's '1' return the date we want
                           to_date('14/10/1066', 'DD/MM/YYYY'))),
                            -- Otherwise something old
           max(decode(substr(rslt_period, 1, 1),
                       '9', closure_date, -- The date we want
                           to_date('14/10/1066', 'DD/MM/YYYY'))),
    into dtPerSta, dtPerClosure
    from tperrslt
    where fiscal_year=to_char(Param_dtAcc,'YYYY')
      and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
                          '9' || to_char(Param_dtAcc,'MM'));

In this example, since we expect two rows to be returned, the problem is to retrieve in one row and two columns what would naturally arrive as two rows of a single column each (as in the array fetch example). We do that by checking each time the column that allows distinction between the two rows, rslt_period. If the row is the required one, the date of interest is returned. Otherwise, we return a date (here the arbitrary date is that of the battle of Hastings), which we know to be in all cases much older (smaller in terms of date comparison) than the one we want. By taking the maximum each time, we can be ensured that the correct date is obtained. This is a very practical trick that can be applied equally well to character or numerical data; we shall study it in more detail in Chapter 11.

SQL is a declarative language, so try to distance your code from the procedurality of business specifications.