DB2 11.1 BLU Performance
One of the best features of DB2 BLU is that columnar tables are not in a separate database from row organized tables. They are both processed by the same runtime and you can combine both row and columnar tables in the same query. However, the actual processing of the columnar storage is by nature different then row processing, so there are different core processes to handle that data, and at some point the columnar data has to be converted to a row format. At the very least, the final result set is row formatted. Transformation also must be done to join row and columnar tables. But, in addition to these obvious cases, there are a number of SQL features and functions that just have not yet been implemented within the BLU engine.
The point of transition to row based processing is shown on the Explain output as a CTQ node (figure 1)
select * from joe.department_c d
where d.deptname = ?
order by d.deptno).
Figure 1
Once this has occurred, DB2 does not move back to columnar processing even where it might have been useful for performance. It is therefore good to have any CTQ nodes as high (later) in the processing as possible. Why does it matter?
- Performance is always improved if filtering occurs early – fewer rows are passed to the next step. If additional filtering does not occur until after the transition to row format, then much more data will have go through this transformation and larger intermediate temp tables will be created and processed.
- The additional processing will have to work with row organized tables rather than in columnar format. The type of queries and data that BLU is typically used for, perform better with columnar tables.
DB2 11.1 has added additional BLU engine support for a number of features:
Sorting
Sorting for an Order By or OLAP functions had not yet been processed in the BLU engine (figure 1). DB2 11.1 implements a new high performance parallel sorting algorithm (fast radix sort) that works on column organized data (figure 2 shows the Explain with DB2 11.1). Not only does it occur within the BLU engine, it also works on the compressed data without decompression; and it is fast. As of now, this new sorting algorithm is only used with BLU, not for row based tables.
Figure 2
Nested Loop Join
Prior to V 11.1, the only join method supported on columnar data was hash join (which is often the best choice for large amounts of data. However, even with analytical queries, filtering often reduces the qualifying data down to a small number of rows in one or both tables. In this case, a nest loop join (NL Join) may be better performing. NL Joins also support inequality join predicates, whereas hash joins do not. Figures 3 & 4 show the Explain for an inequality join for v10.5 and v11 respectively. The query is:
select * from joe.department_c d, joe.employee_c e
where d.deptno > e.workdept
order by d.deptname
Figure 3 (10.5) Note that the NLJoin had to be performed after converting to row format.
Figure 4 (11.1)
Support for additional functions
There are a number of builtin functions that prior to V11.1 were not yet implemented within the BLU Engine. These include OLAP functions such as ROW_NUMBER, RANK, RATION_TO_REPORT, OLAP columnar functions such as AVG, MAX, MIN, and scalar functions like:
LPAD
TO_CHAR
TO_NUMBER
MOD
MONTHNAME
Faster SQL Merge
Internal improvements were made to the processing of the SQL Merge statement on columnar tables.
BLU Declared Global Temporary Tables
Being able to declare a column organized temp table also allows more processing within the BLU engine.
In summary, the more processing that can be done without leaving the BLU engine, the better will the performance be.