 |
 |
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
wonder. | |
You got much more control in an SP compared to BTEQ, but you probably still need BTEQ to CALL the SP :-)
Dieter
| |
