DWHPro Insights

Unnecessary IO Elimination – Teradata View Optimization

By Roland Wenzlofsky ·

Unnecessary IO Elimination – Teradata View Optimization

ProfilePic

 

Summary

Today I will show you how to identify the source problem to tune the query’s performance utilizing the unnecessary amount of IO, the UII indicator, and how the WHERE clause placed on a VIEW is evaluated.

A WHERE predicate on a view can be pushed into underlying operations. The historical case requires its actual plan and definitions to establish where filtering occurred; view syntax alone does not force reading every row first.

Performance tuning boosted the performance fivefold. The query is completed 4-5 times faster and utilizes 4-5 times fewer system resources:

Teradata View Optimization

1+ Billion rows INSERTED

Here is the target query for today, which populates a large fact table of the data mart.

– INSERT INTO … SELECT FROM …

– The query is run daily

– Over 1000M rows inserted (1 billion)

INSERT INTO PRD3_1_db_dmcrmuakb.t_crm_ft_campaign_membership  (         …        ) SELECT  … FROM    PRD3_1_db_dmcrmuakb.v_crm_ft_campaign_membership WHERE   (crm_wave_launch_dt >=  (TD_SYSFNLIB.OADD_MONTHS (DATE , -1 )))  /* Today – 1 month */     AND  (crm_campaign_enter_dt >=  (TD_SYSFNLIB.OADD_MONTHS (DATE , -1 ))) /* Today – 1 month */ ;

Poor performance detected

Query performance statistics stored in Teradata QueryLog signify that the query is suspicious and performs poorly in unnecessary IO, excessive Spool space, and CPU utilization.

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

Take a look at Teradata performance metrics:

Teradata View Optimization

What knowledge can we discover from this data?

What is particularly wrong, and how can we tune and improve the query?

A closer look at IO, source view DDL

First, let us describe what UII (Unnecessary IO indicator) is and what it can tell us.

UII = SumIO / (SumCPU * 1000) is a diagnostic ratio, not proof of unnecessary reads. Guard a zero CPU denominator and interpret it with row counts, access paths and the workload.

Let us examine source View DDL through which the target table is populated.

REPLACE VIEW SBX.V_CRM_FT_CAMPAIGN_MEMBERSHIP AS LOCKING ROW ACCESS SELECT     … FROM SBX.T_CRM_CAMPAIGN_MEMBER CCM     JOIN SBX.T_MD_CRM_CAMPAIGN  CP          ON  (             …             AND CP.WAVE_LAUNCH_DATE>=OADD_MONTHS(CURRENT_DATE,-6)             AND  CCM.CAMPAIGN_ENTER_DATE >=OADD_MONTHS(CURRENT_DATE,-6));

A-ha! Here we see that the latest six months are extracted from the view.

The tables are partitioned by columns used in WHERE clause:

CREATE MULTISET TABLE SBX.T_CRM_CAMPAIGN_MEMBER ,FALLBACK ,      NO BEFORE JOURNAL,      NO AFTER JOURNAL,      CHECKSUM = DEFAULT,      DEFAULT MERGEBLOCKRATIO      (                …         ) PRIMARY INDEX ( MEMBER_ID , WAVE_ID ) PARTITION BY RANGE_N(CAMPAIGN_ENTER_DATE  BETWEEN DATE ‘2014-12-01’ AND DATE ‘2025-12-31’ EACH INTERVAL ‘1’ MONTH );

But only last month’s data is inserted in the target table! The INSERT statement is as:

INSERT INTO PRD3_1_db_dmcrmuakb.t_crm_ft_campaign_membership  (         …        ) SELECT  … FROM    PRD3_1_db_dmcrmuakb.v_crm_ft_campaign_membership WHERE   (crm_wave_launch_dt >=  (TD_SYSFNLIB.OADD_MONTHS (DATE , -1 )))  /* Today – 1 month */     AND  (crm_campaign_enter_dt >=  (TD_SYSFNLIB.OADD_MONTHS (DATE , -1 ))) /* Today – 1 month */ ;

Here is the answer:

1. We might put the WHERE clause from the final query INSIDE the source VIEW DDL.

2. We might create an entirely new VIEW for this ETL process if some other users might not want any changes to the VIEW.

Improved query performance

This optimization gave us five times boost in performance. The query is completed 4-5 times faster and utilizes 4-5 times fewer system resources.

Detailed query metrics are below:

Teradata View Optimization

Artemiy Kozyr is Data Engineer at Sberbank, Moscow, with a Master’s Degree in CS.
He has five years of experience in Data Warehousing, ETL, and Visualization for Financial Institutions.

Contacts:

ar***********@***il.com
http://linkedin.com/in/artemiykozyr/

Validation scope: The view/table definitions and original workload are incomplete, so the fivefold benefit was not reproduced. The illustrated partition range ending in 2025 must be maintained for later data; handle out-of-range rows explicitly.

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 “Unnecessary IO Elimination – Teradata View Optimization”

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