2.16. Succinct SQLThe 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:[*]
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. |