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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.05 seconds. Powered By: Snitz Forums 2000