Back in week 27/2007 I wrote a tip about how to optimize a little bit on a type of SQL statements that are commonly used in most DB2 applications. With DB2 version 8 in new function mode it has become possible to optimize this type of SQL statements further. The precondition is that the SQL statement already only returns one row (in other words it is a SELECT INTO). Actually it is a vast improvement. I have seen improvements about 60-70% on CPU usage and up to 80% less getpage requests. This is really good stuff.

An example of a SQL statement before the optimization may look like this (you see similar SQL statement on almost any DB2 installation):

SELECT columna, columnb
INTO :columna, :columnb
FROM tablex x
WHERE keycolumn1 = :key
  AND keycolumn2 =
( SELECT MAX(keycolumn2)
  FROM tablex
  WHERE keycolumn1 = x.keycolumn1
)

If you have already implemented my previous proposal the last line is changed to WHERE keycolumn1 = :key. With DB2 version 8 in new function mode (and of course DB2 version 9) you can rewrite the above SQL statement to:

SELECT columna, columnb
INTO :columna, :columnb
FROM tablex
WHERE keycolumn1 = :key
ORDER BY keycolumn2 DESC
FETCH FIRST ROW ONLY

This is a fully legal SQL statement in DB2 version 8 in new function mode. If your DB2 is not fully upgraded you will receive a syntax error, because it has not been allowed to use ORDER BY on a SELECT INTO. Gone is the irritating subselect that was needed to ensure that the row with the highest value of keycolumn2 was returned. This is now ensured by the ORDER BY keycolumn2 DESC and as I have already stated this is far more efficient than the subselect version no matter how it was coded.