Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 30 May 2012 @ 18:05:17 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Macros vs Stored Procedures for DML statements only
From:   Dieter Noeth

David Clough wrote:

  Stored Procedures vs Macros.  

  On the assumption that there is no procedural code within the Stored Procedure, and there's NO Cursor processing, i.e. there's just a series of DML statements, would I be right in saying that Stored Procedures work just as fast as Macros ?  

The SP is slower, as a CALL has more overhead than an EXEC. But you only notice that when the individual SQLs run fast, e.g. PI-access.

Otherwise SPs have some advantages, e.g.

- SQLs can be single- or multi-statement requests

- ACTIVITYCOUNT for each request

- each request recorded seperatley in DBQL

  Now, you might ask 'why use a Stored Procedure for just DML statements when a Macro will do ?  

  The answer to this is that, between DML statements, we can call a utility Procedure to Collect Stats on the Work Tables which would be used in subsequent steps of the Procedure.  

Are you shure you actually need stats on the work tables? Unless I do complex queries I usually try no stats.

  This cannot be done within a Macro and has certainly led to problems in the past with incorrect Explains in the past, where no Stats were on intermediate 'work' Tables.  

In TD14 stats collection is iirc no longer considered to be DDL, just DML.

  Yes, I know it can all be done equivalently in Bteq, but here again, would it really perform any better than a Stored Procedure, I wonder.  

You got much more control in an SP compared to BTEQ, but you probably still need BTEQ to CALL the SP :-)


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