Archives of the TeradataForum

Message Posted: Tue, 14 Jul 2015 @ 21:18:12 GMT

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

There are three important bits of information missing to do a thoughtful analysis of the performance problem:

1. DDL for the two tables

2. Help Stats (or comparable)

3. Step data -- at least an indication which step is consuming all the resources. You flag step 7: the sliding-window merge join but based on the info given it is more likely to be step 8: the merge. Get the step by turning on step loging or using Viewpoint, drill in on the running session.

The Low Confidence is likely because there are no statistics on the five column set for the DISTINCT operator ( COL1,COL2,START_DATE, END_DATE,PLCFLAG). Probably not worth adding unless there is a major discrpency (more than an order of magnitude) between the estimate and the actual. A quick look at step data comparing the estimate row count with the actual will resolve.

The statement "grouping by COL1,COL2,START_DATE,END_DATE gives duplicate entries" is an indicaiton that there is a data content problem with Table 2. This is not likely to be the source of the performance problem but is worth investigating. Because the UPDATE will fail if you ever let it run long enough and because you are likely to spread the trash around. A better way to addess the dup problem may be a aggregation: SELECT COL1,COL2,START_DATE, END_DATE, MAX(PLCFLAG) AS PLCFLAG) FROM TEST.TABLE2 GROUP BY COL1,COL2,START_DATE, END_DATE

If it is stuck on step 7: I am guessing a PI of (Col 1,Col2). A very low cardinality on this combination might trigger a lengthy join. Average rows per value exceding 50, or the number of rows sharing the most commonly used value exceding 1000 would be starting points for perfromance issues. I assume that the PK on Table1 is COL1,COL2,START_DATE,END_DATE -- verify that it is so.

If it is stuck on Step 8: Look for: Table1 is SET and very low cardinality on the Primary Index (duplicate row checking). Look for secondary indexes on Table1 especially if any contain PLACE_FLAG_CD_old.

Arnie

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