50 - 82 of 82 tags for performance


Have you ever been bewildered trying to tune complex Teradata queries that go on for pages and pages of EXPLAIN text?  This presentation goes beyond "Explain the Explain", to teach strategies and techniques for quickly deciphering the most expensive operations in your queries.  Taught by a 20+ year Teradata veteran, examples of real queries gathered from some of the largest production environments in the world are used.  This is advanced material covered at a quick pace.

There are a number of factors that contribute to performance aspects of Teradata Viewpoint and enough to warrant an article on this subject. So if you are looking for improved Viewpoint responsiveness and snappier portlet refreshes, consider the aspects covered in this article.

i have 1 table with millions of records in it and wanted to write one query.

I have written 2 query's, since i am not a Teradata expert want to validate need your expert advice.

where extract(month from date) in (3,6,9,12)
and fscl_yr_nbr = case when extract(month from date) in (1,2,3,4,5) Then extract(Year from date) else extract(Year from date) + 1 end
and fscl_qtr_nbr = case when extract(month from date) in (12,1,2) Then 3
when extract(month from date) in (3,4,5) Then 4

I have TABLE A and TABLE B

I'm trying to select reg_no.... inner join ON A.id=B.id1,Id2,Id3......Id20. And i have 3 conditions in Where clause.
(A.Id could be equal to any of 20 colums, some times 1 or 4 or 5 of 20 columns too)

I'm writing as
ON (A.Id=B.Id1
OR A.Id=B.Id2
OR A.Id=B.Id20)

I have 7 millions of rows, but after where clause, rows are not more than few thousands can any one plz suggest me whether i'm doing right or any other simple way for my problem.

sample query:

SELECT reg_no
ON (A.Id=B.Id1
OR A.Id=B.Id2

Hi ,
I am using the following table to collect performance statistics on a query, but getting no rows.

SELECT CollectTimeStamp,
DefaultDatabase as DatabaseName,
(FirstRespTime - StartTime) hour to second(4) as FirstRespElapsedTime,
AMPCPUTime+ParserCPUTime TotalCPUTime,
SpoolUsage/(1024*1024*1024) as Spool_GB,
100-(nullifzero(AMPCPUTime/HASHAMP())/(MaxAMPCPUTime)*100) "Skew Factor",
FROM dbc.dbqlogtbl

This presentation will provide recommendations and guidelines for using leading practices to enable system and performance tuning.  You will learn the benefits of using Teradata's most recent set of recommendations to manage workloads on a Teradata system. For example, by using Teradata's data collection guidelines, recommendations for Account Management and Logging, a user will be able to identify, analyze and tune problem queries for better performance. This presentation will also focus on how to use the tools provided in the Teradata Analyst Pak. 

Teradata Data Mover makes moving tables and other objects between Teradata systems easier than ever. However, the initial release, 13.01, does require manual tuning and configuration in order to get the best performance results. Here are some tips and recommendations to improving Data Mover performance.

There is a BI tool-generated SQL query which (full) joins multiple, 30 in total, smaller SQL as following:

(select a14.Store_ID Store_ID, sum(a11.CountConnections) WJXBFS1
from Retail_Views.F_ACM_Connections a11, ....
where filterset = 1) pa11

(select a14.Store_ID Store_ID, sum(a11.CountConnections) WJXBFS1
from Retail_Views.F_ACM_Connections a11, ....
where filterset = 2) pa12


into a query like:

select coalesce(pa11.Store_ID, ..., pa130.Store_ID) Store_ID, a132.StoreName StoreName, max(pa11.WJXBFS1) WJXBFS1, ..., max(pa130.WJXBFS1) WJXBFS30
from ....

From Books:

When doing collect stats on fields/indexes , system collects the information like: total row counts of the table, how many distinct values are there in the column, how many rows per value, is the column indexed, if so unique or non unique etc

The above information are known as statistics.
Table "Emp_Details" has the primary index "Emp_Name". I use this index "Emp_Name" in my joining condition/Where clause.

case1: I dont have collect stats on "Emp_Details" table.


Seeking configuration assistance for TD SQL Assistant v13.x TO BYPASS CONNECTION PERFORMANCE LIMITATIONS

The purpose of this series was to give you some basic queries that I use to provide me with a quick snapshot of how well tuned an EDW is from a workload analysis and database tuning perspective.

The four topics were (direct links are provided at the end of the article):

  • Part 1 - Excessive Use of String Manipulation Verbs
  • Part 2 - Analyze Secondary Index Usage
  • Part 3 - Statistics Analysis
  • Part 4 - Compression Analysis

Have you tried them yet??

My batch is completing late, the tactical queries show inconsistent response times, some applications show extreme variability in resource consumption, the canaries are hoarse!
Bad queries need to be fixed! We will explain why certain queries are bad and visualize how they impact Teradata performance. We will show how to find them, discuss most common mistakes, and give tips on how to fix them. Techniques to measure 'incoming traffic' from highly volatile applications will be discussed.

This session provides an overview and demonstration of the TASM portlets and TASM feature functionalities.

This is just a quick note to put some information out here to see if it helps you to more easily create charts from the Resusage data.

Much of the information you should be tracking comes from DBC.ResUsageSPMA, and that is what this process is going to analyze. Note that this process is for TD12 only.

Ok, so I shouldn’t even need to broach this topic, as I’m sure you have all heard it before: compress, compress, and compress.

In Part 3 of this series, we will take a quick look at how statistics are implemented and maintained at your site.

Statistics Collection can be a complicated and very deep-dive topic, with discussions on the frequency of collection, whether to use sampled stats, automation strategies, etc. This analysis is not going to go that deep, it is a high-level look at the statistics on the tables, and I am looking for just two things:

  • Are statistics applied to the tables or missing?
  • For those that are applied, is there consistency in the application and collection process?

In Part 1 of this series, we looked at the Excessive use of String Manipulation Verbs in a query. Here is that link in case you missed it:

When creating tables in Teradata is there an advantage to placing the column(s) that will be used for the Primary Index at the top of the column list?

Are there any recommendations, from Teradata or otherwise, as to what data types to avoid or limit due to database overhead or performance considerations, etc?

On page 885 of the latest Database Design manual it says "When neither CHARACTER nor VARCHAR is clearly a superior choice, use VARCHAR because VARCHAR data requires slightly less CPU resource to manipulate than CHARACTER data."

This is not intuitive and I wonder if anyone can verify or explain this?

Hi, I am new to SQL and to Teradata so I am sorry if this is a stupid question. I studied real-time programming in school using C. Recently I saw a SQL statement in a BTEQ that didn't sit right with me. Unfortunately I don't have access to Teradata at work. I will have to wait until I get home to play with it but I can't find any fault in my logic. So here goes.

The Teradata JDBC Driver and ODBC Driver allow developers to quickly build applications that interact with the Teradata Database. However, many developers are surprised when their fully functioning application suddenly hits a performance roadblock when it is deployed to their production environment.

Does anyone have some working PM API C code they would like to share? I'm beating my head against the supplied sample code.

Every time I run it I get: "Create Monitor Object failed! 340: Class not registered".

Your help and suggestions are appreciated.

We all know the benefits of identifying and fixing badly performing workloads. Many sites use the common method of identifying the top n highest consuming or skewed queries (often the same queries) and fixing those.

This is the fifth in a series of articles.
       View the first article.
       View the series index.

This is the third in a series of articles.
       View the first article.
       View the series index.

To view detail information about the sessions logged on to the Teradata system you should use the GetSessionData method. This method returns a Sessions collection. The sessions in this collection are sorted by user name.

This is the second in a series of articles.
       View the first article.
       View the series index.

Resource information is available in both summary and detail formats. Each type of information is also available at both the physical (Node) and virtual (Vproc) level.

The Teradata Performance Monitor Object is a COM object that provides methods which encapsulate the code required to fetch performance information from a Teradata Database. In most cases the data is returned as a collection of performance objects.

These articles refer to the version of the object that ships with Teradata Manager 12.0, and 13.0.

This article discusses the use of the Teradata Performance Monitor (COM) object which makes real time Teradata performance information available to a Windows application in a simple object oriented manner.