Update statement execution time different scenarios

Database
Junior Supporter

Update statement execution time different scenarios

I have a case where I have to update a very huge table. The update is simple, column B gets set to a value based on column A using a scalar UDF.

The execution time of this update is different in the below three scenarios depending on table definition and update execution method:

1) SET Table and Update executed as a single statement in open query. --> this takes really long

2) SET Table and Update placed inside a stored procedure and the stored procedure is called --> this runs really fast

3) MULTISET Table with uniqueness constraint on single column primary index and Update executed as a single statement in open query --> this runs really fast

Can someone please explain why this is happening?
5 REPLIES 5
Teradata Employee

Re: Update statement execution time different scenarios

Hi.

 

>>"Can someone please explain why this is happening?"

 

Well... surely EXPLAIN can. But you didn't post it.

 

Cheers.

 

Carlos.

Junior Supporter

Re: Update statement execution time different scenarios

Below is the explain and execution time. CUSTOM_TRUNC and HEX_TO_DEC_4 are scalor UDFs.

 

Also is there a difference in when the uniqueness constraint of SET TABLE is checked for an update query running as open query versus inside a procedure call.

 

1) SET Table and Update executed as a single statement in open query

SyntaxEditor Code Snippet

CREATE SET TABLE UPDATE_FORUM ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      contrct_id INTEGER,
      cell_site_id VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
      day_part_ind BYTEINT,
      dur DECIMAL(18,0),
      decoded_cellsite VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( contrct_id ,cell_site_id ,day_part_ind );

update UPDATE_FORUM
set decoded_cellsite = cast(CUSTOM_TRUNC(HEX_TO_DEC_4(SUBSTR(CELL_SITE_ID, CHARACTERS(CELL_SITE_ID) - 3, 4))) as varchar(10));

Explain update UPDATE_FORUM
set decoded_cellsite = cast(CUSTOM_TRUNC(HEX_TO_DEC_4(SUBSTR(CELL_SITE_ID, CHARACTERS(CELL_SITE_ID) - 3, 4))) as varchar(10));

  1) First, we lock UPDATE_FORUM for write on a reserved RowHash
     to prevent global deadlock.
  2) Next, we lock UPDATE_FORUM for write.
  3) We do an all-AMPs UPDATE from UPDATE_FORUM by way of an
     all-rows scan with no residual conditions.  The size is estimated
     with low confidence to be 68,550,000 rows.  The estimated time for
     this step is 37.31 seconds.
  4) 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 37.31 seconds.

Result Message : UPDATE completed. 68665166 rows processed. Elapsed Time = 00:21:34

 

2) SET Table and Update placed inside a stored procedure and the stored procedure is called

SyntaxEditor Code Snippet

replace procedure UPDATE_FORUM_PROC ()sql security creator
begin

update UPDATE_FORUM
set decoded_cellsite = cast(CUSTOM_TRUNC(HEX_TO_DEC_4(SUBSTR(CELL_SITE_ID, CHARACTERS(CELL_SITE_ID) - 3, 4))) as varchar(10));

end;

call UPDATE_FORUM_PROC();

Explain call UPDATE_FORUM_PROC();

  1) First, we send the CALL Step to RTS. .
  -> No rows are returned to the user as the result of statement 1.

Result Message : CALL completed. 0 rows processed. Elapsed Time = 00:16:01

 

3) MULTISET Table with uniqueness constraint on single column primary index and Update executed as a single statement in open query

 

SyntaxEditor Code Snippet

CREATE MULTISET TABLE UPDATE_FORUM ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      contrct_id INTEGER,
      cell_site_id VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
      day_part_ind BYTEINT,
      dur DECIMAL(18,0),
      decoded_cellsite VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)UNIQUE PRIMARY INDEX ( contrct_id ,cell_site_id ,day_part_ind );

update UPDATE_FORUM
set decoded_cellsite = cast(CUSTOM_TRUNC(HEX_TO_DEC_4(SUBSTR(CELL_SITE_ID, CHARACTERS(CELL_SITE_ID) - 3, 4))) as varchar(10));

Explain update UPDATE_FORUM
set decoded_cellsite = cast(CUSTOM_TRUNC(HEX_TO_DEC_4(SUBSTR(CELL_SITE_ID, CHARACTERS(CELL_SITE_ID) - 3, 4))) as varchar(10));

  1) First, we lock UPDATE_FORUM for write on a reserved RowHash
     to prevent global deadlock.
  2) Next, we lock UPDATE_FORUM for write.
  3) We do an all-AMPs UPDATE from UPDATE_FORUM by way of an
     all-rows scan with no residual conditions.  The size is estimated
     with low confidence to be 68,715,360 rows.  The estimated time for
     this step is 37.48 seconds.
  4) 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 37.48 seconds.

Result Message : UPDATE completed. 68665166 rows processed. Elapsed Time = 00:01:11

 

Junior Contributor

Re: Update statement execution time different scenarios

The 2nd update is 25% faster, this might be due to lower system utilisation, but 20 times faster is strange.

The PI is on the same three columns (not a single column in #3 as you wrote) and if the NUPI is actually unique there should be no difference.

 

Did you recreate/repopulate the table for each run?

Do you have access to QueryLog(Steps) to check the actual resource usage?

Junior Supporter

Re: Update statement execution time different scenarios

This is what I did.

  1. Created the SET table with NUPI and populated from the source.
  2. Executed version 1 of the query.
  3. Deleted all the rows from the SET table and repopulated from the source.
  4. Executed version 2 of the query.
  5. Dropped the SET table and created MULTISET table (with same name) with UPI and populated from the source.
  6. Executed version 3 of the query.

I don't know if I have access to QueryLog(Steps). But I can try if you point me in the right direction with the necessary queries.

Highlighted
Junior Contributor

Re: Update statement execution time different scenarios

The QueryLog are some system tables/views.

Try to run

 

SELECT * FROM dbc.QryLogV
WHERE StatementType = 'Update'
  AND  UserName = USER
ORDER BY StartTime

and check if you can find your queries, most important columns are: ElapsedTime, AMPCPUTime, SpoolUsage, ReqIOKB, ReqPhysIOKB

 

 

Details can be found (maybe) in dbc.QryLogSTepsV with the matching QueryIDs from the 1st Select (but in our case there's should be only a single working step (step 2) anyway, step 1, 2 & 4 are preparation/cleanup)