DWHPro Insights

The Impact of Skewing on Teradata demonstrated

By Roland Wenzlofsky ·

The Impact of Skewing on Teradata demonstrated

Teradata professionals inevitably encounter the detrimental effects of data skew on query performance.

Yet, I believe only a handful of us know the precise extent of impact we should anticipate.

CPU and I/O provide useful context alongside elapsed time, but vary with hardware, cache, plans and workload. DBQL logging and permissions must be configured before measurements can be retrieved.

I conducted tests on both SET and MULTISET tables to broaden the test scenario. The rationale behind incorporating this attribute at the table level was to:

Considering the impact of skewing on performance, wouldn’t it be necessary to impose duplicate row checks on a SET table, thereby increasing the workload on the AMPs affected by skewing?

The configuration of my test environment is as follows:

CREATE SET TABLE TABLE_NOT_SKEWED (A INTEGER, B INTEGER) PRIMARY INDEX (A) ;
CREATE MULTISET TABLE TABLE_NOT_SKEWED_MULTISET (A INTEGER, B INTEGER) PRIMARY INDEX (A) ;
CREATE SET TABLE TABLE_SKEWED (A INTEGER, B INTEGER) PRIMARY INDEX (B) ;
CREATE MULTISET TABLE TABLE_SKEWED_MULTISET (A INTEGER, B INTEGER) PRIMARY INDEX (B) ;

I generated both a skewed and unskewed table as both SET and MULTISET. I populated them with data, wherein Column A is nearly unique, and Column B contains only a handful of distinct values.

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
INSERT INTO TABLE_NOT_SKEWED  SELECT RANDOM(1,1000000), RANDOM(1,5) FROM  SYS_CALENDAR.CALENDAR;
INSERT INTO TABLE_NOT_SKEWED_MULTISET  SELECT RANDOM(1,1000000), RANDOM(1,5) FROM  SYS_CALENDAR.CALENDAR;
INSERT INTO TABLE_SKEWED  SELECT RANDOM(1,1000000), RANDOM(1,5) FROM  SYS_CALENDAR.CALENDAR;
INSERT INTO TABLE_SKEWED_MULTISET  SELECT RANDOM(1,1000000), RANDOM(1,5) FROM  SYS_CALENDAR.CALENDAR;

After careful analysis, I examined DBC.DBQLOGTBL:

SELECT QueryText, MaxAMPCPUTime, MinAMPCPUTime, MaxAmpIO, MinAmpIO, TotalIOCount, AMPCPUTime
FROM DBC.DBQLogTbl
WHERE SessionID = 12345
  AND StartTime >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR;
-- Replace 12345 with the measured session ID and identify the exact query.

Below you can see the result of this test. Here are my observations:

QueryTextMaxCPUMinCPUMaxIOMinIOTotalIOAMPCPU
INSERT INTO TABLE_NOT_SKEWED…0,01041393.6190,33
INSERT INTO TABLE_NOT_SKEWED_MULTISET…0,02038403.5340,44
INSERT INTO TABLE_SKEWED…8,59041.53613204.65142,44
INSERT INTO TABLE_SKEWED_MULTISET…0,03086111.3500,33

SET TABLES

The historical insert measurements show higher I/O for the skewed SET case. They do not establish retrieval cost, and independently randomized inputs confound the comparison.

The reported 41,536 versus 13 I/Os gives a ratio of approximately 3,195, not 4,000. This is a historical observation, not an expected ratio for another workload.

MULTISET TABLES

Multiset tables require fewer disk accesses than their set table counterparts because the expensive duplicate row check is omitted.

The skewed multiset table requires fewer disk accesses than the unskewed table by less than 50%. However, this does not necessarily indicate that it is superior.

Most rows are likely adjacent on a small number of AMPs, which reduces disk access by utilizing memory caching and optimizing block reading methods.

Unfortunately, only a limited number of AMPs are assigned to manage numerous rows, resulting in a bottleneck. Therefore, it would be unwise to be deceived by these figures as inserting into the table remains significantly slower than when undertaken by a single or a select few AMPs.

Choose SET or MULTISET according to duplicate semantics. MULTISET can avoid SET duplicate-row checks, but it does not change PI distribution for identical retained rows or cure skew.

Reproducibility note

This historical experiment illustrates possible skew and insert costs. Independently generated random inputs and missing workload controls prevent interpreting its ratios as a controlled benchmark; the audit did not reproduce the performance figures.

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.

1 thought on “The Impact of Skewing on Teradata demonstrated”

  1. Hello, it is a very interesting approach but in my opinion you have to add two or three more things to have a complete test.

    What if I have already data on my table, how this impact the CPU / io for a multiset or set table?
    What if I will add more columns on the PI of my Set table in order to increase the information on the histogram(less group of values to check)?
    What if I add a USI on my Set table?

    Furthermore, always I have an impact on CPU/io consumption with a Set table, but if I have a UPI or USI defined, the behavior is the same since the bottleneck is not the skewed Data – this problem I will have it on both cases – but TD will recognize the unique values to insert, and this is controlled only from PI, USI, and stats.

    Ending, should it better to say, the higher amount of rows that I have on a skew table, the worse is the impact of the duplicate row check?

    Thank you.

    Reply

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