Archives of the TeradataForum

Message Posted: Wed, 11 Oct 2001 @ 16:37:13 GMT

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

Pter,

The Insert-Select is probably the fastest approach. By "efficient" I assume you mean the fastest performance.

The following items are some things to consider:

* If both tables have the same primary index the Insert will yield better performance.

* If the target table has a NUPI, less rows per PI value should yield better performance.

* If the target table has a NUPI with many row per PI value, changing the table from SET to MULTISET should result in better performance. (Keep in mind that MUTLISET allows complete duplicate rows while SET does not.)

* Check for skewed data, particularly in the source table. If the PI selected causes skewing, performance will suffer.

* Change the priority of the process to something higher than currently set. i.e. change from $M to $H. (Of course, other processes may suffer.)

* Investigate the COMPRESS and DEFAULT options for columns. Smalller rows, less IOs, better performance. (Possible trade-off with query performance.)

* Add more hardware (I threw that one in for the NCR folks! More hardware can almost always can fix a Performance problem!)

For reference, my current client loads about 600 M rows into a 12 B row table and it takes about 6 hours with other work concurrent on the system. The process runs at $H (the highest priority task at that time.) The Primary Index on both tables is the same and the is a NUPI with many rows per PI value. It's a very large (576 AMP) system.

Without knowing your configuration, concurrent workload during this process, software version, etc. it's impossible to say whether 2 hours is erasonable or not. For example, if I were to run this process on my Windows 2000 lapttop. getting 1 M inserts in 1 hour would be unbelievable.

Does anyone have any rules of thumb with respect to insert rates? For example, a user can expect to be able to do 20 UPI inserts/sec/node or something like that. It would be a good reference point if nothing else.

Hope this helps.

Regards,

Thomas F. Stanek
TFS Consulting

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