need help with this process

Database
Enthusiast

need help with this process

Hi,
this is the query i am running which has some stat functions.
/****************************************************************/
DROP TABLE devdata.f_im_incident_audit_log;
CREATE TABLE devdata.f_im_incident_audit_log AS
(SELECT a.incident_id_n Incident_ID
, a.audit_log_id_n Audit_Log_ID
, a.entry_t Entry_TS
, a.assigned_to_X Assigned_to
, a.incident_category_c Product_ID
, a.incident_type_id_c Sub_Product_ID
,a.source_id_c Source_ID
, a.status_id_c Status_ID
, a.user_7_x Esc_N
,MAX(a.user_7_x) OVER(PARTITION BY a.Incident_id_n ORDER BY a.entry_t ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PRE_ESC_N
,CASE WHEN a.user_7_x = 2 THEN
MIN(a.entry_t) OVER(PARTITION BY a.Incident_id_n, a.user_7_x ORDER BY a.entry_t ROWS UNBOUNDED PRECEDING)
ELSE NULL
END ESC2_START_T
,CASE WHEN a.user_7_x = 2 THEN
MAX(a.entry_t) OVER(PARTITION BY a.Incident_id_n, a.user_7_x ORDER BY a.entry_t DESC ROWS UNBOUNDED PRECEDING)
ELSE NULL
END ESC2_MAX_T
,MAX(a.status_id_c) OVER(PARTITION BY a.Incident_id_n ORDER BY a.entry_t ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PRE_Status_ID
FROM
(SELECT incident_id_n
FROM crmprodwork.im_incident_audit_log
WHERE CAST(entry_t AS DATE) >= 1070101
GROUP BY 1) z(incident_id_n)
LEFT OUTER JOIN
crmprodwork.im_incident_audit_log a
ON a.incident_id_n = z.incident_id_n
) WITH DATA;
/***********************************************************************/

Here is what it shows in the Teradata manager while the query is running.

/************************************************************************/
Step Est. Time Actual Time Est. Rows Actual Rows Step Text
1 0:00.00 0:00.00 0 1 First, lock crmprodwork."pseudo table" for read on a row hash.
2 0:00.00 0:00.00 0 36 Next, we lock crmprodwork.im_incident_audit_log for read and we lock devdata.[TBId=0x000F 0x5E99] for exclusive.
3 0:00.00 0:00.02 0 36 We create the table header.
4 1:03.45 0:02.75 1968275 36 We do a SUM step to aggregate from crmprodwork.im_incident_audit_log by way of an all-rows scan. Aggregate Intermediate Results are computed locally, then placed in Spool 52031.
5 0:00.59 0:00.12 1968275 1260475 We do an All-AMPs RETRIEVE step from Spool 52031 (Last Use) by way of an all-rows scan into Spool 52029, which is built locally on the AMPs.
6 0:01.78 0:00.34 1968275 1260475 We do an All-AMPs RETRIEVE step from Spool 52029 (Last Use) by way of an all-rows scan into Spool 52035, which is redistributed by hash code to all AMPs.
7 1:12.05 0:02.16 10324150 6698777 We do an All-AMPs JOIN step from Spool 52035 (Last Use) by way of an all-rows scan, which is joined to table im_incident_audit_log. Spool 52035 and table im_incident_audit_log are left outer joined using a merge join . The result goes into Spool 52034, which is built locally on the AMPs.
8 0:00.00 0:23.27 0 6698777 We do an all-AMPs STAT FUNCTION step from Spool 52034 (Last Use) by way of an all-rows scan into Spool 52038, which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 52036, which is redistributed by hash code to all AMPs.
9 0:00.00 2:04.38 0 6698777 We do an all-AMPs STAT FUNCTION step from Spool 52036 (Last Use) by way of an all-rows scan into Spool 52041, which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 52040, which is redistributed by hash code to all AMPs.
10 0:00.00 1:24.29 0 6698777 We do an all-AMPs STAT FUNCTION step from Spool 52040 (Last Use) by way of an all-rows scan into Spool 52044, which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 52043, which is redistributed by hash code to all AMPs.
11 2:22.73 0:38.98 10324150 6698777 We do an All-AMPs RETRIEVE step from Spool 52043 (Last Use) by way of an all-rows scan into Spool 52033, which is redistributed by hash code to all AMPs.
12 0:00.00 0 We do a MERGE into table [TBId=0x000F 0x5E99] from Spool 52033.
13 0:00.00 0 We lock DBC."pseudo table" for write on a row hash, we lock DBC."pseudo table" for read on a row hash, we lock DBC."pseudo table" for write on a row hash and we lock DBC."pseudo table" for write on a row hash.
14 0:00.00 0 We lock DBC.DBase for read on a row hash, we lock DBC.TVM for write on a row hash, we lock DBC.TVFields for write on a row hash, we lock DBC.AccessRights for write on a row hash and we lock DBC.Indexes for write on a row hash.
15 0:00.00 0 We do a Single-AMP ABORT test from DBC.DBase by way of the unique primary index. This step begins a parallel block of steps.
15 0:00.00 0 We do a Single-AMP ABORT test from DBC.TVM by way of the unique primary index. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVFields. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.Indexes. This step is performed in parallel.
15 0:00.00 0 We do an INSERT step into table DBC.TVM. This step is performed in parallel.
15 0:00.00 0 We INSERT default rights to DBC.AccessRights for devdata.[TBId=0x000F 0x5E99]. This step ends a parallel block of steps.
15 0:00.00 0 We send out an END TRANSACTION step to all AMPs involved in processing the request.
/**************************************************************************/

The total process took 23 minutes to run (which was surprising)
The first 11 steps took less than 4 minutes and
The step 12 "12 0:00.00 0 We do a MERGE into table [TBId=0x000F 0x5E99] from Spool 52033."
took nearly 18 minutes.
what is happening in this step? and any reason why it should take this long?
is there a way to solve this?
Thanks

2 REPLIES
Enthusiast

Re: need help with this process

You are doing left join from z to a, but the NUPI set up by this query is on the Incident id from a.
So you are potentially getting lots of nulls in the PI field.
Check the answer set by
Select incident_id, count(*)
from table
group by 1
order by 1 desc
;

If you have lots of nulls, or lots of any value, check the skew.
Suggest you probably want an inner join, or use z.incident_id to populate the PI.
Enthusiast

Re: need help with this process

Thanks Jim
I will try it.