Performance issue with UPDATE statements

Database
Fan

Performance issue with UPDATE statements

Hello all,

 

Our users table is quite large with 40M records and ~600 fields.

users are accessing that table through a view which joins the base table with a "permissions" table.

the problem we see is with UPDATE statements which joins the users table with another table.

for example:

UPDATE V_sales
FROM V_sales, V_users
set V_sales.value = V_users.value 
where V_sales.id= V_users.id

This update statement has terrible performance, as it seems the whole users table is spooled.

when changing the query to:

UPDATE V_sales
FROM V_sales, (select id, value from V_users) A
set V_sales.value = A.value 
where V_sales.id= A.id

it runs much better.

 

Is that a known issue in Teradata?

 

Is there something which can be done instead of changing all the update statements?

6 REPLIES
Teradata Employee

Re: Performance issue with UPDATE statements

Hi russol,

 

You should try the MERGE statement if your version handle it. Very efficient.

But it will also require a recoding...

The basic syntax :

 

 merge into v_sales as tgt
 using v_users      as src
    on src.id = tgt.id
  when matched then update
   set value = src.value
;
Teradata Employee

Re: Performance issue with UPDATE statements

Yes, MERGE is probably a smarter animal.  It would be very interesting to try that and see if it works as well as (or even better than) the update-join-to-derived-table.  If it doesn't, you should open an incident with Teradata support.  If it does, you should still open an incident for the basic update-join - I would have expected that to be a lot smarter in this case, certainly after all these years.

Teradata Employee

Re: Performance issue with UPDATE statements

Can the explains for the two Update statements be shared please?

Fan

Re: Performance issue with UPDATE statements

just curious,did you try using the alaises as well? 

Fan

Re: Performance issue with UPDATE statements

We couldn't change the update staement to megre since it will require changing many users scripts...

Abhi - what exactly do you mean using aliases?

Fan

Re: Performance issue with UPDATE statements

UPDATE a
FROM V_sales a, V_users b
set value = V_users.value 
where a.id= b.id

 

 

just try it, no harm in tryig right :)