Query writing is an art. When a painter starts on the canvas he has a clear understanding of what he is going to draw on the empty canvas; he can’t just start with something and change his mind in the middle of the drawing. Imagine Leonardo da Vinci drawing Mona Lisa and half way in-between he changes his mind and starts drawing Medusa.

Data volumes are on the continuous rise and the size of the data warehouse has been increasing more than ever. In such a scenario, writing queries efficiently has become even more important. For a developer or a data scientist, using SQL still remains a very powerful method of analyzing data and building reports and trends. 

Here we will assume that the reader is well versed with Teradata SQL syntax and has all the required tools to connect and execute the queries on Teradata.

Every SQL statement that executes on Teradata database consumes CPU, IO, AWT, and Spool.

Teradata's architecture is based on parallelism, which essentially means the efficiency of the query is based on how parallel efficient the query execution is, meaning how well it could leverage Teradata's parallelism. However, it also has an exception which will be covered in a later part of the chapter.

Teradata has many tools for writing the queries; you can use SQLA or a BTEQ for executing queries. There are also many third-party...

Have you been struggling with your queries running for a long time, have your queries been flagged on more than one occasion, or have you been reprimanded by your DBAs multiple times regarding high system resource consumption? Are these some situations you have been through? If yes, the queries you are running are not efficient and need to be looked at for performance improvement/optimization.

There are three types of typical users that use Teradata database:

• Business users
• Developers
• Administrators

The administrator community obviously wants the queries running on the Teradata system to be as efficient as possible.

Here, we will look at some basic rules that need to be kept in mind when...

Teradata gives you the benefit of checking the actual execution plan before executing the query. The English translation of the plan the Teradata optimizer develops to service the SQL. The terms execution plan and explain plan can be used interchangeably:

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;
DIAGNOSTIC HELPSTATS ON FOR SESSION;

VERBOSEEXPLAIN adds information on the hash fields used to build intermediate spool files. This can be of significant value when diagnosing skewed redistribution.  

HELPSTATS adds a list of RECOMMENDED STATISTICS to the end of the explain plan.  A word of caution: do not blindly collect stats from this list of recommendations. The recommendation usually covers all joining columns with single as well as multi-column options and it may prove costly to collect them. 

<span>Even though the EXPLAIN plan is in plain English, it is not easy to understand the underlying meaning without understanding the main keywords. At first look, it possibly won't make any sense to you and you may not relate it to the query. We will decode the output into its pieces to carve out the relevant information behind it.

1. Identify the query for which you want to check the explain.
2. It is recommended to check the explain of all the queries before executing, as query plans get changed if table demographics are changed.
3. Hit F6 by selecting the query to get the explain, or write the EXPLAIN keyword before the query and press F5:

Explain
Insert  Into  test01.webclicks...

In statistics, skewness is a measure of the asymmetry of the probability distribution of a real-valued random variable about its mean. And in Teradata, it is defined as imbalanced processing, caused by uneven distribution. Highly skewed means some AMPs have more rows and some much less, as in data is not properly/evenly distributed. We can have data skew, CPU skew, and IO skew.

Shared Nothing architecture – dividing the work

The shared nothing architecture ensures that each virtual processor is responsible for the storage and retrieval of its own unique data. Data is stored physically together on the node, but the virtual processors ensure parallelism. This is also the basis of Teradata scalability. Each AMP owns an equal slice of the disk:

We will now understand how we can detect skew in a table which has a bad PI and how we can resolve it...

A database can run out of space if it has excessive tables that are skewed. Skew in small tables is understandable and can't be controlled, whereas skew in big tables can be controlled and reduced.

We will connect to our Teradata database instance and open SLQA to generate the list of tables with their space and skew details.

1. Pick the database on which you need to analyze the skew tables.
2. Execute this query to get the list of tables...