sql - rewrite query to remove inner query in tsql to optimize -
i trying optimize following query, according execution plan, sort in inner query has high cost. following query re-written easy read , performs well?
select cl.col1, cl.col2 claim cl (nolock) inner join member mem (nolock) on mem.memid=cl.memid left join payvacation pv (nolock) on cl.claimid = pv.claimid , pv.paymentid = (select top 1 payvacation.paymentid payvacation (nolock), payment (nolock) payvoucher.claimid = cl.claimid , payvacation.paymentid = payment.paymentid order payment.paystatusdate desc)
;with cte ( select cl.col1, cl.col2, cl.claimid claim cl (nolock) inner join member mem (nolock) on mem.memid=cl.memid left join payvacation pv (nolock) on cl.claimid = pv.claimid ), cte2 ( select payvacation.paymentid , payvacation.claimid ,row_number() on (partition payvacation.claimid order payment.paystatusdate desc) rn payvacation (nolock) inner join payment (nolock) on payvacation.paymentid = payment.paymentid inner join cte (nolock) on payvacation.claimid = cl.claimid ) select cl.col1, cl.col2 cte cl inner join cte2 c2 on c2.claimid = cl.claimid , c2.rn = 1
Comments
Post a Comment