I have two stored procedures which runs to create reports, and however it takes around 14 hours to complete, which seems little odd to me. Any help will be appreciated
When we generate report it calls the stored procedure1 and stored procedure 1 calls stored procedure 2
the stored procedure 2 is taking around 12 hours to complete, which is as following
USE [OPICSTEST2]
GO
/****** Object: StoredProcedure [dbo].[CreateWorkTablesForWCASRep2] Script Date: 01/07/2016 09:16:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Check if SPSHSUM has at least 1 record in it
--if it doesnt drop the table as needs recreating
ALTER Procedure [dbo].[CreateWorkTablesForWCASRep2] as
--Add to SPSHUM table or build it from scratch THE SPSHSUM table
--And the table exists
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME =
'SPSHSUM')
BEGIN
DROP TABLE [dbo].[SPSHSUM]
END
--IF the table does not exist create it
CREATE TABLE [dbo].[SPSHSUM](
[ARCDATE] [datetime] NULL,
[BR] [char](2) NULL,
[CNO] [char](10) NULL,
[SECID] [char](20) NULL,
[CCY] [char](3) NULL,
[PORT] [char](4) NULL,
[COST] [char](10) NULL,
[SETTQTY] [decimal](38, 2) NULL,
[SETTPRICE] [decimal](38, 8) NULL,
[SETTAVGCOST] [decimal](38, 8) NULL,
[SETTQTYYTD] [decimal](38, 2) NULL,
[SETTPRICEYTD] [decimal](38, 8) NULL,
[SETTAVGCOSTYTD] [decimal](38, 8) NULL,
[REALIZEDGAIN] [int] NOT NULL,
[OS] [decimal](38, 8) NULL,
[OP] [decimal](38, 8) NULL,
[CM] [decimal](38, 8) NULL,
[PC] [decimal](38, 8) NULL,
[SC] [decimal](38, 8) NULL
)
--Create the @SPSHR Temp table that is required
DECLARE @SPSHR TABLE
(SETTDATE datetime,
BR char(2),
CNO char(10),
SECID char(20),
CCY char(3),
PORT char(4),
COST char(10),
PS char(1),
ORIGQTY decimal(19,6),
COSTAMT decimal(19,6),
REVDATE datetime,
PRODTYPE CHAR(2) ,
VERDATE datetime)
insert into @SPSHR
(
SETTDATE ,
BR ,
CNO ,
SECID,
CCY,
PORT,
COST,
PS ,
ORIGQTY ,
COSTAMT,
REVDATE ,
PRODTYPE ,
VERDATE
)
(
select
SETTDATE,
BR ,
CNO ,
SECID,
CCY ,
PORT ,
COST ,
PS ,
ORIGQTY ,
COSTAMT ,
REVDATE ,
PRODTYPE ,
VERDATE
from SPSH
WHERE PORT <>'CPTY'
AND VERDATE is not null and PRODTYPE ='EQ'
--hdb 28TH feb 08 REMOVING REVERSALS AND MAKING SP RECREATE EVERY DAY TO FIX PROBLEM OF REVERSALS INVALIDATING LOGIC
and REVDATE IS NULL
UNION ALL
select
SETTDATE,
BR,
WCUS.CNO,
SECID,
CCY,
PORT,
COST,
case DR when 'D' then 'S' else 'P' end ,
QTY,
0,
REVDATE,
PRODTYPE as PRODTYPE,
VERDATE as VERDATE
from RDFH inner join WCUS on WCUS.COSTCENT =RDFH.COST
WHERE PORT <>'CPTY' and QTY<>0
AND VERDATE is not null and PRODTYPE ='EQ'
)
--if the table does not exist run this sql to create table
--run this whatever to pick up new securities
--if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME =
--'SPSHSUM')
--begin
--insert the header records
insert into SPSHSUM
select
TMPTBL.ARCDATE as ARCDATE,
TMPTBL.BR as BR,
TMPTBL.CNO as CNO,
TMPTBL.SECID as SECID,
TMPTBL.CCY as CCY,
'CLIE' as PORT, --HDB hard coded to stop duplicates after change 25th Mar 08
TMPTBL.COST as COST,
TMPTBL.SETTQTY as SETTQTY,
--COSTAMT IS CHANGED TO SALECOST AND PURCHCOST DATE 16/05/2008
abs(case when TMPTBL.SETTQTY<0 then (TMPTBL.SALECOST)/TMPTBL.ORIGQTYSale else (TMPTBL.PURCHCOST)/TMPTBL.ORIGQTYPurch end )as SETTPRICE,
(case when TMPTBL.SETTQTY<0 then (TMPTBL.SALECOST)/TMPTBL.ORIGQTYSale else (TMPTBL.PURCHCOST)/TMPTBL.ORIGQTYPurch end )*TMPTBL.SETTQTY as SETTAVGCOST,
0 as SETTQTYYTD,
0 as SETTPRICEYTD,
0 as SETTAVGCOSTYTD,
--CHANGED TO 0 16/05/2008
--TMPTBL.SETTQTY*0 as SETTQTYYTD,
--abs(case when TMPTBL.SETTQTY<0 then (TMPTBL.COSTAMT)/TMPTBL.ORIGQTYSale else (TMPTBL.COSTAMT)/TMPTBL.ORIGQTYPurch end )*0 as SETTPRICEYTD,
--(case when TMPTBL.SETTQTY<0 then (TMPTBL.COSTAMT)/TMPTBL.ORIGQTYSale else (TMPTBL.COSTAMT)/TMPTBL.ORIGQTYPurch end )*TMPTBL.SETTQTY*0 as SETTAVGCOSTYTD,
--HDB 25thMar08
CASE when PORT = 'RFDF' THEN
0
ELSE
CASE WHEN (ABS(PURCHCOST)>0 AND ABS(SALECOST)>0) THEN
case when ABS(PURCHCOST)>ABS(SALECOST) then
(((PURCHCOST/ORIGQTYPurch)*ORIGQTYSale)-SALECOST)
else
case when ABS(PURCHCOST)=ABS(SALECOST) then
COSTAMT
ELSE
(((SALECOST/ORIGQTYSale)*ORIGQTYPurch)- PURCHCOST)
END
end
ELSE 0 END
END
as REALIZEDGAIN,
ORIGQTYSale as OS,
ORIGQTYPurch as OP,
COSTAMT as CM
,PURCHCOST as PC
,SALECOST as SC
from
(
select
SETTDATE as ARCDATE
,BR as BR
,CNO as CNO
,SECID as SECID
,CCY as CCY
--HDB 25thMar08
,PORT as PORT
,COST as COST
,sum(case PS when 'S' then (ORIGQTY) else (ORIGQTY*-1) end) as SETTQTY
,SUM(case PS WHEN 'S' then (ORIGQTY) else 0 end) as ORIGQTYPurch
,SUM(case PS WHEN 'P' then (ORIGQTY*-1) else 0 end) as ORIGQTYSale
,SUM(case when REVDATE is null
then (case PS WHEN 'S' then COSTAMT else 0 end)
else (case PS WHEN 'S' then (COSTAMT*-1) else 0 end)
end) as PurchCOST
,SUM(case when REVDATE is null
then (case PS WHEN 'P' then COSTAMT else 0 end )
else (case PS WHEN 'P' then (COSTAMT*-1) else 0 end)
end) as SaleCOST
,SUM(COSTAMT) as COSTAMT
from @SPSHR SPSHR
where
--only for days before the current branch processing date
SETTDATE<(SELECT BRANPRCDATE from BRPS where BRPS.BR =SPSHR.BR) and
PRODTYPE ='EQ' and
--Remove Port out of join HDB --25th Mar 08 HDB
SPSHR.SETTDATE=(select MIN(SETTDATE) from @SPSHR b where b.Br=SPSHR.BR and b.CNO =SPSHR.CNO and b.SECID =SPSHR.SECID and b.COST =SPSHR.COST)
and SPSHR.REVDATE is null
and SPSHR.VERDATE is not null
group BY SPSHR.SETTDATE,SPSHR.BR,SPSHR.CNO,SPSHR.SECID,SPSHR.CCY,SPSHR.COST,SPSHR.PORT
) as TMPTBL
where
not exists (select 'x' from SPSHSUM SS where TMPTBL.BR =SS.BR and TMPTBL.SECID = SS.SECID and TMPTBL.CNO =SS.CNO)
--where the record does not exist in SPSHSUM
--If the table exists run for each date in turn insert into
declare @tmpx as int
set @tmpx = -1
while (@@rowcount>0 or @tmpx=-1)
BEGIN
set @tmpx = 1
insert into SPSHSUM select
TMPTBL.ARCDATE as ARCDATE,
TMPTBL.BR as BR,
TMPTBL.CNO as CNO,
TMPTBL.SECID as SECID,
TMPTBL.CCY as CCY,
'CLIE' as PORT,
TMPTBL.COST as COST,
(sum(TMPTBL.SETTQTY)+
max(SPSHSUM.SETTQTY)) as SETTQTY,
max(abs(
case when SPSHSUM.SETTQTY<0 then
(case when (TMPTBL.SETTQTY + SPSHSUM.SETTQTY)<0 then (SPSHSUM.SETTAVGCOST + SALECOST)/(SPSHSUM.SETTQTY+TMPTBL.ORIGQTYSale)
else (PURCHCOST)/TMPTBL.ORIGQTYPurch end
)
else
(case when (TMPTBL.SETTQTY+ SPSHSUM.SETTQTY)<0 then (TMPTBL.SALECOST)/TMPTBL.ORIGQTYSale
else (SPSHSUM.SETTAVGCOST+TMPTBL.PURCHCOST)/(SPSHSUM.SETTQTY+TMPTBL.ORIGQTYPurch) end
)
end
))
as SETTPRICE,
max((TMPTBL.SETTQTY+SPSHSUM.SETTQTY)
*
abs(
case when SPSHSUM.SETTQTY<0 then
(case when (TMPTBL.SETTQTY + SPSHSUM.SETTQTY)<0 then (SPSHSUM.SETTAVGCOST + SALECOST)/(SPSHSUM.SETTQTY+TMPTBL.ORIGQTYSale)
else (PURCHCOST)/TMPTBL.ORIGQTYPurch end
)
else
(case when (TMPTBL.SETTQTY+ SPSHSUM.SETTQTY)<0 then (TMPTBL.SALECOST)/TMPTBL.ORIGQTYSale
else (SPSHSUM.SETTAVGCOST+TMPTBL.PURCHCOST)/(SPSHSUM.SETTQTY+TMPTBL.ORIGQTYPurch) end
)
end
))
as SETTAVGCOST,
max(SPSHSUM.SETTQTY) as SETTQTYYTD,
max(SPSHSUM.SETTAVGCOST) as SETTAVGCOSTYTD,
max(SPSHSUM.SETTPRICE) as SETTPRICEYTD,
--HDB 25thMar08
sum(CASE when TMPTBL.PORT = 'RFDF' THEN
0
ELSE
(case when SPSHSUM.SETTQTY<0 then
case when (ABS(ORIGQTYPurch)<abs(ORIGQTYSale+ SPSHSUM.SETTQTY)) then
--SPREAD
--Sale Cost
(((SETTAVGCOST+SALECOST) / (SPSHSUM.SETTQTY + ORIGQTYSale))
--Purchase COST
- (case when ORIGQTYPurch =0 then 0 else (TMPTBL.PurchCOST / ORIGQTYPurch) end))
--END SPREAD
--* Volume
* abs(ORIGQTYPurch)
else
--SPREAD
--Sale Cost
(((SETTAVGCOST+SALECOST) / (SPSHSUM.SETTQTY + ORIGQTYSale))
--Purchase COST
- (case when ORIGQTYPurch =0 then 0 else (TMPTBL.PurchCOST / ORIGQTYPurch) end))
--END SPREAD
* abs(ORIGQTYSale + SPSHSUM.SETTQTY)
end
else
case when (abs(ORIGQTYSale)<abs(ORIGQTYPurch + SPSHSUM.SETTQTY)) then
--SPREAD
--Sale Cost
(
(case when ORIGQTYSale=0 then 0 else (SALECOST / ORIGQTYSale) end)
--Purchase COST
- (case when (SPSHSUM.SETTQTY + ORIGQTYPurch) = 0 then 0 else ((SETTAVGCOST+PURCHCOST) / (SPSHSUM.SETTQTY + ORIGQTYPurch)) end)
)
--END SPREAD
--* Volume
* abs(ORIGQTYSale)
else
--SPREAD
--Sale Cost
(
(case when ORIGQTYSale=0 then 0 else (SALECOST / ORIGQTYSale) end)
--Purchase COST
- (case when (SPSHSUM.SETTQTY + ORIGQTYPurch) = 0 then 0 else ((SETTAVGCOST+PURCHCOST) / (SPSHSUM.SETTQTY + ORIGQTYPurch)) end)
)
--END SPREAD
--* Volume
* abs(SPSHSUM.SETTQTY+ORIGQTYPurch)
end
end
) END)
as REALIZEDGAIN,
sum(ORIGQTYSale),
sum(ORIGQTYPurch),
sum(COSTAMT) as CM
,sum(PURCHCOST) as PC
,sum(SALECOST) as SC
from
--Temp table inside sql
(
select
SETTDATE as ARCDATE
,BR as BR
,CNO as CNO
,SECID as SECID
,CCY as CCY
,PORT as PORT
,COST as COST
,sum(case when REVDATE is null
then (case PS when 'S' then ORIGQTY else (ORIGQTY*-1) end)
else (case PS when 'S' then (ORIGQTY*-1) else ORIGQTY end)
end) as SETTQTY
,SUM(case when REVDATE is null
then (case PS WHEN 'S' then ORIGQTY else 0 end)
else (case PS WHEN 'S' then (ORIGQTY*-1) else 0 end)
end) as ORIGQTYPurch
,SUM(case when REVDATE is null
then (case PS WHEN 'P' then (ORIGQTY*-1) else 0 end )
else (case PS WHEN 'P' then ORIGQTY else 0 end)
end) as ORIGQTYSale
,SUM(case when REVDATE is null
then (case PS WHEN 'S' then COSTAMT else 0 end)
else (case PS WHEN 'S' then (COSTAMT*-1) else 0 end)
end) as PurchCOST
,SUM(case when REVDATE is null
then (case PS WHEN 'P' then COSTAMT else 0 end )
else (case PS WHEN 'P' then (COSTAMT*-1) else 0 end)
end) as SaleCOST
,SUM(case when REVDATE is null
then COSTAMT
else (COSTAMT*-1)
END) as COSTAMT
from @SPSHR SPSHR
where
PRODTYPE ='EQ' and VERDATE is not null and
--only for days before the current branch processing date
SETTDATE<(SELECT BRANPRCDATE from BRPS where BRPS.BR =SPSHR.BR) and
--Remove Port out of join HDB --25th Mar 08 HDB
SETTDATE=(select MIN(SETTDATE) from @SPSHR b where b.Br=SPSHR.BR and b.CNO =SPSHR.CNO and b.SECID =SPSHR.SECID --and b.PORT =SPSHR.PORT
--Get the min settlement date that has not already been added to the new table ie bigger than the max
--Remove Port out of join HDB --25th Mar 08 HDB
and b.SETTDATE>(select max(ARCDATE) from SPSHSUM where b.Br=SPSHSUM.BR and b.CNO =SPSHSUM.CNO and b.SECID =SPSHSUM.SECID and b.COST =SPSHR.COST
)) OR
--deal with reversals
--Remove Port out of join HDB --25th Mar 08 HDB
SPSHR.REVDATE=(select MIN(SETTDATE) from @SPSHR b where b.Br=SPSHR.BR and b.CNO =SPSHR.CNO and b.SECID =SPSHR.SECID and b.COST =SPSHR.COST
--Get the min settlement date that has not already been added to the new table ie bigger than the max
and b.SETTDATE>(select max(ARCDATE) from SPSHSUM where b.Br=SPSHSUM.BR and b.CNO =SPSHSUM.CNO and b.SECID =SPSHSUM.SECID
))
group BY SETTDATE,BR,CNO,SECID,CCY,PORT,COST
) as TMPTBL, SPSHSUM
where
TMPTBL.Br=SPSHSUM.BR and TMPTBL.CNO =SPSHSUM.CNO and TMPTBL.SECID =SPSHSUM.SECID and
--Max record in the SPSHSUM table
--Remove Port out of join HDB --25th Mar 08 HDB
SPSHSUM.ARCDATE =( Select MAX(C.ARCDATE) from SPSHSUM C where C.BR=SPSHSUM.BR and C.CNO =SPSHSUM.CNO and C.SECID =SPSHSUM.SECID and C.COST =SPSHSUM.COST)
group by
TMPTBL.ARCDATE,
TMPTBL.BR,
TMPTBL.CNO,
TMPTBL.SECID,
TMPTBL.CCY,
TMPTBL.COST
END
