I recently encounter a situation to tune a query which performance is very poor. Two tables (Huge volume) of tables are joined together and updating a field.
Update A Table_Name_1 A, Table_Name_2 B Set A.Field3=B.Field3 Where A.Field1=B.Field2 and A.Field2=B.Field2
Here, A.Field1 and A.Field2 are not primary index and so performance is very poor. I cannot able to change the PI of Table_Name_1. So i have used USI, but using USI on a huge volume of table will eat free permanent space.
This query has to be run daily in BATCh mode, shall go ahead with Drop and Create USI on daily basis? i mean, i can drop the USI once this query is done and create USI before table is getting inserted? Will it create a performance issue if i drop and create USI often. Here my ultimate aim is to avoid using huge permanent space by dropping USI daily.
Hi, inclusion of create and drop USI as a part of ETL (batch) is a relevant option. If your batch runs isolated in the night without pressure to catch the "window", then create and drop, otherwise consider the impact on the performance of concurrent processes and potential increasing of the total ETL duration (based on batch’s dependencies). But still it can be the best choice.