Improve Performance -Update query with OR in WHERE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Improve Performance -Update query with OR in WHERE

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2013-12-18 : 07:22:31

Hi! How do we improve this Update query Performance as currently it is taking 1 Minute to update 17643 Rows.

Query ->
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
and
#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).

Please advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 07:41:14

did you try separating it into two updates which each of conditions eparated by OR and chck the performance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bsethi24
Starting Member

25 Posts

Posted - 2013-12-18 : 07:46:09

Hi!

First of all can't separate the OR. and tried this but, getting diff. in output of 5417 rows (may be due to some rows have values in both columns).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 08:01:15

that doesnt make any sense. In any case I would rekon same number of records in your table to get updated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bsethi24
Starting Member

25 Posts

Posted - 2013-12-18 : 08:16:39

Hi ! I agree. But, Business guys are not allowing to this in separate queries. That's why in reply mentioned "can't do this."

Kristen
Test

22859 Posts

Posted - 2013-12-18 : 13:21:11

I presume that [xyz_test] is in table t2?

I have found, in the past, that UNION can optimise faster than OR, so this may be faster for you:


Declare @date datetime = '09-Dec-2013' 
update	#tmp_sp_abc 
set	test = rtrim(xyz_test) 
from	#tmp_sp_abc AS t1
	JOIN
	(
		SELECT	t1.i, t2.xyz_test
		FROM	#tmp_sp_abc AS t1
			JOIN t_h_bg_pc_it AS t2 
				 ON t2.id_i = t1.i
		WHERE	    t1.r_type = 1
			AND t2.[date] = @date 
		UNION
		SELECT	t1.i, t2.xyz_test
		FROM	#tmp_sp_abc AS t1
			JOIN t_h_bg_pc_it AS t2 
				 ON t2.id_s1 = t1.s
		WHERE	    t1.r_type = 1
			AND t2.[date] = @date 
	) AS X
		 ON X.i = t1.i

UNION ALL would be better (than UNION), but it is possible that you will get duplicate rows from both the UNION ALL queries. This won't matter, just that such rows in #tmp_sp_abc will update twice with the same data (but if that happens the ROWCOUNT will be different); if it is critical to you that the ROWCOUNT is correct, and rows only update once, then you will need UNION, instead of UNION ALL, but that will be slower (because UNION requires SORT and De-Dupe steps)

I suppose you could "code out" the DUPs and thus make use of UNION ALL by adding a NOT EXISTS in the second SELECT of the UNION ALL to prevent anything that was selected in the first SELECT being also re-selected in the second SELECT. That complexity may negate the time saved though.

Another route to achieving that would be to create another #TempTable and load it with the first SELECT in the UNION, and then insert more rows from the second SELECT - but only where not already existing in #TempTable (from the first SELECT). Then JOIN that #TempTable in the main UPDATE query.

If you think any of those would be useful, but don't know how to code them - or my description was not clear enough!!! - then do please ask.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-18 : 17:33:26

Try a clustered index on:
#tmp_sp_abc ( r_type, i )

And to insure that the index name is unique:



DECLARE @create_temp_table_index nvarchar(2000)
SET @create_temp_table_index = 'create clustered index tmp_sp_abc_' + REPLACE(NEWID(), '-', '') + ' on #tmp_sp_abc( r_type, i )'
EXEC(@create_temp_table_index)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources