SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 slow update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kowalsky
Starting Member

USA
29 Posts

Posted - 06/12/2013 :  16:40:40  Show Profile  Reply with Quote
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

USA
29 Posts

Posted - 06/12/2013 :  17:45:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000