Archives of the TeradataForum

Message Posted: Sun, 02 Mar 2008 @ 19:51:16 GMT

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

I would build up on Dieter's last comment, I think you mentioned that there are only 7 amps and there's about 100 million records already in the system. I guess that puts the number of records per AMP on an average of 14 million.

In the past I have had severe performance issues (even when the table was partitioned) because of about couple of billion records going in per partition, if I do some statistics over the number of amps in that system it would come up to about 11 million per Amp.... At that point performance was a disaster (the table was partitioned on month and the performance got worse as more loads came in towards the end of the month). We suspected it to be because of the fact that the number of data blocks that the new records have to go in is sort of spread across as the table grew, with pretty much each row going into a different data block, resulting in more physical IOs... We repartitioned it on something more granular after re-visting the original partitioning scheme and the nature of queries and the loads started going in faster... !!

So here's what I would suggest you to do....

load the records into an empty table (check the speed of the load, I bet it would be a lot fast).

Do an insert select from this table to your target table that has these 100 million records, you would probably notice it being slower (if possible turn on dbql logging to capture the step information as well). You would notice in the dbql (also in performance monitor explain steps) that it probably get's stuck at the final "merge into" step in the explain where all the physical writes into the table happens...

If you can find that out, you have your culprit, and then probably can revisit your physical model to add partitions, history tables etc ...

Also try doing a pack disk or so on the table (20 % ?)... though I am not sure how much helpful it would be in this case ...(I would suggest do the other stuff first)

Good Luck !

Joseph D'silva

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