Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 08 Oct 2011 @ 09:15:41 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Stored Procedures - Cursor Processing
From:   Anomy Anom

<-- Anonymously Posted: Friday, October 07, 2011 18:08 -->

Hi David,

I forwarded your question to one of the Teradata developers and she recommends that you use a table UDF to split up text strings. See her response below.

Teradata recommends using a UDF to split up text strings. Since it sounds like you need to take a single input (string value) and return multiple outputs (split up text string), Teradata recommends using a Table UDF to do this. A Table UDF returns a table of multiple column values as a result.

Teradata s basis for this recommendation is that the way a UDF is executed on a Teradata Database system is much more efficient than the Stored Procedure with cursor processing. The stored procedure will not be taking advantage of Teradata s parallel capabilities.

The way the stored procedure will work is it has to fetch one row at a time via the cursor, and uses a single thread to do this. Then the result set has to be sent back to the dispatcher before it can be returned to the client. So the stored procedure is very inefficient because it only uses 1 CPU from all available on the system to do the processing. There will be additional latency due to the fact that there will need to be message passing between the AMPs and dispatcher.

A UDF, on the other hand, will provide the most efficient execution path possible for this type of operation. A UDF is executed on the AMPs in parallel, and each UDF being called has its own thread allocated (unlike the stored procedure). A UDF thus uses CPU very efficiently and takes advantage of Teradata s parallel architecture.

You also mentioned that Oracle does the same type of operation very efficiently, while Teradata Database does it inefficiently. The reason for this is because Oracle has a shared-everything form of architecture, while Teradata is shared-nothing. So there is a fundamental difference in the architecture that means that some operations will need to be written differently for Oracle and Teradata in order to get better performance.

If you do not know C, then you can write a table UDF in the Java language (see recommendation above). One thing to note is that we do provide a Teradata Domain Specific Function to split up text strings which will be available in Teradata Database 14.0. Unfortunately, a C UDF that does this type of operation is not available for download at this time.

Here are some notes as to performance of UDFs in the C and Java languages.

For Teradata Database 13.10, we had enhancements done to the Java UDF architecture to improve its performance over the original implementation.

With these enhancements, Java UDF performance was approximately doubled.

The current performance of Java UDFs is about 10% slower than an equivalent C UDF in protected mode. This 10% is measured in CPU time; it may vary slightly when using elapsed time for measurement.

In Teradata Database 13.10, this performance enhancement is disabled by default and can be turned on via a DBSControl flag. The performance enhancement is enabled by default in Teradata Database 14.0.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023