Fast Update(Please Help) - SQL Server Forums

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

Transact-SQL (2000)

Fast Update(Please Help)

Author

Topic

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 10:05:53

Dear Friends
Below is my query


update Auditdata set tatcalltype='13',CDRcomment=a_sc.Providermaster_id
from auditdata_scrap1 a_sc inner join 
auditdata auditdata on a_sc.id=auditdata.id
where auditdata.tatcalltype is null and a_sc.status='y'

the Above SQL Query takes 30 minutes to update

the above query contain 2 table
1)auditdata contain 15000000 rows
2)Auditdata_scrap1 contain 10480000 rows

index are defined to the columns used for the inner join and where clause

plz help me to make the SQL Query Faster

Thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 11:05:19

[code]set rowcount 10000

update x
set tatcalltype = '13',
CDRcomment = a_sc.Providermaster_id
from auditdata AS x
inner join auditdata_scrap1 as a_sc on a_sc.id = x.id
where x.tatcalltype is null
and a_sc.status = 'y'

while @@rowcount > 0
update x
set tatcalltype = '13',
CDRcomment = a_sc.Providermaster_id
from auditdata AS x
inner join auditdata_scrap1 as a_sc on a_sc.id = x.id
where x.tatcalltype is null
and a_sc.status = 'y'

set rowcount 0[/code]

N 56°04'39.26"
E 12°55'05.63"

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 11:32:34

Dear Friend

Thank for your help.....
but this SQL Query also take lot of time to update



set rowcount 10000

update		x
set		tatcalltype = '13',
		CDRcomment = a_sc.Providermaster_id
from		auditdata AS x
inner join	auditdata_scrap1 as a_sc on a_sc.id = x.id
where		x.tatcalltype is null
		and a_sc.status = 'y'

while @@rowcount > 0
	update		x
	set		tatcalltype = '13',
			CDRcomment = a_sc.Providermaster_id
	from		auditdata AS x
	inner join	auditdata_scrap1 as a_sc on a_sc.id = x.id
	where		x.tatcalltype is null
			and a_sc.status = 'y'

set rowcount 0