update statement right or wrong

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 2005 Forums

Transact-SQL (2005)

update statement right or wrong

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-05-03 : 04:10:59

Hi All,

I have 2 tables in database. I want to populate table1 data (lastusage column) into table 2.

table 1 data has 20 million records
table 2 has 4 million records.

create table #temp1 (
assettag varchar(100),
app varchar(100),
userid varchar(100),
lastusage int)

create table #temp2 (
assettag varchar(100),
app varchar(100),
userid varchar(100),
lastusage int)

is this update right?

update #temp2
set lastusage = t1.lastusage
from #temp2 t2 join #temp1 t1
on (ltrim(rtrim(t1.assettag)) = ltrim(rtrim(t2.assettag))
and ltrim(rtrim(t1.app)) = ltrim(rtrim(t2.app)))
and ltrim(rtrim(t1.userid)) <> ltrim(rtrim(t2.userid))

The update is just running since 40 hrs. :(

Regards,
Sachin

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-03 : 05:01:42

Do you have proper indexes??
But keep in mind that if you have proper index on columns like assettag or app, then optimizer still cann't use your indexes because of LTRIM, RTRIM functions in JOIN or WHERE clause.

--------------------------
http://connectsql.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 05:04:34

do you have to apply ltrim and rtrim on the column ?

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-05-03 : 06:21:07

thanks all.

ltrim and rtrim was taking lot of time, have removed it.

regards,
sachin

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-03 : 06:35:45

No, it wasn't ltrim and rtrim taking the time, it was the fact that there was no possibility that the query could use any indexes that exist on the table. ltrim and rtrim are very fast functions.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-05-03 : 08:51:36

but as soon as I removed ltrim and rtrim, the update query got executed in 20 mins.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-03 : 12:03:32

Yes, and that will be because you have indexes in place. ltrim and rtrim and many other functions are not good practice on joins as they will 9 times out of 10 ensure that you cannot use that wonderful index that means your update takes 20 mins and not hours..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources