Archives of the TeradataForum

Message Posted: Wed, 11 Sep 2002 @ 12:42:42 GMT


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

Subj:		Transactions, requests, statements, macros

From:		Geoffrey Rommel

I'm starting a new thread because we've kinda veered off the original subject.

Verdammt! Dieter is right. Don't you hate it when that happens? :-)

Try the following:

   ins tab1 sel * from tabx sample 1
   ;ins tab1 sel * from tabx;

This will be a fast-path insert

   create macro test as
   (ins tab1 sel * from tabx sample 1;
   ins tab1 sel * from taby;
   );

   exec test;

Look at execution time and transient journal sizes.

As output #1 below shows, Dieter is right: the macro does a fast-path insert/select. I think, however, that this must be a special optimization for this one case. In general, it would not be possible to execute every statement in a macro "in parallel". In output #2, I show an example: a select followed by an insert. Here the select must happen either before or after the insert. Fortunately, it happens just where you would expct it.

===== OUTPUT #1 =====

create table tab1
as
lci_dw_tbls.npmatvi_hvac_sl_prd
with no data;

 *** Table has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

--insert a single row into an empty table
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838;

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-
--insert lots of rows (will be journalled)
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Insert completed. 900644 rows added.
 *** Total elapsed time was 3 seconds.

+---------+---------+---------+---------+---------+---------+---------+-

delete from tab1 all;

 *** Delete completed. 900645 rows removed.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-
--This will be a fast-path insert
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

 *** Insert completed. 900645 rows added.

+---------+---------+---------+---------+---------+---------+---------+-

-- NOW the macro:

create macro dieter_test as
(ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;
);

 *** Macro has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

delete from tab1 all;

 *** Delete completed. 900645 rows removed.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

exec dieter_test;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

 *** Insert completed. 900645 rows added.

+---------+---------+---------+---------+---------+---------+---------+-

-- EXPLAIN:
explain
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Help information returned. 26 rows.
 *** Total elapsed time was 1 second.

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct lci_dw_tbls."pseudo table" for read on a RowHash to prevent global deadlock for lci_dw_tbls.npmatvi_hvac_sl_prd.`
	`2)`	`Next, we lock a distinct LCIDBA."pseudo table" for write on a RowHash to prevent global deadlock for LCIDBA.tab1.`
	`3)`	`We lock lci_dw_tbls.npmatvi_hvac_sl_prd for read, and we lock LCIDBA.tab1 for write.`
	`4)`	`We do a single-AMP RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of the unique primary index "lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no = 644838" with no residual conditions into Spool 1, which is built locally on that AMP. The size of Spool 1 is estimated with high confidence to be 1 row. The estimated time for this step is 0.15 seconds.`
	`5)`	`We do an all-AMPs RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of an all-rows scan with a condition of ("lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no <> 644838") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 900,644 rows. The estimated time for this step is 1.42 seconds.`
	`6)`	`We do a SORT to order Spool 1 by row hash.`
	`7)`	`We do a MERGE into LCIDBA.tab1 from Spool 1 (Last Use).`
	`8)`	`We spoil the parser's dictionary cache for the table.`
	`9)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`No rows are returned to the user as the result of statement 1. No rows are returned to the user as the result of statement 2.`

+---------+---------+---------+---------+---------+---------+---------+-

explain
exec dieter_test;

 *** Help information returned. 26 rows.
 *** Total elapsed time was 1 second.

[Yeah, it's the same. Excerpt:]


	`5)`	`We do an all-AMPs RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of an all-rows scan with a condition of ("lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no <> 644838") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 900,644 rows. The estimated time for this step is 1.42 seconds.`
	`6)`	`We do a SORT to order Spool 1 by row hash.`
	`7)`	`We do a MERGE into LCIDBA.tab1 from Spool 1 (Last Use).`

===== OUTPUT #2 =====

create table tab2
( numerus    integer,
  nomen      char(10) )
unique primary index(numerus);

 *** Table has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

ins tab2 (1, 'one');
ins tab2 (2, 'two');
ins tab2 (3, 'three');
ins tab2 (4, 'four');
ins tab2 (5, 'five');

+---------+---------+---------+---------+---------+---------+---------+-

create macro dieter_test2 as
(select * from tab2
;ins tab2 (6, 'six')
;);

 *** Macro has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-----

First explain, then run.

explain
exec dieter_test2;

 *** Help information returned. 14 rows.
 *** Total elapsed time was 1 second.

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct LCIDBA."pseudo table" for read on a RowHash to prevent global deadlock for LCIDBA.tab2.`
	`2)`	`Next, we lock LCIDBA.tab2 for read.`
	`3)`	`We do an all-AMPs RETRIEVE step from LCIDBA.tab2 by way of an all-rows scan with no residual conditions into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 320 rows. The estimated time for this step is 0.15 seconds.`
	`4)`	`We do an INSERT into LCIDBA.tab2.`
	`5)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1. No rows are returned to the user as the result of statement 2.`

+---------+---------+---------+---------+---------+---------+---------+----

exec dieter_test2;

 *** Query completed. 5 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

    numerus  nomen
-----------  ----------
          2  two
          4  four
          1  one
          3  three
          5  five

 *** Insert completed. One row added.


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

Attachments

Library

Quick Reference