Archives of the TeradataForum

Message Posted: Mon, 20 Jul 2009 @ 16:05:57 GMT


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

Subj:		Re: Macro use with Work Tables

From:		Dieter Noeth

David Clough wrote:

The change from Procedure to Macro with trivial, just topping and tailing the wrapping code, as the rest of it was just a series of Insert/Select statements.

...

Thing is, the Insert/Select uses a set of 'work' tables (actually they exist in Perm space) and clear them down at the end of the Macro. This seems to have had the affect that, within the Explain Plan, there are no available Stats for the Macro to work with.

This ended up with the Macro never finishing and my insistence on using a Macro appearing rather foolish.

So, one question I have is, do Macros establish their Explain Plan at time of Compile or time of execution ?

If you explain that "exec macro", you'll see the plan for all steps, because macros are always multi-statement requests, whereas statements within an SP are seperate statements (unless there's BEGIN/END REQUEST).

Additionally, when writing from one 'work' to another - which are initially empty - will the Macro be able to do a Random Amp sample or will it only pick up 'no statistics' at the start of run ?

RAS is done during optimization, thus the optimizer assumes the tables are empty :-(

Are there times when Macros (like in this instance) are not a good idea ?

I especially like (in Teradata mode):

     DELETE FROM tab;

-- fast-path/no journal because it's the last statement within that transaction

     INSERT INTO tab SELECT...;

-- fast-path/no journal because at the beginning of the transaction the table is empty

Now let's make it even faster and put it into a macro/multi-statement;

     DELETE FROM tab
     ;INSERT INTO tab SELECT...;

-- both are journaled now :-)

We can re-do this in Bteq, but I'm now starting to question how good Macros are.

There's more overhead for CALLing an SP than for EXECuting a macro. So if it's a sub-second query (and repeated a lot) you'll notice that difference, but else...

The main reason for still using macros are SELECTs. Before TD12 it couldn't be done in an SP, and in TD12 it's still more complicated to write.

Could you post the macro EXPLAIN?

Dieter


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

Attachments

Library

Quick Reference