Bharat21 - Saturday, April 29, 2017 6:28 AM

Hi ,I have optimized one query ,Kindly suggest ,Is it right or any effect on result after this 

Existing :
       while exists (select top 1 * from #vr )
        begin
            select @sl_no = sl_no
            from #vr

            update Thyrocare..wo_dtl set despatched='C'
            from #vr a ,Thyrocare..wo_dtl (nolock) b
            where a.sdate=b.sdate and a.sl_no=b.sl_no and a.testcode1=b.test_code and a.testcode1 <>''
                and a.testcode1 is not null --and b.despatched not in ('T','R')
            and b.sdate=@sdate
                and b.sl_no=@sl_no and b.PositionInfo1 not like '%RELEASED FROM CHARBI REPORT RELEASE%'
                --and b.PositionInfo1 is null commented this line 21-04-2016
            update Thyrocare..wo_dtl set despatched='C'
            from #vr a ,Thyrocare..wo_dtl (nolock) b
            where a.sdate=b.sdate and a.sl_no=b.sl_no and a.testcode2=b.test_code
                and a.testcode2 <>'' and a.testcode2 is not null --and b.despatched not in ('T','R')
            and b.sdate=@sdate and b.sl_no=@sl_no and b.PositionInfo1 not like '%RELEASED FROM CHARBI REPORT RELEASE%'
            --and b.PositionInfo1 is null commented this line 21-04-2016
        
            delete #vr where sl_no = @sl_no
        end

Optimized :

      select sdate , sl_no , despatched , test_code into #wo_dtl  from thyrocare..wo_dtl with (nolock ) where sdate = @sdate and PositionInfo1 not like '%RELEASED FROM CHARBI REPORT RELEASE%'
        while exists (select top 1 * from #vr )
        begin
            select @sl_no = sl_no
            from #vr
            update Thyrocare..wo_dtl set despatched='C'
            from #vr a , #wo_dtl (nolock) b
            where a.sdate=b.sdate and a.sl_no=b.sl_no and a.testcode1=b.test_code and a.testcode1 <>''
                and a.testcode1 is not null --and b.despatched not in ('T','R')
            --and b.sdate=@sdate
                and b.sl_no=@sl_no --and b.PositionInfo1 not like '%RELEASED FROM CHARBI REPORT RELEASE%'
                --and b.PositionInfo1 is null commented this line 21-04-2016
            update Thyrocare..wo_dtl set despatched='C'
            from #vr a , #wo_dtl (nolock) b
            where a.sdate=b.sdate and a.sl_no=b.sl_no and a.testcode2=b.test_code
                and a.testcode2 <>'' and a.testcode2 is not null --and b.despatched not in ('T','R')
            --and b.sdate=@sdate
             and b.sl_no=@sl_no ---and b.PositionInfo1 not like '%RELEASED FROM CHARBI REPORT RELEASE%'
            --and b.PositionInfo1 is null commented this line 21-04-2016
        
            delete #vr where sl_no = @sl_no
        end