How do we improve this Update query Performance as currently it is taking 1 Minute to update 17643 Rows.
Declare @date datetime = '09-Dec-2013' update #tmp_sp_abc set test = rtrim(xyz_test) from #tmp_sp_abc t1, t_h_bg_pc_it t2 where (t2.id_i = t1.i or t2.id_s1 = t1.s) and t1.r_type = 1 and t2.[date] = @date
Tables Row Count: - #tmp_sp_abc -> 125352 t_h_bg_pc_it -> 14798 Rows
t_h_bg_pc_it table has 300 columns with primary key on id_i column
#tmp_sp_abc has 11 columns with no primary key and no indexes.
found that "OR" condition is the root cause of this much time consumption but, can't change it.
tried to add indexes on: -
Table: - t_h_bg_pc_it Columns: - [xyz_test], [id_i], [id_s1], [date] Table: - #tmp_sp_abc Columns: - [i], [s], [r_type] include [test]
but, by doing this saved only 5 seconds.
Attaching the Execution Plan Snaps (Without above indexes and with indexes).