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

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

Kivy: Swiping (Carousel & ScreenManager) -

jdbc - Not able to establish database connection in eclipse -