DWHPro Insights

product join

Improving SQL Performance with Simple Query Rewrites: Dealing with Duplicates and Business Calendars

Illustration of a glossy orange database cylinder with data fragments across its surface

The Teradata flavor of SQL is still, in principle, a declarative language. Hence, there can be multiple ways to describe an SQL query and achieve the same result. While the answer is the same, Teradata may use a completely different execution plan based on how the query is expressed. One approach is investing in heavy …

Read more

Teradata Join Strategies: Merge, Hash, Nested and Product Joins Most read

Illustration of a stopwatch dial on an orange paint-splash background

Introduction Teradata offers several methods for conducting joins, but all necessitate one prerequisite. 🎓 Prefer video? Lectures 8 and 10 of my Udemy course Teradata Performance Tuning in Practice cover this topic: “Merge, hash, nested and product joins” and “Sliding-window merge join on partitioned tables”. See the course on Udemy → The paired table rows …

Read more

SQL Tuning Goals: Improving Performance and Reducing Resource Usage Widely read

Illustration of a stopwatch dial on an orange paint-splash background

Learn about the goals of SQL tuning and how to optimize database performance by reducing resource usage. Skew, IOs, and CPU seconds are key metrics. Discover how to ensure completeness and correctness of Teradata statistics, detect missing and stale statistics, and improve query plans.

Understanding Teradata DBQL Tables and Query Logging Most read

Illustration of a person in a suit standing beside a database cylinder

Learn about Query Logging with Teradata DBQL Tables, a powerful feature for workload analysis and performance tuning. Configure settings and select which key figures to store and their level of detail. The article covers how to implement and activate DBQL tables, determine which information to collect, and analyze tactical queries.

Teradata Join Strategy: The Benefits and Costs of Merge and Product Join Methods Widely read

Illustration of a stopwatch dial on an orange paint-splash background

Teradata uses different join methods and data-placement strategies. Rows to be matched must meet at the executing AMP, which can require duplication, redistribution or sorting. Some methods need less preparation than others, but lower preparation cost does not mean zero geography or resource cost. We should consider the following costs in the join strategy: The …

Read more

The Importance of Up-to-Date Statistics for Teradata SQL Tuning Widely read

Illustration of a stopwatch dial on an orange paint-splash background

The DBQL example needs configured logging and a bounded time window. HASHAMP()+1 assumes the relevant map/AMP scope; it may misrepresent a request using only a subset of AMPs. Use the actual participating-AMP count for meaningful skew/impact calculations, and clear temporary query bands after the measured workload. 1. Complete and up-to-date Statistics At the start of …

Read more

Optimizing Teradata Requests with Group-AMP Operations for Improved Performance

Illustration of a stopwatch dial on an orange paint-splash background

All-AMP describes participating AMPs, not a mandatory full-table or subtable scan. NUSI access can be narrowed by other predicates and maps; inspect the actual access steps. Group-AMP operations optimize requests by minimizing the number of active AMPs. These operations fall between single-AMP and all-AMP operations. The Optimizer employs group-AMP for data retrieval and joins. A …

Read more

Teradata SQL Tuning: How to Optimize Queries for Stable Runtimes Popular

Illustration of a glossy orange database cylinder with data fragments across its surface

Learn how to optimize Teradata SQL Tuning with comprehensive analysis of available tools in this article. Discover the optimal approach for query optimization for both tactical and strategic workloads, as well as methods for eliminating full table scans, reducing skew, and examining the execution plan. Plus, find out how to address common issues like concurrency and misestimation.

Protected by Security by CleanTalk and CleanTalk Anti-Spam