Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 24 Nov 2010 @ 19:02:24 GMT


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


Subj:   Re: Pros and Cons of Stored Procedures
 
From:   John_Wight

Mike Dempsey Wrote:

  Procedural logic is not usually a good thing with Teradata. Teradata is a parallel database - procedural logic is often sequential and should, in most cases, be avoided.  


While the main premise of this is true - TD is a Parallel environment, we need to consider this in context. For Example - if you write SPL Procs using cursors and step through each row processing 'records' like you would do with COBOL, or other languages/processes, then YES - SPL is NOT a good fit for Teradata and we are just as well off on any other 'NON' parallel DBMS.

However, there are a lot of administration processes that I use SPL for, use normal 'parallel TD SQL' internally and they operate and perform very well. In those instances I need 'procedural logic' only to steer me to a specific action based on a previous actions/results, etc. BUT, the SQL is the normal SQL you might find in MACROS, i.e. INSERT/SELECT, UPDATE/SELECT, etc.

I also use SPL with (wait for it - this is JK here and you know what I HATE most. . . ) CURSORS!! Believe it or not - BUT, they are NOT on large data sets, One example is a process we've instituted that periodically checks on the status of Statistics on certain Databases and or tables. The first part creates a GT table (INSERT/SELECT) with those tables that qualify to have stats collected - i.e. > 10% space change or > 28 days stale (just our 'defaults'). With that data set, I then establish a cursor to go through each row and, one by one, dynamically generate a COLLECT STATS . . on the table and execute it for each table and update control data in other tables within the cursor loop. This works just fine for a couple reasons. 1) the volume is low - typically 10-30 tables at most qualify in any period. 2) the collect stats process on a table can consumes time anyway, so fast response, performance, etc. is not an issue here.

The long and short of it is like most things in Life - IT DEPENDS! In the end, you need to know the characteristics of your DBMS, the functionality of all the 'options/functionality' you have available within the DBMS environment and then apply the correct technology or functionality for a given situation. The WORST thing we can do is to set rigid rules - e.g. SPL should not be used because it is procedural, etc.

At least that's the view of 'my world' from where I sit, and that may not be the same stool from which you observe life;-) FWIW;-)

. . . and a very Safe and Happy Thanksgiving to all those that celebrate that holiday.


JK



     
  <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