Archives of the TeradataForum
Message Posted: Wed, 30 May 2012 @ 18:05:17 GMT
| 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
You got much more control in an SP compared to BTEQ, but you probably still need BTEQ to CALL the SP :-)