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.
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?
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 ;
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.
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?