DWHPro Insights

Introduction to Teradata Big Data Blocks: Advantages and Disadvantages

By Roland Wenzlofsky ·

Introduction to Teradata Big Data Blocks: Advantages and Disadvantages

Teradata Big Data Blocks – Introduction

Teradata 13.10 introduced larger file systems components, such as cylinders and data blocks. The cylinder size was increased from 1.9 MB to 11.3 MB, while the data block size limit was raised from 127.5 KB to 1 MB in Teradata 14.10.

Current Database Limits documentation lists maximum non-spool row size of 1 MB and internal spool rows around 1 MB. The 64 KB table/result-row statement describes historical limits. Individual columns, sort keys, DBS settings and client transport can impose separate limits.

Implementing these measures can improve system performance. However, there may be certain situations that could also lead to a decrease in performance. We will provide a comprehensive analysis of the benefits and drawbacks of these modifications.

Advantages

Larger data block sizes enable the storage of additional rows per block, facilitating the reading and writing of numerous rows per I/O operation. This advantage is due to the performance benefits associated with larger rows.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
  • Firstly, the performance of queries that use large spool tables can be improved. Typically, these are queries doing full table scans, such as queries related to strategic workload.
  • Full table scan performance improvement is not restricted to read operations but includes updates and insert…select statements.
  • The performance of TPT jobs with an update, insert, and delete steps can be improved.
  • Depending on the data’s nature, big data blocks can improve the compression factor of block-level compression (BLC). With each data block holding a more significant number of rows, the algorithms used for block-level compression may achieve better results when scanning the rows for common compressed patterns.
  • This approach will improve the performance of tables containing wide rows. For example, when using 127.5 KB-sized data blocks, only one 64KB row fits each data block. The system needs one I/O to read each row. By increasing the data block size, I/O operations will become more efficient.
  • The performance of sort operations can be faster than with smaller data blocks, as each sorted buffer can simultaneously hold and process more rows.

The above-mentioned performance improvements will, however, increase memory demand per AMP.

Disadvantages

  • As the last statement above hints, big data blocks will need more memory for reading, writing, sorting data, and joining partitioned tables. Insufficient AMP memory can be the result.
  • The performance of the tactical workload can decrease. As tactical workload is characterized by index access (such as the primary index), typically, only one or a few rows are located. Teradata has to move 1MB instead of 127.5 KB from the disk to the FSG cache – to reach one row – the transfer costs are higher, and more memory is consumed.
  • Similarly, TPT Stream and TPump performance can decrease for the same reason.
  • Large blocks can increase memory demand for partitioned joins. Whether partitions can share physical blocks depends on storage format and release; this article does not establish a universal block-sharing rule.

Conclusion

Big data blocks can enhance performance but may not suit every situation. Fortunately, it is possible to decide on a per-table basis whether to use smaller blocks, even after upgrading to 1MB data blocks.

Big data blocks are ideal for strategic workloads, but it is advisable to revert to small blocks for tactical workload queries and row-partitioned tables with numerous partitions.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Introduction to Teradata Big Data Blocks: Advantages and Disadvantages”

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam