April 29, 2017 at 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
April 29, 2017 at 6:57 am
Bharat21 - Saturday, April 29, 2017 6:28 AMHi ,I have optimized one query ,Kindly suggest ,Is it right or any effect on result after thisExisting :
while exists (select top 1 * from #vr )
begin
select @sl_no = sl_no
from #vrupdate 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
endOptimized :
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.
April 29, 2017 at 8:21 am
(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
April 29, 2017 at 12:49 pm
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