Reg Query optimization

  • 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

  • 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

    Quick question, why the NOLOCK hint?
    😎
    You will have to post the actual execution plans if you want to have any input on the performance, at the first glance, the changes look superficial.

  • (Nolock) is use for retrieve the data fast at the time  inserting records into hash table also avoid deadlocks 

    But my above   question is different,Kindly suggest if any one knows

  • You really need to read about NOLOCK and what it really does.  Here's a quick article by Denny Cherry, SQL Server MVP, former MCM.
    It's not what you think. It basically allows dirty reads - like using READ UNCOMMITTED in your queries. Which is more important to you, speed or accuracy?

    Maybe you should read Grant Fritchey's book on Execution Plans

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply