Update SQL query is taking long time to execute

Database

Update SQL query is taking long time to execute

We are using the below query to modify the transaction ID on the table and this transaction ID is primary index on the table through ETL job.

USING (NEW_TRANSACTION_ID VARCHAR(300), RELATED_TRANSACTION_ID VARCHAR(300), RECORD_TYPE VARCHAR(765), PATH_MARKET_FLAG BYTEINT, TRANSACTION_ID VARCHAR(300), SERVICES_RENDERED_DATE DATE) UPDATE X_EV.REPORTING_TRANSACTION  SET TRANSACTION_ID = '1024412', RELATED_TRANSACTION_ID = NULL, RECORD_TYPE = 'SO', PATH_MARKET_FLAG = 0 WHERE TRANSACTION_ID = '1000736-SOACT2' AND SERVICES_RENDERED_DATE = '2013-05-21'.

Explain Plan of the query as follows:

  1) First, we lock a distinct X."pseudo table" for write on a

     RowHash to prevent global deadlock for

     X.REPORTING_TRANSACTION.

  2) Next, we lock X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION for write.

  3) We do a single-AMP RETRIEVE step from a single partition of

     X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION by way of the primary index

     "X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION.TRANSACTION_ID =

     '1000736-SOACT2'

, X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE = DATE

     '2013-05-21'" with a residual condition of (

     "(X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION.TRANSACTION_ID = '1000736-SOACT2')

     AND (X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE = DATE

     '2013-05-21')") locking row for access into Spool 1 (group_amps),

     which is redistributed by the rowkey of (

     X.REPORTING_TRANSACTION.ROWID) to all AMPs.  Then we do a

     SORT to order Spool 1 by the sort key in spool field1.  The size

     of Spool 1 is estimated with high confidence to be 1 row (18

     bytes).  The estimated time for this step is 0.00 seconds.

  4) We do a group-AMP MERGE DELETE to X.REPORTING_TRANSACTION in

     view X_EV.REPORTING_TRANSACTION from Spool 1 (Last Use) via

     the row id.  New updated rows are built and the result goes into

     Spool 2 (all_amps), which is redistributed by the rowkey of (

     '1024412', X.REPORTING_TRANSACTION.Field_1035) to all AMPs.

     Then we do a SORT to partition Spool 2 by rowkey.  The size is

     estimated with high confidence to be 1 row.  The estimated time

     for this step is 24 minutes and 44 seconds.

  5) We do an all-AMPs MERGE into X.REPORTING_TRANSACTION in view

     X_EV.REPORTING_TRANSACTION from Spool 2 (Last Use).  The size

     is estimated with high confidence to be 1 row.  The estimated time

     for this step is 42.44 seconds.

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 25 minutes and 27 seconds.

For 100 records the aboc=ve query is running for almost 8 hours. Please help us to tune the above query so that it can run fast.

Tags (2)
3 REPLIES
N/A

Re: Update SQL query is taking long time to execute

Did you check DBQL for elapsed time and CPU/IO usage?

If it's actually 100 rows this should run much faster (even if it's changing the PI and partitioning).

Are there any Secondary Indexes?

Re: Update SQL query is taking long time to execute

Thanks denoeth for prompt response. I am new in Teradata and do not know how to check the DBQL. Please provide me some idea how I can generate the DBQL report.

We have modified the query and now it is taking only 10 minutes.

UPDATE  X_EV.REPORTING_TRANSACTION 

SET TRANSACTION_ID = RELATED_TRANSACTION_ID

WHERE RECORD_TYPE IN ('SI','SO') AND PATH_MARKET_FLAG = 0 AND RELATED_TRANSACTION_ID IS NOT NULL

Teradata Employee

Re: Update SQL query is taking long time to execute

You need to enable DBQL first .... check with yout DBA if it is enabled, and then he should be able to give you that report.

Whats the situation with statistics collection? And how busy is the node in general?