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.
Author |
Topic |
LearningSQLKid
Yak Posting Veteran
51 Posts |
Posted - 2013-06-17 : 04:07:56
|
Hi GuysI 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 cuset 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 cawhere (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 cuset bal= t.amtfrom collect cacross 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 )twhere (action_date >= @process_dt)and Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-06-17 : 08:24:03
|
Is cu refer to the table collect?. Try this tooupdate cuset 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_timegroup by t1.ref) as t on t.ref=cu.ref MadhivananFailing to plan is Planning to fail |
|
|
|
|
|