Recursive SQL Archives -

Improving Stored Procedure Performance with Teradata MAPS Architecture

03/08/202605/05/2023 by Roland Wenzlofsky

Occasionally, it is necessary to utilize a cursor within a stored procedure to execute specific functionality. I recently encountered a stored procedure that contained a loop with multiple INSERT statements executed through a cursor. This particular cursor was designed to process only a limited number of rows. Despite this, the stored procedure took up to …

Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR

04/11/202605/05/2023 by Roland Wenzlofsky

In a previous article, Roland explained how to use recursion to build a data range without touching a physical table. This time, we explore an alternative approach that avoids recursion at the expense of single-AMP, single-row access. We can use this approach to build both a wide date range as well as a narrow one. We …

The Teradata Recursive Query for Performance Tuning

04/11/202605/02/2023 by Roland Wenzlofsky

Problem-solving without a Teradata Recursive Query To depict a corporate hierarchy in our instance, we can employ a non-recursive approach, illustrated by the query presented below: The above query has several harmful properties: Problem-solving with a Teradata Recursive Query The above recursive query has several benefits: The only modification required to enlarge the Company_Hierarchy column …

Two Methods for Generating Number Ranges in Teradata

03/08/202604/28/2023 by Roland Wenzlofsky

Method 1, avoiding any helper tables or functions which are only available in new releases of Teradata: Method 2, with helper tables and XMLAGG: Both methods deliver the string “1,2,3,4,5,6,7,8,9,10”. Which process do you believe is using fewer Disk IOs and CPU resources?

An Introduction to Teradata Recursions: Generating Date Ranges Without a Physical Table

04/11/202604/27/2023 by Roland Wenzlofsky

Introduction to Teradata Recursions To link records from a primary table with a specific date range from a secondary table, a common requirement is to create a simulated history table with snapshot dates using a change history table with defined timelines. Consider the following illustration: The proposed solution would resemble this: You must create your …

Mastering Teradata Recursive Queries: How to Create and Use Them for Shortest Path Problems

04/11/202604/20/2023 by Roland Wenzlofsky

Learn how to create Teradata recursive queries using either the WITH RECURSIVE clause or CREATE RECURSIVE VIEW statement in this guide. Explore a real-world example of finding the shortest paths in a graph through a recursive solution.