Tuple Reconstruction

Abstract

As mentioned earlier, data with matrix characteristics can be stored in linear memory either column by column (columnar layout) or row by row (row layout). The impacts were already discussed in Chap. 8 in more detail.The columnar layout is optimized for analytical set-based operations that work on many rows but for a notably smaller subset of all columns of data.The row layout shows a better performance for select operations on few single tuples. In this chapter, we discuss the operations needed for tuple reconstruction in detail and explain the influence of the different layouts on the performance of these operations. Tuple reconstruction is a typical functionality in OLTP applications. It is executed whenever more than one column is requested from the database, for example when the user in an ERP system calls the “show” or “edit” transactions for the master data object or for a document.

Self Test Questions

 

• 1. Tuple Reconstruction on the Row Layout: Performance

  Given a table with the following characteristics:

  • Physical storage in rows

  • The size of each field is 34 byte

  • The number of attributes is 9

  • A cache line has 64 byte

  • The CPU processes 2 MB per millisecond.

  • Calculate the time required for reconstructing a full row. Please assume the following conversions: $1\,\text{{MB}} = 1{,}000\,\text{{kB}}$, $1\,\text{{kB}} = 1{,}000\,\text{{B}}$

  • (a) $\approx$ 0.1 $\upmu$s

  • (b) $\approx$ 0.275 $\upmu$s

  • (c) $\approx$ 0.16 $\upmu$s

  • (d) $\approx$ 0.416 $\upmu$s

• 2. Tuple Reconstruction on the Column Layout: Performance

  Given a table with the following characteristics:

  • Physical storage in columns

  • The size of each field is 34 byte

  • The number of attributes is 9

  • A cache line has 64 byte

  • The CPU processes 2 MB per millisecond.

  • Calculate the time required for reconstructing a full row. Please assume the following conversions: $1\text{{MB}} = 1{,}000\,\text{{KB}}, 1\,\text{{kB}} = 1{,}000\,\text{{B}}$

  • (a) $\approx$ 0.16 $\upmu$s

  • (b) $\approx$ 0.145 $\upmu$s

  • (c) $\approx$ 0.288 $\upmu$s

  • (d) $\approx$ 0.225 $\upmu$s

• 3. Tuple Reconstruction in Hybrid Layout

  A table containing product stock information has the following attributes:

  Warehouse (4 byte); Product Id (4 byte); Product Name Short (20 byte); Product Name Long (40 byte); Self Production (1 byte); Production Plant (4 byte); Product Group (4 byte); Sector (4 byte); Stock Volume (8 byte); Unit of Measure (3 byte); Price (8 byte); Currency (3 byte); Total Stock Value (8 byte); Stock Currency (3 byte)

  The size of a full tuple is 114 byte.

  The size of a cache-line is 64 byte.

  The table is stored in main memory using a hybrid layout. The following fields are stored together:

  • Stock Volume and Unit of Measure;

  • Price and Currency;

  • Total Stock Value and Stock Currency;

  All other fields are stored column-wise.

  Calculate and select from the list below the time required for reconstructing a full tuple using a single CPU core. Please assume the following conversions: $1\,\text{{MB}} = 1{,}000\,\text{{kB}}, 1\,\text{{kB}} = 1{,}000\,\text{{B}}$

  • (a) $\approx$ 0.352 $\upmu$s

  • (b) $\approx$ 0.020 $\upmu$s

  • (c) $\approx$ 0.061 $\upmu$s

  • (d) $\approx$ 0.427 $\upmu$s

• 4. Comparison of Performance of the Tuple Reconstruction on Different Layouts

  A table containing product stock information has the following attributes:

  Warehouse (4 byte); Product Id (4 byte); Product Name Short (20 byte); Product Name Long (40 byte); Self Production (1 byte); Production Plant (4 byte); Product group (4 byte); Sector (4 byte); Stock Volume (8 byte); Unit of Measure (3 byte); Price (8 byte); Currency (3 byte); Total Stock Value (8 byte); Stock Currency (3 byte)

  The size of a full tuple is 114 byte.

  The size of a cache-line is 64 byte.

  Which of the following statements are true?

  • (a) If the table is physically stored in column layout, the reconstruction of a single full tuple consumes $\approx$0.192 $\upmu$s using a single CPU core

  • (b) If the table is physically stored in row layout, the reconstruction of a single full tuple consumes $\approx$128 ns using a single CPU core

  • (c) If the table is physically stored in column layout, the reconstruction of a single full tuple consumes $\approx$448 nanoseconds using a single CPU core

  • (d) If the table is physically stored in row layout, the reconstruction of a single full tuple consumes $\approx$0.64 $\upmu$s using a single CPU core.