EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Teradata Tutorial Teradata Performance Tuning
Secondary Sidebar
Teradata Tutorial
  • Basic
    • What is Teradata
    • Career In Teradata
    • Teradata Architecture
    • Teradata data types
    • Teradata ODBC Driver
    • Teradata Vantage
    • Insert into Teradata
    • Teradata CASE Statement
    • Teradata Partition by
    • Teradata Date Formats
    • Teradata Current Date
    • Teradata Substring
    • Teradata BTEQ
    • Teradata Concatenate
    • Teradata REPLACE
    • Teradata Joins
    • Primary Index in Teradata
    • Fastload in Teradata
    • Collect Stats in Teradata
    • Teradata Volatile Table
    • Teradata TPT
    • Teradata Qualify
    • Teradata?coalesce
    • Teradata RENAME TABLE
    • Teradata Viewpoint
    • Teradata Performance Tuning
    • Teradata AMP
    • Teradata Utilities

Teradata Performance Tuning

Teradata Performance Tuning

Introduction to Teradata Performance Tuning

Teradata performance tuning involves a. Optimization of its database by normalizing key tables, using correct data types, rightsizing the number of columns, avoiding null values, using right join operations, and deploying correct indexes and server cache b. Fine-tuning database quires by tweaking SQL to the benchmark standards and following certain dos and don’ts while coding SQL statements and c. Altering Compute, storage, and network environment and parameters to enhance the database performance.

Performance tuning is essential to any organization to improve customer experience by improving front-end response time with faster retrieval of data from the database and cutting unwanted database activities. Any slowness in the database will reflect in application performance. In digital business operations, any such slowness will drive the customers to move away from competitors, resulting in a financial loss to the company.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In this article, let’s examine ways and means of performance tuning in Teradata applications.

Features of Teradata

  • One of the renowned relational database management used mainly in building very large data warehousing applications.
  • Owned by a company called Teradata started in 1979 and constantly upgraded till 2014
  • The workload is shared among multiple Processors and offers abundant parallel processing capabilities.
  • Has dedicated processor nodes with attached disk storage to store records evenly and retrieve them independently.
  • It is scalable linearly with the addition of nodes, and it can be easily connected to mainframe or Network systems.
  • It supports SQL, a host of utilities to facilitate the uploading of data into the Teradata database.

Steps in Teradata performance tuning

As mentioned earlier, Teradata performance tuning can occur at three levels viz., a. Database level, b. Optimizing SQL queries, c. Tweaking hardware environment. Various steps in all the levels are explained below.

1. Database Tuning

The database structure should be sound enough to store large volumes of data and retrieve them faster to ensure better performance of the Teradata system.

Normalizing the tables

  • Each column should have a unique name, and it should have a single value
  • Every table should have a primary key field, and all the non-key fields in the table should be functionally fully dependent on the primary key.
  • There should not be any dependency between non-key fields in a table
  • Multivalued dependency should be avoided.

Benefits of Normalization are

  • It avoids duplication of records and the wrong multiplication of records during the join operation.
  • Improves consistency of data and accuracy in query results
  • Faster Execution due to nil redundant records and quicker Search operations using Index.

Indexing

Indexing helps in retrieving records faster by cutting short full table access. There are two types of Indexes, namely the Primary Index and Secondary Index. The primary index column should be unique, and the other columns should be dependent on the primary index column. Secondary indexes are created depending on the data demography and how that column is used in the where clause.

Partitioning

This process splits a large table into multiple smaller tables. Logical grouping of tables facilitates easy maintenance of records and quicker retrievals of records as the search is restricted to a smaller volume of data resulting in an overall reduction in response time.  The rows should be grouped and partitioned on a column in such a way that user accesses are restricted within the group and do not cut across partitions.

Usage of right data types

  • For the numeric field, the right-sized data type should be chosen as per the maximum value to be stored, and for non-numeric fields, it should be as per the maximum length. Shorter length data types are always better for better performance.
  • Date and time types should be used wherever it is necessary to avoid wasteful conversion.
  • The column used in the join operation should have the same type in all the tables to avoid unnecessary conversion during Join.

Avoid Null values

Null is a no value in database parlance, and generally, it should be avoided in columns involved in arithmetic, aggregation, string manipulations, and conditionals operations since the results are unpredictable. One way to avoid this error is to explicitly check the null value in the coding, but it slows the performance level.

Too many Columns

Avoid too many columns in a table as it has heavy overhead and pulls down the performance of the database wherever possible design tables with a smaller number of columns split logically for smoother and faster operation.

Server Query Caching

This Caching is also known as the pinning of tables in the memory of the server. Copies of the frequently accessed tables are maintained in the memory, and any queries accessing this table will refer to the hot copies from memory instead of accessing the database to get the results quickly and speed up the performance. Any change in the contents of the table will trigger refreshing the contents in the memory.

2. Query optimization

  • In where clause, the column with Indexes should only be used as far as possible, and a full table scan should be avoided.
  • Leading wild cards should be avoided since Indexes will not be used in such search operations.
  • Logical OR conditions should be used judiciously, and if one of the OR conditions is not on an indexed column, then the optimizer may choose a full table scan. Alternatively, a union clause can be used.
  • Like expression where clause should be avoided as far as possible, the optimizer can choose a full table scan.
  • Instead of IN expression, EXISTS should be used to improve database performance.
  • Use DISTICT in lieu of GROUP BY to save query time
  • Avoid UNION and use UNION ALL since UNION takes more time in filtering duplicates
  • Have manageable smaller SQL statements against a big one.
  • Use Joints with a fewer number of tables and fine-tune them.

3. Monitoring Database performance and fine-tuning

  • Statistics should be collected for critical columns depending upon the data demography, and it should also be collected for the non-index column used in the where clause. Statistics should be updated frequently.
  • Monitor whether the execution plan uses an index or resorting to a full table scan and study the bottlenecks.
  • Use Optimizer and Explain options along with statistics data to track whether Indexes created are used effectively and their impact on the performance of the database.
  • Add new indexes and drop existing indexes.
  • Suggest code changes in SQL where clause to overcome bottleneck.

Conclusion

The performance of the database should be monitored continuously using DB tools, and any bottle necks should be analyzed and resolved. Along with DB monitoring, application design and coding should also be revisited for a stable performance level.

Recommended Articles

This is a guide to Teradata Performance Tuning. Here we discuss the ways and means of performance tuning in Teradata applications along with the features and steps. You may also have a look at the following articles to learn more –

  1. Insert into Teradata
  2. Teradata Current Date
  3. Teradata CASE Statement
  4. Teradata Partition by
Popular Course in this category
Data Visualization Training (15 Courses, 5+ Projects)
  15 Online Courses |  5 Hands-on Projects |  105+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more