August 1, 2011 at 10:38 pm
Hi Folks,
am facing a performance problem in one server like there are so many blockings are occured on the server which becomes SQL server is using more CPU memory it almost reaches to '95'. if i kill the process which is blocking by the same process and executing the select statement.
anyone let me know, why a process is resulting more than once after executing the query ' sp_who2 active'.
And am attaching the screenshot for this..
TIA...
August 2, 2011 at 6:26 am
I'd bet that this is a massive query that processes a tonne of data and that is using parallelism.
Killing the connection is not going to solve anything. You need to make that query run faster or run in another transaction isolation level (for exemple SNAPSHOT).
Can you post the actual excution plan of that query? I'll see if I can speed it up.
August 2, 2011 at 7:37 am
srinath.vanama (8/1/2011)
Hi Folks,am facing a performance problem in one server like there are so many blockings are occured on the server which becomes SQL server is using more CPU memory it almost reaches to '95'. if i kill the process which is blocking by the same process and executing the select statement.
anyone let me know, why a process is resulting more than once after executing the query ' sp_who2 active'.
And am attaching the screenshot for this..
TIA...
Ummmm.... SPID 64 is "blocking" itself in your graphic. While it's not uncommon for some self blocking to occur, your graphic shows a LOT of self blocking. This is pretty typical of a proc that has loops and calls to other procs in a RBAR fashion but I don't know for sure because I've not seen the proc. There are other possibilities that may cause this but they typically all boil down to the same fix...
You can certainly play with some indexing (and, maybe, MAXDOP settings) to see if you can get the performance up so there's less blocking but, usually, the only way to fix something like this is to rewrite a substantial portion of the code to operate in a more setbased fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2011 at 9:36 am
Hi ,
Select * from sysproecessesm where blocked <>0 , you can find blocking SPID 's
Dbcc inputbuffer(spid) -- you can find the Current running Query .
First try to find out from which hostname the queries are running ??
August 2, 2011 at 10:01 am
Lavanyasri (8/2/2011)
Hi ,Select * from sysproecessesm where blocked <>0 , you can find blocking SPID 's
Dbcc inputbuffer(spid) -- you can find the Current running Query .
First try to find out from which hostname the queries are running ??
How would that help for self blocking??? Find the calling sp and debug that.
August 3, 2011 at 3:23 am
HI ,
How to find calling SP ???
August 3, 2011 at 4:04 am
Hi folks,
Thanks for your replies,
For temporarily i created certain indices, so that queries are performing not bad. But, still am not satisfied with that moreover i'd like to improve the query performance.
Ninja, kindly help me how to run this query in other isolation level as you mentioned in previous post.
Lavanyasri,
Mentioned process is an automated Job. If u go through the Job Properties u can find it is calling which SP on which database.
August 3, 2011 at 5:56 am
Lavanyasri (8/3/2011)
HI ,How to find calling SP ???
Borrowed from the activity monitor...
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
August 3, 2011 at 5:59 am
srinath.vanama (8/3/2011)
Hi folks,Thanks for your replies,
For temporarily i created certain indices, so that queries are performing not bad. But, still am not satisfied with that moreover i'd like to improve the query performance.
Ninja, kindly help me how to run this query in other isolation level as you mentioned in previous post.
Lavanyasri,
Mentioned process is an automated Job. If u go through the Job Properties u can find it is calling which SP on which database.
Snapshot needs to first be allowed on the DB, then needs to be activated for the piece of code you are running.
I strongly recommend testing this outside prod to see what kind of impact you will get on tempdb (where the row versions are kept).
http://msdn.microsoft.com/en-us/library/ms173763.aspx
I can help with tuning that query but I'll need to see the code and actual execution plan along with the table's definition (including keys and indexes)
August 4, 2011 at 3:01 am
Only time i've had issues of a spid blocking itself this much is as Jeff stated, looping/cursor based procedures/scripts and sometimes parallelism.
As ninja says if you can provide us with that information then we can offer guidance on what you could do to tune it but it could very well be that a re-write of the procedure is in order......
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 4, 2011 at 4:26 am
Hi folks,
This is the SP. am facing the problem.
USE [ABC]
GO
/****** Object: StoredProcedure [dbo].[usp_update_fruits]
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select id,mygod from pqr
ALTER procedure [dbo].[usp_update_fruits]
as
begin
begin try
create table #my (id bigint,mygod varchar(20))
create table #my_new (id bigint identity,mygod varchar(20))
declare @i int,@count int,@mygod varchar(20),@startingBox money,@presentBox money
declare @startingBoxByCassete money,@CurrentCahsByCassete money
declare @CAS1_LEVEL_STATUS int,@CAS2_LEVEL_STATUS int,@CAS3_LEVEL_STATUS int,@CAS4_LEVEL_STATUS int,@CAS5_LEVEL_STATUS int,@LEVEL_STATUS int
declare @presentBox1 int,@presentBox2 int,@presentBox3 int,@presentBox4 int,@presentBox5 int
declare @data table (id int identity(1,1), mygod varchar(20),startingBox money,presentBox money
,startBox1 int,startBox2 int,startBox3 int,startBox4 int,startBox5 int
,presentBox1 int,presentBox2 int,presentBox3 int,presentBox4 int,presentBox5 int
,CAS1_LEVEL_STATUS int,CAS2_LEVEL_STATUS int,CAS3_LEVEL_STATUS int,CAS4_LEVEL_STATUS int,CAS5_LEVEL_STATUS int,my_id int,LEVEL_STATUS int)
declare @MY_ID int
CREATE TABLE #MT(
[MTVDEV] [char](10) NOT NULL,
[CUDEN1] [bigint] NULL,
[CUDEN2] [bigint] NULL,
[CUDEN3] [bigint] NULL,
[CUDEN4] [bigint] NULL,
[CUDEN5] [bigint] NULL,
[CUCRT1] [char](1) NULL,
[CUCRT2] [char](1) NULL,
[CUCRT3] [char](1) NULL,
[CUCRT4] [char](1) NULL,
[CUCRT5] [char](1) NULL,
[CUCUR1] [char](3) NULL,
[CUCUR2] [char](3) NULL,
[CUCUR3] [char](3) NULL,
[CUCUR4] [char](3) NULL,
[CUCUR5] [char](3) NULL,
[ERCRT1] [tinyint] NULL,
[ERCRT2] [tinyint] NULL,
[ERCRT3] [tinyint] NULL,
[ERCRT4] [tinyint] NULL,
[ERCRT5] [tinyint] NULL,
[CUSCC1] [bigint] NULL,
[CUSCC2] [bigint] NULL,
[CUSCC3] [bigint] NULL,
[CUSCC4] [bigint] NULL,
[CUSCC5] [bigint] NULL,
[CU#BC1] [int] NULL,
[CU#BC2] [int] NULL,
[CU#BC3] [int] NULL,
[CU#BC4] [int] NULL,
[CU#BC5] [int] NULL,
[MaxanceMin1] [int] NULL,
[MaxanceMin2] [int] NULL,
[MaxanceMin3] [int] NULL,
[MaxanceMin4] [int] NULL,
[MaxanceMin5] [int] NULL,
startingBoxByCassete money null,
CurrentBoxByCassete money null
)
--insert into #my(id,mygod) select id,mygod from pqr
insert into #my(id,mygod) select 1,InternalRefNumber from dbo.lakalaka
insert into #my_new(mygod) select distinct mygod from #my
set @count=@@ROWCOUNT
set @i=1
insert into #mt([MTVDEV]
,[CUDEN1]
,[CUDEN2]
,[CUDEN3]
,[CUDEN4]
,[CUDEN5]
,[CUCRT1]
,[CUCRT2]
,[CUCRT3]
,[CUCRT4]
,[CUCRT5]
,[CUCUR1]
,[CUCUR2]
,[CUCUR3]
,[CUCUR4]
,[CUCUR5]
,[ERCRT1]
,[ERCRT2]
,[ERCRT3]
,[ERCRT4]
,[ERCRT5]
,[CUSCC1]
,[CUSCC2]
,[CUSCC3]
,[CUSCC4]
,[CUSCC5]
,[CU#BC1]
,[CU#BC2]
,[CU#BC3]
,[CU#BC4]
,[CU#BC5]
,MaxanceMin1
,MaxanceMin2
,MaxanceMin3
,MaxanceMin4
,MaxanceMin5
,startingBoxByCassete
,CurrentBoxByCassete
)
SELECT [MTVDEV]
,[CUDEN1]--[TXDEN1]--[CUDEN1]
,[CUDEN2]--[TXDEN2]--[CUDEN2]
,[CUDEN3]--[TXDEN3]--[CUDEN3]
,[CUDEN4]--[TXDEN4]--[CUDEN4]
,[CUDEN5]--[TXDEN5]--[CUDEN5]
,[CUCRT1]
,[CUCRT2]
,[CUCRT3]
,[CUCRT4]
,[CUCRT5]
,[CUCUR1]
,[CUCUR2]
,[CUCUR3]
,[CUCUR4]
,[CUCUR5]
,[ERCRT1]
,[ERCRT2]
,[ERCRT3]
,[ERCRT4]
,[ERCRT5]
,[CUSCC1]--[txscb1]--[CUSCC1]
,[CUSCC2]--[txscb2]--[CUSCC2]
,[CUSCC3]--[txscb3]--[CUSCC3]
,[CUSCC4]--[txscb4]--[CUSCC4]
,[CUSCC5]--[txscb5]--[CUSCC5]
,[CU#BC1]
,[CU#BC2]
,[CU#BC3]
,[CU#BC4]
,[CU#BC5]
,dbo.usp_getcurrentBox([CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CU#BC1],[CU#BC2],[CU#BC3],[CU#BC4],[CU#BC5],[CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CUSCC1],[CUSCC2],[CUSCC3],[CUSCC4],[CUSCC5],1)
,dbo.usp_getcurrentBox([CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CU#BC1],[CU#BC2],[CU#BC3],[CU#BC4],[CU#BC5],[CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CUSCC1],[CUSCC2],[CUSCC3],[CUSCC4],[CUSCC5],2)
,dbo.usp_getcurrentBox([CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CU#BC1],[CU#BC2],[CU#BC3],[CU#BC4],[CU#BC5],[CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CUSCC1],[CUSCC2],[CUSCC3],[CUSCC4],[CUSCC5],3)
,dbo.usp_getcurrentBox([CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CU#BC1],[CU#BC2],[CU#BC3],[CU#BC4],[CU#BC5],[CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CUSCC1],[CUSCC2],[CUSCC3],[CUSCC4],[CUSCC5],4)
,dbo.usp_getcurrentBox([CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CU#BC1],[CU#BC2],[CU#BC3],[CU#BC4],[CU#BC5],[CUDEN1],[CUDEN2],[CUDEN3],[CUDEN4],[CUDEN5],[CUCUR1],[CUCUR2],[CUCUR3],[CUCUR4],[CUCUR5],[CUSCC1],[CUSCC2],[CUSCC3],[CUSCC4],[CUSCC5],5)
,(ISNULL([CUSCC1],0)/100) + (ISNULL([CUSCC2],0)/100) + (ISNULL([CUSCC3],0)/100) + (ISNULL([CUSCC4],0)/100) + (ISNULL([CUSCC5],0)/100)
,((ISNULL([CUSCC1],0)/100) + (ISNULL([CUSCC2],0)/100) + (ISNULL([CUSCC3],0)/100) + (ISNULL([CUSCC4],0)/100) + (ISNULL([CUSCC5],0)/100)) - (((ISNULL([CUDEN1],0) * ISNULL([CU#BC1],0) ) + (ISNULL([CUDEN2],0) * ISNULL([CU#BC2],0) ) + (ISNULL([CUDEN3],0) *
ISNULL([CU#BC3],0) )+ (ISNULL([CUDEN4],0) * ISNULL([CU#BC4],0) ) + (ISNULL([CUDEN5],0) * ISNULL([CU#BC5],0) ))/100)
FROM [soap].[ala].[dbo].[MT] where mtvdev in(select mygod from #my) and mtdele <>'D'
INSERT INTO [ABC].[dbo].[fruits]
([_MYID]
,[NOOFCASSETTES]
,[CAS1_1]
,[CAS1_2]
,[CAS1_3]
,[CAS1_4]
,[CAS1_5]
,[CAS2_1]
,[CAS2_2]
,[CAS2_3]
,[CAS2_4]
,[CAS2_5]
,[CAS3_1]
,[CAS3_2]
,[CAS3_3]
,[CAS3_4]
,[CAS3_5]
,[CAS4_1]
,[CAS4_2]
,[CAS4_3]
,[CAS4_4]
,[CAS4_5]
,[CAS5_1]
,[CAS5_2]
,[CAS5_3]
,[CAS5_4]
,[CAS5_5])
select AssetInvID,4
,[CUDEN1]/100
,[CUCUR1]
,[ERCRT1]
,[CUSCC1]/100
,(MaxanceMin1*[CUDEN1]/100)
,[CUDEN2]/100
,[CUCUR2]
,[ERCRT2]
,[CUSCC2]/100
,((MaxanceMin2*[CUDEN2]/100))
,[CUDEN3]/100
,[CUCUR3]
,[ERCRT3]
,[CUSCC3]/100
,((MaxanceMin3*[CUDEN3]/100))
,[CUDEN4]/100
,[CUCUR4]
,[ERCRT4]
,[CUSCC4]/100
,((MaxanceMin4*[CUDEN4]/100))
,[CUDEN5]/100
,[CUCUR5]
,[ERCRT5]
,[CUSCC5]/100
,((MaxanceMin1*[CUDEN5]/100))
from #mt mt
inner join [ABC].[dbo].lakalaka *** on mt.mtvdev=***.InternalRefNumber
left outer join [ABC].[dbo].[fruits] ac on _MYID=AssetInvID
where _MYID is null
update a
set [CAS1_1]=[CUDEN1]/100
,[CAS1_2]=[CUCUR1]
,[CAS1_3]=[ERCRT1]
,[CAS1_4]=[CUSCC1]/100
,[CAS1_5]=((MaxanceMin1*[CUDEN1]/100))
,[CAS2_1]=[CUDEN2]/100
,[CAS2_2]=[CUCUR2]
,[CAS2_3]=[ERCRT2]
,[CAS2_4]=[CUSCC2]/100
,[CAS2_5]=((MaxanceMin2*[CUDEN2]/100))
,[CAS3_1]=[CUDEN3]/100
,[CAS3_2]=[CUCUR3]
,[CAS3_3]=[ERCRT3]
,[CAS3_4]=[CUSCC3]/100
,[CAS3_5]=((MaxanceMin3*[CUDEN3]/100))
,[CAS4_1]=[CUDEN4]/100
,[CAS4_2]=[CUCUR4]
,[CAS4_3]=[ERCRT4]
,[CAS4_4]=[CUSCC4]/100
,[CAS4_5]=((MaxanceMin4*[CUDEN4]/100))
,[CAS5_1]=[CUDEN5]/100
,[CAS5_2]=[CUCUR5]
,[CAS5_3]=[ERCRT5]
,[CAS5_4]=[CUSCC5]/100
,[CAS5_5]=((MaxanceMin5*[CUDEN5]/100))
from [ABC].[dbo].[fruits] a
inner join [ABC].[dbo].lakalaka *** on _MYID=AssetInvID
inner join #mt m on m.mtvdev=***.InternalRefNumber
insert into @data(mygod,startingBox,presentBox
,startBox1,startBox2,startBox3,startBox4,startBox5
,presentBox1,presentBox2,presentBox3,presentBox4,presentBox5
,CAS1_LEVEL_STATUS,CAS2_LEVEL_STATUS,CAS3_LEVEL_STATUS,CAS4_LEVEL_STATUS,CAS5_LEVEL_STATUS,my_id,LEVEL_STATUS)
select ***.InternalRefNumber,(isnull(CUSCC1,0)/100 + isnull(CUSCC2,0)/100 + isnull(CUSCC3,0)/100 + isnull(CUSCC4,0)/100 + isnull(CUSCC5,0)/100)
,ISNULL((MaxanceMin1*[CUDEN1]/100),0)
+ ISNULL((MaxanceMin2*[CUDEN2]/100),0)
+ ISNULL((MaxanceMin3*[CUDEN3]/100),0)
+ ISNULL((MaxanceMin4*[CUDEN4]/100),0)
+ ISNULL((MaxanceMin5*[CUDEN5]/100),0)
,isnull(CUSCC1,0)/100
, isnull(CUSCC2,0)/100
, isnull(CUSCC3,0)/100
, isnull(CUSCC4,0)/100
, isnull(CUSCC5,0)/100
,ISNULL((MaxanceMin1*[CUDEN1]/100),0)
,ISNULL((MaxanceMin2*[CUDEN2]/100),0)
,ISNULL((MaxanceMin3*[CUDEN3]/100),0)
,ISNULL((MaxanceMin4*[CUDEN4]/100),0)
,ISNULL((MaxanceMin5*[CUDEN5]/100),0)
, case isnull(CUSCC1,0) when 0 then 1 else CAS1_LEVEL_STATUS end
, case isnull(CUSCC2,0) when 0 then 1 else CAS2_LEVEL_STATUS end
, case isnull(CUSCC3,0) when 0 then 1 else CAS3_LEVEL_STATUS end
, case isnull(CUSCC4,0) when 0 then 1 else CAS4_LEVEL_STATUS end
, case isnull(CUSCC5,0) when 0 then 1 else CAS5_LEVEL_STATUS end,_MYID,LEVEL_STATUS
from #MT mt
inner join [ABC].[dbo].lakalaka *** on mt.mtvdev=***.InternalRefNumber
inner join dbo.fruits on _MYID=AssetInvID
set @count = @@rowcount
while @i < = @count
begin
select @mygod=mygod,@startingBox=startingBox,@presentBox=presentBox
,@presentBox1=case isnull(presentBox1,0) when 0 then 0 else (presentBox1*100)/startBox1 end
,@presentBox2=case isnull(presentBox2,0) when 0 then 0 else (presentBox2*100)/startBox2 end
,@presentBox3=case isnull(presentBox3,0) when 0 then 0 else (presentBox3*100)/startBox3 end
,@presentBox4=case isnull(presentBox4,0) when 0 then 0 else (presentBox4*100)/startBox4 end
,@presentBox5=case isnull(presentBox5,0) when 0 then 0 else (presentBox5*100)/startBox5 end
--,@presentBox1=presentBox1,@presentBox2=presentBox2,@presentBox3=presentBox3,@presentBox4=presentBox4,@presentBox5=presentBox5
,@CAS1_LEVEL_STATUS=CAS1_LEVEL_STATUS ,@CAS2_LEVEL_STATUS =CAS2_LEVEL_STATUS ,@CAS3_LEVEL_STATUS=CAS3_LEVEL_STATUS ,@CAS4_LEVEL_STATUS=CAS4_LEVEL_STATUS ,@CAS5_LEVEL_STATUS=CAS5_LEVEL_STATUS,@MY_ID=MY_ID,@LEVEL_STATUS=LEVEL_STATUS
from @data where id=@i
/*INSERT INTO [ABC].[dbo].[aa_box]
([startingBox]
,[presentBox]
,[mygod]
,[date])
select @startingBox,@presentBox,@mygod,getdate()
*/
if @startingBox > 0
begin
--if (@presentBox / @startingBox ) <0.101 --and
--((@CAS1_LEVEL_STATUS =1 or @presentBox1 =0 ) and (@CAS2_LEVEL_STATUS =1 or @presentBox2 =0 ) and (@CAS3_LEVEL_STATUS =1 or @presentBox3 =0 ) and (@CAS4_LEVEL_STATUS =1 or @presentBox4 =0 ) and (@CAS5_LEVEL_STATUS =1 or @presentBox5 =0 ))
if (@presentBox / @startingBox ) <0.031 --and
--((@CAS1_LEVEL_STATUS =1 or @presentBox1 =0 ) and (@CAS2_LEVEL_STATUS =1 or @presentBox2 =0 ) and (@CAS3_LEVEL_STATUS =1 or @presentBox3 =0 ) and (@CAS4_LEVEL_STATUS =1 or @presentBox4 =0 ) and (@CAS5_LEVEL_STATUS =1 or @presentBox5 =0 ))
begin
if isnull(@LEVEL_STATUS,0) <>2
begin
--change by bhushan on 21-04-2011
set @MY_ID=@MY_ID
exec [ABC].dbo.usp_SendMessageProcess 'ES2CL3',@mygod
update fruits set LEVEL_STATUS=2 where _MYID=@MY_ID
--change by bhushan on 21-04-2011
end
end
--else if (@presentBox / @startingBox ) <0.25 --and (@CAS1_LEVEL_STATUS =1 and @CAS2_LEVEL_STATUS =1 and @CAS3_LEVEL_STATUS =1 and @CAS4_LEVEL_STATUS =1 and @CAS5_LEVEL_STATUS =1 )
else if (@presentBox / @startingBox ) <0.201 --and (@CAS1_LEVEL_STATUS =1 and @CAS2_LEVEL_STATUS =1 and @CAS3_LEVEL_STATUS =1 and @CAS4_LEVEL_STATUS =1 and @CAS5_LEVEL_STATUS =1 )
begin
if isnull(@LEVEL_STATUS,0) <>1
begin
exec [ABC].dbo.usp_SendMessageProcess 'ES2CL2',@mygod
update fruits set LEVEL_STATUS=1 where _MYID=@MY_ID
end
end
--else if (@presentBox / @startingBox ) >0.25
else if (@presentBox / @startingBox ) >0.2
begin
if isnull(@LEVEL_STATUS,0) <>0
begin
exec [ABC].dbo.usp_SendMessageProcess 'ES2CL0',@mygod
exec [ABC].dbo.usp_SendMessageProcess 'ES2CLL0',@mygod
update fruits set LEVEL_STATUS=0 where _MYID=@MY_ID
end
end
end
set @i=@i + 1
end
--delete from pqr where id in(select id from #my)
UPDATE MS
SETMS.TXTDV1 = a.TXTDV1,
MS.TXTDV2 = a.TXTDV2,
MS.TXTDV3 = a.TXTDV3,
MS.TXTDV4 = a.TXTDV4 ,
MS._TCPIDL = a._TCPIDL,
MS._TCPBI = a._TCPBI,
MS._TCPBO = a._TCPBO,
MS.cuslc1 = a.cuslc1,
MS.cuslc2 = a.cuslc2,
MS.cuslc3 = a.cuslc3,
MS.cuslc4 = a.cuslc4
FROM
[ABC].[dbo].[MT_STATE] MS INNER JOIN
(SELECT MTVDEV,TXTDV1,TXTDV2,TXTDV3,TXTDV4 ,_TCPIDL,_TCPBI,_TCPBO,cuslc1,cuslc2,cuslc3,cuslc4
FROM [soap].Ala.dbo.MT_VIEW
WHERE MTDELE <> 'D') a
ON MS.MTVDEV = a.MTVDEV
UPDATE MS
SET MS.TCSTCA = a.TCSTCA ,
MS.TCCUCA = a.TCCUCA
FROM
[ABC].[dbo].[MT_STATE] MS INNER JOIN
(SELECT MTVDEV ,TCSTCA, (TCSTCA - TCCAOU) as TCCUCA
FROM [soap].ala.dbo.MT_VIEW
) a
ON MS.MTVDEV = a.MTVDEV
update ms
set StartBoxByCassette=startingBoxByCassete,
ms.CurrentBoxByCassette=m.CurrentBoxByCassete from [ABC].[dbo].[MT_STATE] MS
INNER JOIN #MT m on MS.MTVDEV = m.MTVDEV
end try
begin catch
INSERT INTO [aaaa_error]
([erroeid]
,[errormsg],lineNum,SPName)
select error_number(),error_message(),error_line(),error_procedure()
end catch
end
August 4, 2011 at 5:42 am
Reformated so that's easier on the eyes.
Can you post the actual execution plans (at least 1 loop)
There's a lot going on in this and it's not easy to make this go fast on a web forum... we'll see what we can do.
USE [ABC]
GO
/****** Object: StoredProcedure [dbo].[usp_update_fruits]
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select id,mygod from pqr
ALTER PROCEDURE [dbo].[usp_update_fruits]
AS
BEGIN
BEGIN TRY
CREATE TABLE #my
(
id BIGINT
, mygod VARCHAR(20)
)
CREATE TABLE #my_new
(
id BIGINT IDENTITY
, mygod VARCHAR(20)
)
DECLARE
@i INT
, @count INT
, @mygod VARCHAR(20)
, @startingBox MONEY
, @presentBox MONEY
DECLARE
@startingBoxByCassete MONEY
, @CurrentCahsByCassete MONEY
DECLARE
@CAS1_LEVEL_STATUS INT
, @CAS2_LEVEL_STATUS INT
, @CAS3_LEVEL_STATUS INT
, @CAS4_LEVEL_STATUS INT
, @CAS5_LEVEL_STATUS INT
, @LEVEL_STATUS INT
DECLARE
@presentBox1 INT
, @presentBox2 INT
, @presentBox3 INT
, @presentBox4 INT
, @presentBox5 INT
DECLARE @data TABLE
(
id INT IDENTITY(1 , 1)
, mygod VARCHAR(20)
, startingBox MONEY
, presentBox MONEY
, startBox1 INT
, startBox2 INT
, startBox3 INT
, startBox4 INT
, startBox5 INT
, presentBox1 INT
, presentBox2 INT
, presentBox3 INT
, presentBox4 INT
, presentBox5 INT
, CAS1_LEVEL_STATUS INT
, CAS2_LEVEL_STATUS INT
, CAS3_LEVEL_STATUS INT
, CAS4_LEVEL_STATUS INT
, CAS5_LEVEL_STATUS INT
, my_id INT
, LEVEL_STATUS INT
)
DECLARE @MY_ID INT
CREATE TABLE #MT
(
[MTVDEV] [char](10) NOT NULL
, [CUDEN1] [bigint] NULL
, [CUDEN2] [bigint] NULL
, [CUDEN3] [bigint] NULL
, [CUDEN4] [bigint] NULL
, [CUDEN5] [bigint] NULL
, [CUCRT1] [char](1) NULL
, [CUCRT2] [char](1) NULL
, [CUCRT3] [char](1) NULL
, [CUCRT4] [char](1) NULL
, [CUCRT5] [char](1) NULL
, [CUCUR1] [char](3) NULL
, [CUCUR2] [char](3) NULL
, [CUCUR3] [char](3) NULL
, [CUCUR4] [char](3) NULL
, [CUCUR5] [char](3) NULL
, [ERCRT1] [tinyint] NULL
, [ERCRT2] [tinyint] NULL
, [ERCRT3] [tinyint] NULL
, [ERCRT4] [tinyint] NULL
, [ERCRT5] [tinyint] NULL
, [CUSCC1] [bigint] NULL
, [CUSCC2] [bigint] NULL
, [CUSCC3] [bigint] NULL
, [CUSCC4] [bigint] NULL
, [CUSCC5] [bigint] NULL
, [CU#BC1] [int] NULL
, [CU#BC2] [int] NULL
, [CU#BC3] [int] NULL
, [CU#BC4] [int] NULL
, [CU#BC5] [int] NULL
, [MaxanceMin1] [int] NULL
, [MaxanceMin2] [int] NULL
, [MaxanceMin3] [int] NULL
, [MaxanceMin4] [int] NULL
, [MaxanceMin5] [int] NULL
, startingBoxByCassete MONEY NULL
, CurrentBoxByCassete MONEY NULL
)
--insert into #my(id,mygod) select id,mygod from pqr
INSERT INTO
#my ( id , mygod )
SELECT
1
, InternalRefNumber
FROM
dbo.lakalaka
INSERT INTO
#my_new ( mygod )
SELECT DISTINCT
mygod
FROM
#my
SET @count = @@ROWCOUNT
SET @i = 1
INSERT INTO
#mt
(
[MTVDEV]
, [CUDEN1]
, [CUDEN2]
, [CUDEN3]
, [CUDEN4]
, [CUDEN5]
, [CUCRT1]
, [CUCRT2]
, [CUCRT3]
, [CUCRT4]
, [CUCRT5]
, [CUCUR1]
, [CUCUR2]
, [CUCUR3]
, [CUCUR4]
, [CUCUR5]
, [ERCRT1]
, [ERCRT2]
, [ERCRT3]
, [ERCRT4]
, [ERCRT5]
, [CUSCC1]
, [CUSCC2]
, [CUSCC3]
, [CUSCC4]
, [CUSCC5]
, [CU#BC1]
, [CU#BC2]
, [CU#BC3]
, [CU#BC4]
, [CU#BC5]
, MaxanceMin1
, MaxanceMin2
, MaxanceMin3
, MaxanceMin4
, MaxanceMin5
, startingBoxByCassete
, CurrentBoxByCassete
)
SELECT
[MTVDEV]
, [CUDEN1]--[TXDEN1]--[CUDEN1]
, [CUDEN2]--[TXDEN2]--[CUDEN2]
, [CUDEN3]--[TXDEN3]--[CUDEN3]
, [CUDEN4]--[TXDEN4]--[CUDEN4]
, [CUDEN5]--[TXDEN5]--[CUDEN5]
, [CUCRT1]
, [CUCRT2]
, [CUCRT3]
, [CUCRT4]
, [CUCRT5]
, [CUCUR1]
, [CUCUR2]
, [CUCUR3]
, [CUCUR4]
, [CUCUR5]
, [ERCRT1]
, [ERCRT2]
, [ERCRT3]
, [ERCRT4]
, [ERCRT5]
, [CUSCC1]--[txscb1]--[CUSCC1]
, [CUSCC2]--[txscb2]--[CUSCC2]
, [CUSCC3]--[txscb3]--[CUSCC3]
, [CUSCC4]--[txscb4]--[CUSCC4]
, [CUSCC5]--[txscb5]--[CUSCC5]
, [CU#BC1]
, [CU#BC2]
, [CU#BC3]
, [CU#BC4]
, [CU#BC5]
, dbo.usp_getcurrentBox([CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CU#BC1] , [CU#BC2] ,
[CU#BC3] , [CU#BC4] , [CU#BC5] ,
[CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CUSCC1] , [CUSCC2] ,
[CUSCC3] , [CUSCC4] , [CUSCC5] , 1)
, dbo.usp_getcurrentBox([CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CU#BC1] , [CU#BC2] ,
[CU#BC3] , [CU#BC4] , [CU#BC5] ,
[CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CUSCC1] , [CUSCC2] ,
[CUSCC3] , [CUSCC4] , [CUSCC5] , 2)
, dbo.usp_getcurrentBox([CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CU#BC1] , [CU#BC2] ,
[CU#BC3] , [CU#BC4] , [CU#BC5] ,
[CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CUSCC1] , [CUSCC2] ,
[CUSCC3] , [CUSCC4] , [CUSCC5] , 3)
, dbo.usp_getcurrentBox([CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CU#BC1] , [CU#BC2] ,
[CU#BC3] , [CU#BC4] , [CU#BC5] ,
[CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CUSCC1] , [CUSCC2] ,
[CUSCC3] , [CUSCC4] , [CUSCC5] , 4)
, dbo.usp_getcurrentBox([CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CU#BC1] , [CU#BC2] ,
[CU#BC3] , [CU#BC4] , [CU#BC5] ,
[CUDEN1] , [CUDEN2] , [CUDEN3] ,
[CUDEN4] , [CUDEN5] , [CUCUR1] ,
[CUCUR2] , [CUCUR3] , [CUCUR4] ,
[CUCUR5] , [CUSCC1] , [CUSCC2] ,
[CUSCC3] , [CUSCC4] , [CUSCC5] , 5)
, ( ISNULL([CUSCC1] , 0) / 100 ) + ( ISNULL([CUSCC2] , 0)
/ 100 )
+ ( ISNULL([CUSCC3] , 0) / 100 ) + ( ISNULL([CUSCC4] , 0)
/ 100 )
+ ( ISNULL([CUSCC5] , 0) / 100 )
, ( ( ISNULL([CUSCC1] , 0) / 100 ) + ( ISNULL([CUSCC2] , 0)
/ 100 )
+ ( ISNULL([CUSCC3] , 0) / 100 ) + ( ISNULL([CUSCC4] , 0)
/ 100 )
+ ( ISNULL([CUSCC5] , 0) / 100 ) )
- ( ( ( ISNULL([CUDEN1] , 0) * ISNULL([CU#BC1] , 0) )
+ ( ISNULL([CUDEN2] , 0) * ISNULL([CU#BC2] , 0) )
+ ( ISNULL([CUDEN3] , 0) * ISNULL([CU#BC3] , 0) )
+ ( ISNULL([CUDEN4] , 0) * ISNULL([CU#BC4] , 0) )
+ ( ISNULL([CUDEN5] , 0) * ISNULL([CU#BC5] , 0) ) )
/ 100 )
FROM
[soap].[ala].[dbo].[MT]
WHERE
mtvdev IN ( SELECT
mygod
FROM
#my )
AND mtdele <> 'D'
INSERT INTO
[ABC].[dbo].[fruits]
(
[_MYID]
, [NOOFCASSETTES]
, [CAS1_1]
, [CAS1_2]
, [CAS1_3]
, [CAS1_4]
, [CAS1_5]
, [CAS2_1]
, [CAS2_2]
, [CAS2_3]
, [CAS2_4]
, [CAS2_5]
, [CAS3_1]
, [CAS3_2]
, [CAS3_3]
, [CAS3_4]
, [CAS3_5]
, [CAS4_1]
, [CAS4_2]
, [CAS4_3]
, [CAS4_4]
, [CAS4_5]
, [CAS5_1]
, [CAS5_2]
, [CAS5_3]
, [CAS5_4]
, [CAS5_5]
)
SELECT
AssetInvID
, 4
, [CUDEN1] / 100
, [CUCUR1]
, [ERCRT1]
, [CUSCC1] / 100
, ( MaxanceMin1 * [CUDEN1] / 100 )
, [CUDEN2] / 100
, [CUCUR2]
, [ERCRT2]
, [CUSCC2] / 100
, ( (MaxanceMin2 * [CUDEN2] / 100) )
, [CUDEN3] / 100
, [CUCUR3]
, [ERCRT3]
, [CUSCC3] / 100
, ( (MaxanceMin3 * [CUDEN3] / 100) )
, [CUDEN4] / 100
, [CUCUR4]
, [ERCRT4]
, [CUSCC4] / 100
, ( (MaxanceMin4 * [CUDEN4] / 100) )
, [CUDEN5] / 100
, [CUCUR5]
, [ERCRT5]
, [CUSCC5] / 100
, ( (MaxanceMin1 * [CUDEN5] / 100) )
FROM
#mt mt
INNER JOIN [ABC].[dbo].lakalaka SSS
ON mt.mtvdev = SSS.InternalRefNumber
LEFT OUTER JOIN [ABC].[dbo].[fruits] ac
ON _MYID = AssetInvID
WHERE
_MYID IS NULL
UPDATE
a
SET
[CAS1_1] = [CUDEN1] / 100
, [CAS1_2] = [CUCUR1]
, [CAS1_3] = [ERCRT1]
, [CAS1_4] = [CUSCC1] / 100
, [CAS1_5] = ( (MaxanceMin1 * [CUDEN1] / 100) )
, [CAS2_1] = [CUDEN2] / 100
, [CAS2_2] = [CUCUR2]
, [CAS2_3] = [ERCRT2]
, [CAS2_4] = [CUSCC2] / 100
, [CAS2_5] = ( (MaxanceMin2 * [CUDEN2] / 100) )
, [CAS3_1] = [CUDEN3] / 100
, [CAS3_2] = [CUCUR3]
, [CAS3_3] = [ERCRT3]
, [CAS3_4] = [CUSCC3] / 100
, [CAS3_5] = ( (MaxanceMin3 * [CUDEN3] / 100) )
, [CAS4_1] = [CUDEN4] / 100
, [CAS4_2] = [CUCUR4]
, [CAS4_3] = [ERCRT4]
, [CAS4_4] = [CUSCC4] / 100
, [CAS4_5] = ( (MaxanceMin4 * [CUDEN4] / 100) )
, [CAS5_1] = [CUDEN5] / 100
, [CAS5_2] = [CUCUR5]
, [CAS5_3] = [ERCRT5]
, [CAS5_4] = [CUSCC5] / 100
, [CAS5_5] = ( (MaxanceMin5 * [CUDEN5] / 100) )
FROM
[ABC].[dbo].[fruits] a
INNER JOIN [ABC].[dbo].lakalaka SSS
ON _MYID = AssetInvID
INNER JOIN #mt m
ON m.mtvdev = SSS.InternalRefNumber
INSERT INTO
(
mygod
, startingBox
, presentBox
, startBox1
, startBox2
, startBox3
, startBox4
, startBox5
, presentBox1
, presentBox2
, presentBox3
, presentBox4
, presentBox5
, CAS1_LEVEL_STATUS
, CAS2_LEVEL_STATUS
, CAS3_LEVEL_STATUS
, CAS4_LEVEL_STATUS
, CAS5_LEVEL_STATUS
, my_id
, LEVEL_STATUS
)
SELECT
SSS.InternalRefNumber
, ( ISNULL(CUSCC1 , 0) / 100 + ISNULL(CUSCC2 , 0) / 100
+ ISNULL(CUSCC3 , 0) / 100 + ISNULL(CUSCC4 , 0) / 100
+ ISNULL(CUSCC5 , 0) / 100 )
, ISNULL(( MaxanceMin1 * [CUDEN1] / 100 ) , 0)
+ ISNULL(( MaxanceMin2 * [CUDEN2] / 100 ) , 0)
+ ISNULL(( MaxanceMin3 * [CUDEN3] / 100 ) , 0)
+ ISNULL(( MaxanceMin4 * [CUDEN4] / 100 ) , 0)
+ ISNULL(( MaxanceMin5 * [CUDEN5] / 100 ) , 0)
, ISNULL(CUSCC1 , 0) / 100
, ISNULL(CUSCC2 , 0) / 100
, ISNULL(CUSCC3 , 0) / 100
, ISNULL(CUSCC4 , 0) / 100
, ISNULL(CUSCC5 , 0) / 100
, ISNULL(( MaxanceMin1 * [CUDEN1] / 100 ) , 0)
, ISNULL(( MaxanceMin2 * [CUDEN2] / 100 ) , 0)
, ISNULL(( MaxanceMin3 * [CUDEN3] / 100 ) , 0)
, ISNULL(( MaxanceMin4 * [CUDEN4] / 100 ) , 0)
, ISNULL(( MaxanceMin5 * [CUDEN5] / 100 ) , 0)
, CASE ISNULL(CUSCC1 , 0)
WHEN 0 THEN 1
ELSE CAS1_LEVEL_STATUS
END
, CASE ISNULL(CUSCC2 , 0)
WHEN 0 THEN 1
ELSE CAS2_LEVEL_STATUS
END
, CASE ISNULL(CUSCC3 , 0)
WHEN 0 THEN 1
ELSE CAS3_LEVEL_STATUS
END
, CASE ISNULL(CUSCC4 , 0)
WHEN 0 THEN 1
ELSE CAS4_LEVEL_STATUS
END
, CASE ISNULL(CUSCC5 , 0)
WHEN 0 THEN 1
ELSE CAS5_LEVEL_STATUS
END
, _MYID
, LEVEL_STATUS
FROM
#MT mt
INNER JOIN [ABC].[dbo].lakalaka SSS
ON mt.mtvdev = SSS.InternalRefNumber
INNER JOIN dbo.fruits
ON _MYID = AssetInvID
SET @count = @@rowcount
WHILE @i <= @count
BEGIN
SELECT
@mygod = mygod
, @startingBox = startingBox
, @presentBox = presentBox
, @presentBox1 = CASE ISNULL(presentBox1 , 0)
WHEN 0 THEN 0
ELSE ( presentBox1 * 100 )
/ startBox1
END
, @presentBox2 = CASE ISNULL(presentBox2 , 0)
WHEN 0 THEN 0
ELSE ( presentBox2 * 100 )
/ startBox2
END
, @presentBox3 = CASE ISNULL(presentBox3 , 0)
WHEN 0 THEN 0
ELSE ( presentBox3 * 100 )
/ startBox3
END
, @presentBox4 = CASE ISNULL(presentBox4 , 0)
WHEN 0 THEN 0
ELSE ( presentBox4 * 100 )
/ startBox4
END
, @presentBox5 = CASE ISNULL(presentBox5 , 0)
WHEN 0 THEN 0
ELSE ( presentBox5 * 100 )
/ startBox5
END
-- ,@presentBox1=presentBox1,@presentBox2=presentBox2,@presentBox3=presentBox3,@presentBox4=presentBox4,@presentBox5=presentBox5
, @CAS1_LEVEL_STATUS = CAS1_LEVEL_STATUS
, @CAS2_LEVEL_STATUS = CAS2_LEVEL_STATUS
, @CAS3_LEVEL_STATUS = CAS3_LEVEL_STATUS
, @CAS4_LEVEL_STATUS = CAS4_LEVEL_STATUS
, @CAS5_LEVEL_STATUS = CAS5_LEVEL_STATUS
, @MY_ID = MY_ID
, @LEVEL_STATUS = LEVEL_STATUS
FROM
WHERE
id = @i
/* INSERT INTO [ABC].[dbo].[aa_box]
([startingBox]
,[presentBox]
,[mygod]
,[date])
select @startingBox,@presentBox,@mygod,getdate()
*/
IF @startingBox > 0
BEGIN
-- if (@presentBox / @startingBox ) <0.101 --and
--((@CAS1_LEVEL_STATUS =1 or @presentBox1 =0 ) and (@CAS2_LEVEL_STATUS =1 or @presentBox2 =0 ) and (@CAS3_LEVEL_STATUS =1 or @presentBox3 =0 ) and (@CAS4_LEVEL_STATUS =1 or @presentBox4 =0 ) and (@CAS5_LEVEL_STATUS =1 or @presentBox5 =0 ))
IF ( @presentBox / @startingBox ) < 0.031 --and
-- ((@CAS1_LEVEL_STATUS =1 or @presentBox1 =0 ) and (@CAS2_LEVEL_STATUS =1 or @presentBox2 =0 ) and (@CAS3_LEVEL_STATUS =1 or @presentBox3 =0 ) and (@CAS4_LEVEL_STATUS =1 or @presentBox4 =0 ) and (@CAS5_LEVEL_STATUS =1 or @presentBox5 =0 ))
BEGIN
IF ISNULL(@LEVEL_STATUS , 0) <> 2
BEGIN
--change by bhushan on 21-04-2011
SET @MY_ID = @MY_ID
EXEC [ABC].dbo.usp_SendMessageProcess 'ES2CL3' ,
@mygod
UPDATE
fruits
SET LEVEL_STATUS = 2
WHERE
_MYID = @MY_ID
--change by bhushan on 21-04-2011
END
END
-- else if (@presentBox / @startingBox ) <0.25 --and (@CAS1_LEVEL_STATUS =1 and @CAS2_LEVEL_STATUS =1 and @CAS3_LEVEL_STATUS =1 and @CAS4_LEVEL_STATUS =1 and @CAS5_LEVEL_STATUS =1 )
ELSE
IF ( @presentBox / @startingBox ) < 0.201 --and (@CAS1_LEVEL_STATUS =1 and @CAS2_LEVEL_STATUS =1 and @CAS3_LEVEL_STATUS =1 and @CAS4_LEVEL_STATUS =1 and @CAS5_LEVEL_STATUS =1 )
BEGIN
IF ISNULL(@LEVEL_STATUS , 0) <> 1
BEGIN
EXEC [ABC].dbo.usp_SendMessageProcess 'ES2CL2' ,
@mygod
UPDATE
fruits
SET LEVEL_STATUS = 1
WHERE
_MYID = @MY_ID
END
END
-- else if (@presentBox / @startingBox ) >0.25
ELSE
IF ( @presentBox / @startingBox ) > 0.2
BEGIN
IF ISNULL(@LEVEL_STATUS , 0) <> 0
BEGIN
EXEC [ABC].dbo.usp_SendMessageProcess 'ES2CL0' ,
@mygod
EXEC [ABC].dbo.usp_SendMessageProcess 'ES2CLL0' ,
@mygod
UPDATE
fruits
SET LEVEL_STATUS = 0
WHERE
_MYID = @MY_ID
END
END
END
SET @i = @i + 1
END
-- delete from pqr where id in(select id from #my)
UPDATE
MS
SET
MS.TXTDV1 = a.TXTDV1
, MS.TXTDV2 = a.TXTDV2
, MS.TXTDV3 = a.TXTDV3
, MS.TXTDV4 = a.TXTDV4
, MS._TCPIDL = a._TCPIDL
, MS._TCPBI = a._TCPBI
, MS._TCPBO = a._TCPBO
, MS.cuslc1 = a.cuslc1
, MS.cuslc2 = a.cuslc2
, MS.cuslc3 = a.cuslc3
, MS.cuslc4 = a.cuslc4
FROM
[ABC].[dbo].[MT_STATE] MS
INNER JOIN (
SELECT
MTVDEV
, TXTDV1
, TXTDV2
, TXTDV3
, TXTDV4
, _TCPIDL
, _TCPBI
, _TCPBO
, cuslc1
, cuslc2
, cuslc3
, cuslc4
FROM
[soap].Ala.dbo.MT_VIEW
WHERE
MTDELE <> 'D'
) a
ON MS.MTVDEV = a.MTVDEV
UPDATE
MS
SET
MS.TCSTCA = a.TCSTCA
, MS.TCCUCA = a.TCCUCA
FROM
[ABC].[dbo].[MT_STATE] MS
INNER JOIN (
SELECT
MTVDEV
, TCSTCA
, ( TCSTCA - TCCAOU ) AS TCCUCA
FROM
[soap].ala.dbo.MT_VIEW
) a
ON MS.MTVDEV = a.MTVDEV
UPDATE
ms
SET
StartBoxByCassette = startingBoxByCassete
, ms.CurrentBoxByCassette = m.CurrentBoxByCassete
FROM
[ABC].[dbo].[MT_STATE] MS
INNER JOIN #MT m
ON MS.MTVDEV = m.MTVDEV
END TRY
BEGIN CATCH
INSERT INTO
[aaaa_error]
(
[erroeid]
, [errormsg]
, lineNum
, SPName
)
SELECT
ERROR_NUMBER()
, ERROR_MESSAGE()
, ERROR_LINE()
, ERROR_PROCEDURE()
END CATCH
END
August 4, 2011 at 6:07 am
hey Ninja,
I automated the job as per the user requirement which calls this SP and this job runs on 24*7 Basis.
Once again thanks for your kind support..
August 4, 2011 at 6:14 am
srinath.vanama (8/4/2011)
hey Ninja,I automated the job as per the user requirement which calls this SP and this job runs on 24*7 Basis.
Once again thanks for your kind support..
So you're all done with this issue?
August 4, 2011 at 6:31 am
Some extent,
This job was created long back, previously this job runs fine and only on that particular day this kind of problem i faced. so later i told the user to convert this SP as simple query then i suggested them to create certain indices in the query and executed.
After doing all these things some extent am successful, as this job runs on 24 * 7 basis still am getting the same processes after querying 'sp_who2 active' but this time same results are lesser when compare to previous time.
You advised me to change the isolation level. so how should i change this SP into SNAPSHOT isolation level.
If possible, kindly help to fix this issue permanently..
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply