Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 04 Oct 2011 @ 18:15:23 GMT

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

Subj:   Re: Stored Procedures - Cursor Processing
From:   Dieter Noeth

David Clough wrote:

  Why, oh why, oh why do Stored Procedures perform so appallingly bad in Teradata, when they include Cursor processing ?  

It's also bad on other DBMSes, but especially bad on Teradata, as you're running a sequential FETCH NEXT on a parallel system.

  I've been involved in looking at a procedural solution for splitting up text strings - themselves containing numeric keys - deriving a business equivalent code for those split up numeric values, and then stringing those codes back together.  

  We tried two types of solution with Stored Procedures and Cursor processing (one solution even using my one and only ever Recursive Procedure) but the performance was so bad it was embarrassing !  

The recursive version shouldn't be too bad (just consuming lots of spool), but i tested some UDFs to split/concat comma-delimited strings, these are the fastest way (Teradata's Sergio Ferragut wrote them a few years ago).

  I say embarrassing above because when I showed it to my Oracle inclined colleague he was at some pains to point out that an equivalent Procedure in Oracle would be 'blisteringly quick' .... sigh.  

Most Oracle developers still write lots of (nested) cursors in PL/SQL, it's probably easier for them to manage as they don't have to rack their brains that much ;-)

On Oracle performance is usually acceptable, but it has never been a parallel database and set based solutions run usually much faster on Oracle, too.


  <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