Archives of the TeradataForum

Message Posted: Thu, 24 Aug 2006 @ 15:32:08 GMT

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

David:

Are the timings you specified a single run or are they based on averages of multiple runs? If it is a single run, other things executing at the time could easily skew the timings one way or the other.

Of course the time is going to be based largely on the number of read and write operations to the table and double that due to the Transient Journal (TJ). You do not mention how you accomplish the DELETE and INSERT vs the UPDATE. I too would have expected the UPDATE to be faster because of reading and re-writing of the same block vs once for the DELETE and again for the INSERT. Again, we have no data on the method you are using to do the two things, but I am thinking maybe a multi-statement request of some sort. The exception to the one block scenerio would be if you were changing one or more of the PI columns. This would require a delete from one block on one AMP and an insert into a different block on either the same or different AMP. Plus, the TJ entry for both the DELETE and INSERT - same with an UPDATE.

This process might actually be faster with MLOAD (fewer reads and writes of blocks and no TJ). However, you would be required to drop your JI(s) and rebuild them which would add to the over all time as well as any USI definitions.

Maybe if you provide a bit more detail, we can do better speculating.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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