Archives of the TeradataForum

Message Posted: Tue, 02 May 2006 @ 09:46:56 GMT

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

Transient journaling is used to roll back data from a failed or aborted query (or series of queries in a transaction) that modify data.

So yes, if your macro modifies data, a transient journal will be used.

The main difference between a macro and a query submitted from a query tool such as bteq is when it is parsed.

When you *create* a macro, Teradata will parse it and generate what is known as the plastic steps and store them. When you run the macro, Teradata simply recalls the plastic steps and runs them. This process could be likened to compiling a program (eg a 'C' or Cobol program) and the creation of an executable version of it.

When you submit a query from a query tool, Teradata must parse the SQL and generate the plastic steps upon receipt of the query. Unlike a macro, these steps aren't stored but run immediately (although they may be cached).

So where will you see a performance difference? The parsing process requires the system to do some work to analyse the query text, obtain information from the various "metadata repositories" to find out about permissions, table structures, data sampling, defined indexes, column data types and so on. This is all needed so Teradata (or any database for that matter) can actually run the query.

If you are running lots of tactical queries (which are by definition the same except for key values and fast running) then the query parsing could be an undesired overhead. You will incur this if you submit a query that needs to be parsed - such as an SQL request. If you put your query in a macro, this parsing etc has been completed once when the macro was created - so Teradata can skip this overhead and get straight down to running the query.

Now having said that, the overhead (from some informal and unofficial tests I have done) isn't that huge and the aforementioned cache helps if the same tactical query is processed over and over (even with different data value). But there is still some overhead which can be measured as a small number of milli-seconds.

On the other hand, if you are running a decision support query. That is a query which may run for several minutes or hours, saving a few milliseconds by placing the query in a macro will be unnoticeable.

I hope this helps

Glenn Mc

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