1

Sometimes it can well be observed that individual instructions within a macro execute way faster than a whole macro in Teradata ? Is this just a delusion or is there any logic behind it ? I am newbie to Teradata and I will appreciate if someone explains the reasons from the basics .

StrugglingCoder
  • 4,781
  • 16
  • 69
  • 103

1 Answers1

2

A Macro is exactly the same as a MultiStatement Request (MSR).

When you EXPLAIN EXEC mymacro you will notice that all the statements within the macro are done followed by a final END TRANSACTION step.

Now if you do something like a DELETE ALL as a standalone transaction it's a fast-path delete, a kind of TRUNCATE, because the optimizer knows it's the last modification of that table and it's committed.

Then you might have an INSERT SELECT into that table, which is also fast-path, because the table was empty at the begin of the transaction.

Now you put both in a Macro: The DELETE is not the last modification and the INSERT SELECT is not into an empty table, so both statements will be Transient Journaled. Of course this is much slower...

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • "Now you put both in a Macro: The DELETE is not the last modification and the INSERT SELECT is not into an empty table, so both statements will be Transient Journaled. Of course this is much slower..." .. this part I could not get . Could you please explain ? How would the statements be Transient Journaled as we know DELETE ALL is not Transient Journaled right ? Please explain . – StrugglingCoder May 21 '15 at 07:05
  • 1
    @user3655102: `DELETE ALL` is not journaled if the optimizer knows it's the **last modification** of that table and it's committed. But now the following `INSERT` might fail and then you must be able to rollback, hence the journal. – dnoeth May 21 '15 at 07:12