slow update - 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 2012 Forums

Transact-SQL (2012)

slow update

Author

Topic

kowalsky
Starting Member

29 Posts

Posted - 2013-06-12 : 16:40:40

hi all,
I have this statement that tries to update a date field based on a lookup table where I have two date fields. In the lookup I have pairs of date, adjusted date. All I am trying to do is update my adjusted date field in my main table based on this lookup table (if I don't find MYDATE in the lookup table, the MAINTBL.ADJ_DATE should be equal to MYDATE). The query I am using is this:


update MAINTBL set ADJ_DATE = t1.DATE_ADJUSTED
from	(
        select MAINTBL.DUE_DATE, 
	      case
		when DATELKUP.DATE_ADJUSTED  IS NULL
		then MAINTBL.MYDATE
		else DATELKUP.DATE_ADJUSTED
		end as 	DATE_ADJUSTED
	from MAINTBL
	left outer join DATELKUP
	on MAINTBL.MYDATE = DATELKUP.MYDATE
	) t1
where MAINTBL.MYDATE = t1.MYDATE

I've created an index (nonunique) on MYDATE.
If I add to the where clause a single date, the update runs within 2 seconds. If I leave the statement as it is it ran for 25 minutes and still didn't finish.
What else can I try?

Thanks,
kowalsky

kowalsky

kowalsky
Starting Member

29 Posts

Posted - 2013-06-12 : 17:45:32

ok,
thanks, someone else got this solved for me:
update MAINTBL set ADJ_DATE = CASE WHEN DATELKUP.DATE_ADJUSTED IS NULL THEN MAINTBL.MYDATE ELSE DATELKUP.DATE_ADJUSTED END
from MAINTBL
left outer join DATELKUP on MAINTBL.MYDATE = DATELKUP.MYDATE

thanks anyway,
kowalsky

kowalsky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources