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 2008 Forums
 Transact-SQL (2008)
 Please help me re-write the query

Author  Topic 

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2013-06-17 : 04:07:56
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

52326 Posts

Posted - 2013-06-17 : 04:15:22


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
Yak Posting Veteran

51 Posts

Posted - 2013-06-17 : 07:23:17
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

22864 Posts

Posted - 2013-06-17 : 08:24:03
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
Go to Top of Page
   

- Advertisement -