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 2008 Forums
 Transact-SQL (2008)
 Please help me re-write the query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LearningSQLKid
Starting Member

Hong Kong
45 Posts

Posted - 06/17/2013 :  04:07:56  Show Profile  Reply with Quote
Hi Guys

I need you help in finding the alternative of the query below. The bedthistory is a big table and when i run the following query it takes ages to run. How can i re-write this query to run it faster.

update cu
set bal= (

select min(amt)
from DebtHistory
where ref = cu.descr
and insert_time = (
select max(insert_time)
from DebtHistory
where ref = cu.descr
and insert_time < cu.action_date
)
)
from collect ca
where (action_date >= @process_dt)


Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  04:15:22  Show Profile  Reply with Quote


update cu
set bal= t.amt
from collect ca
cross apply (select row_number() over (order by insert_time desc) AS Seq,amt
             from DebtHistory
             where ref = cu.descr
             and insert_time < cu.action_date
             )t
where (action_date >= @process_dt)
and Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

LearningSQLKid
Starting Member

Hong Kong
45 Posts

Posted - 06/17/2013 :  07:23:17  Show Profile  Reply with Quote
Thanks visakh.

Its better than the previous one but still taking long . Any idea what should i do ?



Select Knowledge from LearningProcess
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 06/17/2013 :  08:24:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is cu refer to the table collect?. Try this too

update cu
set cu.bal=t.amt from collect cu inner join
(
select t1.ref,min(t1.amt) as amt from DebtHistory as t1 inner join
(
	select ref,max(insert_time) as insert_time
	from DebtHistory group by ref
) as t2 on t1.ref=t2.ref and t1.insert_time=t2.insert_time
group by t1.ref
) as t on t.ref=cu.ref


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 06/17/2013 08:25:08
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.06 seconds. Powered By: Snitz Forums 2000