December 1, 2008 at 4:56 am
Hi,
I have the following proc that is facing a performance problem.
ALTER PROCEDURE [dbo].[prc_SummaryRpt] (@FromDate SmallDateTime, @ToDate SmallDateTime, @ProfCode smallint, @Branch smallint=0, @WHCode varchar(5)=NULL, @CCYCode varchar(5)=NULL, @ContractFlag bit=0)
AS
DECLARE @ERROR INT
DECLARE @Profile Table (ProfCode int, ProfName varchar(50) COLLATE DATABASE_DEFAULT,Level0 varchar(20) COLLATE DATABASE_DEFAULT, Level1 varchar(20) COLLATE DATABASE_DEFAULT,Level2 varchar(20) COLLATE DATABASE_DEFAULT,Level3 varchar(20) COLLATE DATABASE_DEFAULT,Level4 varchar(20) COLLATE DATABASE_DEFAULT, Level5 varchar(20) COLLATE DATABASE_DEFAULT,
HCount int, soeid varchar(10) COLLATE DATABASE_DEFAULT, AdhocProfile bit, AllLV bit,AllWH bit, AllCcy bit, ProfileTypeCode varchar(20) COLLATE DATABASE_DEFAULT)
BEGIN
SET NOCOUNT ON
Create Table #Trades (EODDate smalldatetime, LVDesc varchar(50) COLLATE DATABASE_DEFAULT, WHCode varchar(10) COLLATE DATABASE_DEFAULT, CcyCode varchar(10) COLLATE DATABASE_DEFAULT,
Branch TinyInt, ContractNo varchar(200) COLLATE DATABASE_DEFAULT,
Daily_Accrual numeric(28, 16) ,
Trader varchar(200) COLLATE DATABASE_DEFAULT,
Time_Decay_MTM numeric(28, 16) ,
Reval_Rates_MTM numeric(28, 16) ,
FX_Rates_MTM numeric(28, 16) ,
New_Trades_MTM numeric(28, 16) ,
Cancelled_Trades_MTM numeric(28, 16) ,
Amended_Trades_MTM numeric(28, 16) ,
Economic_Revenue numeric(28, 16) , Economic_Revenue_FromHandoff numeric(28, 16) ,
Difference_with_Intellect numeric(28, 16),
tradeaction smallint )
BEGIN TRANSACTION
IF @Branch = 0 set @Branch=NULL
IF (@ContractFlag = 0)
BEGIN
INSERT INTO #Trades (EODDate, LVDesc, WHCode, CcyCode,
Daily_Accrual, Trader, Branch, ContractNo,
Time_Decay_MTM, Reval_Rates_MTM ,FX_Rates_MTM,
New_Trades_MTM, Cancelled_Trades_MTM,Amended_Trades_MTM ,Economic_Revenue,Difference_with_Intellect, Economic_Revenue_FromHandoff,tradeaction)
SELECT DISTINCT A.EODDate [EODDate], D.LVDesc [LV], RTRIM(C.WHCode) [WH],
B.CcyCode [Ccy],
O.DailyAccrual [Daily_Accrual], H.TraderName [Trader], A.Branch [Branch], A.ContractNo,
(A.TDMTMUSD-A.PREIDMTMUSD) [Time_Decay_MTM],(A.PARALLELTIMTMUSD-A.TDMTMUSD) [Reval_Rates_MTM],
(A.FIMTMUSD-A.PARALLELTIMTMUSD) [FX_Rates_MTM],
case when B.Action = 'NEW' then A.IDMTMUSD else 0 end [New_Trades_MTM],
case when B.Action = 'DELETE' and P.Contractno is null then A.IDMTMUSD - A.FIMTMUSD else 0 end [Cancelled_Trades_MTM],
case when B.Action = 'AMEND' then A.IDMTMUSD-0 when B.Action = 'BREAK' then A.IDMTMUSD-0
when B.Action = 'DELETE' and P.ContractNo is not null then 0-A.FIMTMUSD
else 0 end [Amended_Trades_MTM],
0 [Economic_Revenue], 0 [Difference_with_Intellect], ISNULL(O.ECONVAL,0) [Economic_Revenue_FromHandoff]
,A.TradeAction
FROM CONTRACTHISTORY B WITH (NOLOCK), TRIPL_ForOffShore.dbo.TBL_WAREHOUSE C WITH(NOLOCK),
TRIPL_ForOffShore.dbo.TBL_LEGALVEHICLE D WITH(NOLOCK),
TRIPL_ForOffShore.dbo.TBL_TRADER H WITH (NOLOCK),
CTCDETAILHISTORY O WITH(NOLOCK),
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch) L ,dbo.fn_ProfileWareHouse(@ProfCode,@WHCode) M,dbo.fn_ProfileCurrency(@ProfCode,@CCYCode) N, PAAREPORTHISTORY A WITH(NOLOCK)
left join dbo.fn_getPreversionContracts(@FromDate,@ToDate) P on A.ContractNo = P.Contractno and A.EodDate = P.EodDate
WHERE A.ContractNo = B.ContractNo
AND A.Branch = B.Branch
AND A.EODDate = B.EODDate
AND B.WHCode = C.WHCode
AND B.Branch = C.LVCode AND C.LVCode = D.LVCode
AND C.TraderCode = H.TraderCode
AND A.EODDATE = O.EODDATE
AND B.CCYCODE = O.CCYCODE
AND B.WHCODE = O.WHCODE
AND B.Branch = L.LVCode
AND B.WHCode = M.WHCode
AND B.CcyCode = N.CcyCode
AND A.EODDate BETWEEN @FromDate AND @ToDate
AND B.EODDate BETWEEN @FromDate AND @ToDate
AND O.EODDate BETWEEN @FromDate AND @ToDate
AND A.tradeaction = case when B.action = 'NEW' then 1
when B.action = 'DELETE' then 2
when B.action = 'AMEND' then 3
when B.action = 'BREAK' then 4
else 0 end
UPDATE A SET A.Economic_Revenue = (Reval_Rates_MTM + Time_Decay_MTM + FX_Rates_MTM + New_Trades_MTM + Cancelled_Trades_MTM + Amended_Trades_MTM)FROM #Trades A
SELECT Convert(varchar(15), EODDate,106) [EODDate], LVDesc [LV], WHCode [WH], CcyCode [Ccy],
Trader [Trader], isnull(Avg(Daily_Accrual),0) [Daily_Accrual],
ISNULL(Convert(numeric,SUM(Time_Decay_MTM)),0) [Time_Decay_MTM], ISNULL(Convert(numeric,SUM(Reval_Rates_MTM)),0) [Reval_Rates_MTM] ,
ISNULL(Convert(numeric,SUM(FX_Rates_MTM)),0) [FX_Rates_MTM], ISNULL(Convert(numeric,SUM(New_Trades_MTM)),0) [New_Trades_MTM],
ISNULL(Convert(numeric,SUM(Cancelled_Trades_MTM)),0) [Cancelled_Trades_MTM],ISNULL(Convert(numeric,SUM(Amended_Trades_MTM)),0) [Amended_Trades_MTM],
ISNULL(SUM(Economic_Revenue),0) [Economic_Revenue], [Economic_Revenue_FromHandoff],
[Difference_with_Intellect]
FROM #Trades
GROUP BY EODDate, LVDesc, WHCode , CcyCode ,
Trader , Difference_with_Intellect,[Economic_Revenue_FromHandoff]
ORDER BY EODDate, LVDesc, WHCode , CcyCode ,
Trader
SELECT ProfCode,ProfName,'EODDate' [Level0], Level1 [Level1],Level2 [Level2], Level3 [Level3], Level4 [Level4], Level5 [Level5],
HCount + 1 [HCount],soeid,AdhocProfile,AllLV, AllWH,AllCcy,ProfileTypeCode FROM TRIPL_ForOffShore.dbo.TBL_PROFILES where ProfCode=@ProfCode
END
Set @Error = @@Error
If @Error <> 0 GoTo ERROR_HANDLER
COMMIT TRANSACTION
SET NOCOUNT OFF
RETURN 0
ERROR_HANDLER:
EXECUTE prc_LogErrors @ERROR, NULL, @@PROCID
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 1 -- FAILURE
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------
There two large tables PAAreporthistory and contracthistory which are having millions of data.
---ContractHistory Table-------
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
ContractHistory dbo user table 2008-01-18 15:59:37.320
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ContractNo varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS
Branch tinyint no 1 3 0 no (n/a) (n/a) NULL
CcyCode varchar no 3 no no no SQL_Latin1_General_CP1_CI_AS
WHCode varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
StartDate smalldatetime no 4 no (n/a) (n/a) NULL
MaturityDate smalldatetime no 4 no (n/a) (n/a) NULL
DealDate smalldatetime no 4 no (n/a) (n/a) NULL
DealTime varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
EODDate smalldatetime no 4 no (n/a) (n/a) NULL
Action varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
SubProductId smallint no 2 5 0 yes (n/a) (n/a) NULL
Asset bit no 1 yes (n/a) (n/a) NULL
BaseNo bigint no 8 yes (n/a) (n/a) NULL
CustomerName varchar no 45 yes no no SQL_Latin1_General_CP1_CI_AS
DealerCode varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
Principal numeric no 13 28 16 yes (n/a) (n/a) NULL
IssueDate smalldatetime no 4 yes (n/a) (n/a) NULL
FirstStartDate smalldatetime no 4 yes (n/a) (n/a) NULL
FinalMaturityDate smalldatetime no 4 yes (n/a) (n/a) NULL
DealRate float no 8 53 NULL yes (n/a) (n/a) NULL
CoverRate float no 8 53 NULL yes (n/a) (n/a) NULL
InterestType varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
InterestSchedule varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
InterestMethod varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
AmortisationSchedule varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
AmortisationAmount numeric no 13 28 16 yes (n/a) (n/a) NULL
RateCurveId tinyint no 1 3 0 yes (n/a) (n/a) NULL
ContractType varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
AccruedDays varchar no 5 yes no no SQL_Latin1_General_CP1_CI_AS
CouponRate varchar no 15 yes no no SQL_Latin1_General_CP1_CI_AS
AuthorizeDate smalldatetime no 4 yes (n/a) (n/a) NULL
DailyAccrual numeric no 13 28 16 yes (n/a) (n/a) NULL
ToDateAccrual numeric no 13 28 16 yes (n/a) (n/a) NULL
ProjectedDailyAccrual numeric no 13 28 16 yes (n/a) (n/a) NULL
InterestAmount numeric no 13 28 16 yes (n/a) (n/a) NULL
LastUpdatedOn smalldatetime no 4 yes (n/a) (n/a) NULL
BondType varchar no 5 yes no no SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IX_ContractHistory nonclustered located on PRIMARY EODDate(-), Branch, WHCode, CcyCode, RateCurveId, ContractNo, Action
PK_ContractHistory clustered, unique, primary key located on PRIMARY ContractNo, Branch, CcyCode, WHCode, StartDate, MaturityDate, EODDate, Action
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK_ContractHistory (n/a) (n/a) (n/a) (n/a) ContractNo, Branch, CcyCode, WHCode, StartDate, MaturityDate, EODDate, Action
No foreign keys reference this table.
No views with schema binding reference this table.
---PaaReportHistory----
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
PAAReportHistory dbo user table 2008-01-18 15:59:37.663
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ContractNo varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS
Branch tinyint no 1 3 0 no (n/a) (n/a) NULL
EODDate smalldatetime no 4 no (n/a) (n/a) NULL
PreIDMTM numeric no 13 28 16 no (n/a) (n/a) NULL
PreIDMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
TDMTM numeric no 13 28 16 no (n/a) (n/a) NULL
TDMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
DV01MTM numeric no 13 28 16 no (n/a) (n/a) NULL
DV01MTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
ParallelDV01MTM numeric no 13 28 16 no (n/a) (n/a) NULL
ParallelDV01MTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
TIMTM numeric no 13 28 16 no (n/a) (n/a) NULL
TIMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
ParallelTIMTM numeric no 13 28 16 no (n/a) (n/a) NULL
ParallelTIMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
FIMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
IDMTM numeric no 13 28 16 no (n/a) (n/a) NULL
IDMTMUSD numeric no 13 28 16 no (n/a) (n/a) NULL
TradeAction tinyint no 1 3 0 no (n/a) (n/a) NULL
EconomicValue numeric no 13 28 16 yes (n/a) (n/a) NULL
Comments varchar no 255 yes no no SQL_Latin1_General_CP1_CI_AS
LastUpdatedOn smalldatetime no 4 no (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_ContractReportHistory clustered, unique, primary key located on PRIMARY ContractNo, Branch, EODDate, TradeAction
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT on column DV01MTM DF_PAAReportHistory_DV01MTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column DV01MTMUSD DF_PAAReportHistory_DV01MTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column FIMTMUSD DF_PAAReportHistory_FIMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column IDMTM DF_PAAReportHistory_IDMTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column IDMTMUSD DF_PAAReportHistory_IDMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column LastUpdatedOn DF_PAAReportHistory_LastUpdatedOn (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column ParallelDV01MTM DF_PAAReportHistory_ParallelDV01MTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column ParallelDV01MTMUSD DF_PAAReportHistory_ParallelDV01MTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column ParallelTIMTMUSD DF_PAAReportHistory_ParallelTIMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column PreIDMTM DF_PAAReportHistory_PreIDMTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column PreIDMTMUSD DF_PAAReportHistory_PreIDMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column TIMTM DF_PAAReportHistory_RIMTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column TIMTMUSD DF_PAAReportHistory_RIMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column TDMTM DF_PAAReportHistory_TDMTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column TDMTMUSD DF_PAAReportHistory_TDMTMUSD (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column ParallelTIMTM DF_PAAReportHistory_TIMTM (n/a) (n/a) (n/a) (n/a) (0.0)
DEFAULT on column TradeAction DF_PAAReportHistory_TradeAction (n/a) (n/a) (n/a) (n/a) (0)
PRIMARY KEY (clustered) PK_ContractReportHistory (n/a) (n/a) (n/a) (n/a) ContractNo, Branch, EODDate, TradeAction
No foreign keys reference this table.
No views with schema binding reference this table.
---------------------
----Execution Plan-----
Query (Insert statement for @Trades …….)
Cost : 78.70%
ContractHistory
Cost : 10%
Row Count : 46,921 for date range of 5 days
Estimated Row Count : 41
Number Of Executes : 3150
Index Seek
PAAReportHistory
Cost : 1%
Row Count : 46,921
Estimated Row Count : 1
Number Of Executes : 46,921
Clustured Index Seek
fn_PrevVersionContracts
Cost : 8%
Row Count : 5,535,243
Table scan (ContractHistory)
Query(UPDATE A SET A.Economic_Revenue = (Reval_Rates_MTM + Time_Decay_MTM + FX_Rates_MTM + New_Trades_MTM + Cancelled_Trades_MTM + Amended_Trades_MTM)FROM #Trades A
Cost : 9.56%
Query(SELECT Convert(varchar(15), EODDate,106) [EODDate], LVDesc [LV], WHCode [WH], CcyCode [Ccy],……)
Cost : 10.61%
----------================----------------
I am not providing the information of the other tables as those are small tables only.
Please give me any suggestions to improve the performance.
KPR
December 1, 2008 at 5:14 am
Please don't cross post. It just wastes peoples time and fragments replies. There's no need to start a new thread, there are several answers to your original one.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic611102-65-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2008 at 5:25 am
Sorry, I am actually fresher on this site. I thought sending threads to other sections will provide me more options.
KPR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply