December 1, 2008 at 3:11 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 3:42 am
Hello
1. This sproc would probably run more quickly if you remove all of the statements relating to transactions - you don't need it.
You're SELECTING data from live tables into a temp table, then updating the temp table, then SELECTing from it. In the absence of any statements which might update the live data, there's nothing to roll back/commit.
2. You could populate the temp table in one statement:
SELECT...INTO #temp
FROM...
3. If the sproc is still slow, check out what the table-valued functions are doing and consider replacing them with joins to tables.
Finally, it probably won't make much difference to performance, but consider using proper join syntax throughout - it will make your complex query more readable.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2008 at 4:08 am
If you run the contents of the proc in query analyser, of the insert, update and select, which one is the slowest?
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 4:17 am
Thanks for the replies.
Actually 'select' is the slowest.
KPR
December 1, 2008 at 4:26 am
Hi Chris,
Thanks for your response. I tried with your scenarios(Select ...into #trades).
Now the execution plan has changed. The insert( with select) was taking 79% cost, but now it has been reduced to 15%.
But the 'select ....from #trdes' is consuming 73 %. ('sort' itself is consuming 40 %).
KPR
December 1, 2008 at 4:52 am
You may want to consider an index on the temp table
I wouldn't normally suggest a wide clustered index, but since this is a temp table, perhaps a clustered index on EODDate, LVDesc, WHCode , CcyCode ,
Trader , Difference_with_Intellect,[Economic_Revenue_FromHandoff] would make things much better.
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:00 am
Hi Gail,
Can you pls give me the syntax for creating index on temp in the proc?
KPR
December 1, 2008 at 5:07 am
KPR (12/1/2008)
Hi Chris,Thanks for your response. I tried with your scenarios(Select ...into #trades).
Now the execution plan has changed. The insert( with select) was taking 79% cost, but now it has been reduced to 15%.
But the 'select ....from #trdes' is consuming 73 %. ('sort' itself is consuming 40 %).
This is your original select with a little reformatting. How many rows are returned?
[font="Courier New"]IF (@ContractFlag = 0)
BEGIN
SELECT DISTINCT A.EODDate [EODDate], D.LVDesc [LVDesc], RTRIM(C.WHCode) [WHCode], B.CcyCode [CcyCode],
O.DailyAccrual [Daily_Accrual], H.TraderName [Trader], A.Branch [Branch], A.ContractNo,
CAST(A.TDMTMUSD-A.PREIDMTMUSD AS numeric(28, 16)) [Time_Decay_MTM],
CAST(A.PARALLELTIMTMUSD-A.TDMTMUSD AS numeric(28, 16)) [Reval_Rates_MTM],
CAST((A.FIMTMUSD-A.PARALLELTIMTMUSD) AS numeric(28, 16)) [FX_Rates_MTM],
CAST(CASE WHEN B.Action = 'NEW' THEN A.IDMTMUSD ELSE 0 END AS numeric(28, 16)) [New_Trades_MTM],
CAST(CASE WHEN B.Action = 'DELETE' AND P.Contractno IS NULL THEN A.IDMTMUSD - A.FIMTMUSD ELSE 0 END AS numeric(28, 16)) [Cancelled_Trades_MTM],
CAST(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 AS numeric(28, 16)) [Amended_Trades_MTM],
CAST(0 AS numeric(28, 16)) [Economic_Revenue],
CAST(0 AS numeric(28, 16)) [Difference_with_Intellect],
CAST(ISNULL(O.ECONVAL,0) AS numeric(28, 16)) [Economic_Revenue_FromHandoff],
A.TradeAction
INTO #Trades
FROM CONTRACTHISTORY B WITH (NOLOCK),
INNER JOIN TRIPL_ForOffShore.dbo.TBL_WAREHOUSE C WITH(NOLOCK) ON B.WHCode = C.WHCode AND B.Branch = C.LVCode
INNER JOIN TRIPL_ForOffShore.dbo.TBL_LEGALVEHICLE D WITH(NOLOCK) ON C.LVCode = D.LVCode
INNER JOIN TRIPL_ForOffShore.dbo.TBL_TRADER H WITH (NOLOCK) ON C.TraderCode = H.TraderCode
INNER JOIN CTCDETAILHISTORY O WITH(NOLOCK) ON B.CCYCODE = O.CCYCODE AND B.WHCODE = O.WHCODE
INNER JOIN dbo.fn_ProfileLegalVechile(@ProfCode,@Branch) L ON B.Branch = L.LVCode
INNER JOIN dbo.fn_ProfileWareHouse(@ProfCode,@WHCode) M ON B.WHCode = M.WHCode
INNER JOIN dbo.fn_ProfileCurrency(@ProfCode,@CCYCode) N ON B.CcyCode = N.CcyCode
INNER JOIN PAAREPORTHISTORY A WITH(NOLOCK) ON A.ContractNo = B.ContractNo AND A.Branch = B.Branch AND A.EODDate = B.EODDate AND A.EODDATE = O.EODDATE
AND A.tradeaction = CASE B.action WHEN 'NEW' THEN 1 WHEN 'DELETE' THEN 2 WHEN 'AMEND' THEN 3 WHEN 'BREAK' THEN 4 ELSE 0 END
LEFT JOIN dbo.fn_getPreversionContracts(@FromDate,@ToDate) P ON A.ContractNo = P.Contractno AND A.EodDate = P.EodDate
WHERE B.EODDate BETWEEN @FromDate AND @ToDate
--AND A.EODDate BETWEEN @FromDate AND @ToDate <-- already in
--AND O.EODDate BETWEEN @FromDate AND @ToDate <-- join criteria
END[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2008 at 5:23 am
KPR (12/1/2008)
Hi Gail,Can you pls give me the syntax for creating index on temp in the proc?
Same syntax as for a regular table
CREATE [UNIQUE] [CLUSTERED] INDEX < Index name > ON < Table name > (< Column List > )
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:24 am
Earlier Proc was returning 279 rows in 1 min 25 secs for 5 days.
Now it is returning 685 rows in 56 secs.
KPR
December 1, 2008 at 5:33 am
KPR (12/1/2008)
Earlier Proc was returning 279 rows in 1 min 25 secs for 5 days.Now it is returning 685 rows in 56 secs.
How are you testing, KPI? If you're amending & running whole procs, then you will find it much much easier to cut out the section you're amending / testing and paste it into QA / SMS. We'll help if you're unsure how to do this - just ask.
When you say the original proc was returning 279 rows for 5 days, does that include right now?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2008 at 5:39 am
Please ignore the previous thread
Proc is returning 685 rows only for 5 days
279 was for 1 day
KPR
December 1, 2008 at 5:49 am
I have tried like this but after running 2 to 3 times Proc is executing in 1 Min 33 secs.
alter PROCEDURE [dbo].[prc_SummaryRptkk] (@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 )
CREATE CLUSTERED INDEX IX_1 on #Trades (EODDate, LVDesc, WHCode , CcyCode ,Trader , Difference_with_Intellect,[Economic_Revenue_FromHandoff])
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
KPR
December 1, 2008 at 5:57 am
KPR (12/1/2008)
I have tried like this but after running 2 to 3 times Proc is executing in 1 Min 33 secs.
I'm sure it is - it looks like the original proc to me.
How are you testing, KPI? If you're amending & running whole procs, then you will find it much much easier to cut out the section you're amending / testing and paste it into QA / SMS. We'll help if you're unsure how to do this - just ask.
When you say the original proc was returning 279 rows for 5 days, does that include right now?
In other words, how many rows are you expecting in your output and how many rows are returned by the query I posted earlier?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2008 at 6:03 am
Hi Chris, The prev one is the Gilamonster thread I had tried. The below one is yours I had tried.
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
SELECT DISTINCT A.EODDate [EODDate], D.LVDesc [LVDesc], RTRIM(C.WHCode) [WHCode], B.CcyCode [CcyCode],
O.DailyAccrual [Daily_Accrual], H.TraderName [Trader], A.Branch [Branch], A.ContractNo,
CAST(A.TDMTMUSD-A.PREIDMTMUSD AS numeric(28, 16)) [Time_Decay_MTM],
CAST(A.PARALLELTIMTMUSD-A.TDMTMUSD AS numeric(28, 16)) [Reval_Rates_MTM],
CAST((A.FIMTMUSD-A.PARALLELTIMTMUSD) AS numeric(28, 16)) [FX_Rates_MTM],
CAST(CASE WHEN B.Action = 'NEW' THEN A.IDMTMUSD ELSE 0 END AS numeric(28, 16)) [New_Trades_MTM],
CAST(CASE WHEN B.Action = 'DELETE' AND P.Contractno IS NULL THEN A.IDMTMUSD - A.FIMTMUSD ELSE 0 END AS numeric(28, 16)) [Cancelled_Trades_MTM],
CAST(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 AS numeric(28, 16)) [Amended_Trades_MTM],
CAST(0 AS numeric(28, 16)) [Economic_Revenue],
CAST(0 AS numeric(28, 16)) [Difference_with_Intellect],
CAST(ISNULL(O.ECONVAL,0) AS numeric(28, 16)) [Economic_Revenue_FromHandoff],
A.TradeAction
INTO #Trades
FROM CONTRACTHISTORY B WITH (NOLOCK)
INNER JOIN TRIPL_ForOffShore.dbo.TBL_WAREHOUSE C WITH(NOLOCK) ON B.WHCode = C.WHCode AND B.Branch = C.LVCode
INNER JOIN TRIPL_ForOffShore.dbo.TBL_LEGALVEHICLE D WITH(NOLOCK) ON C.LVCode = D.LVCode
INNER JOIN TRIPL_ForOffShore.dbo.TBL_TRADER H WITH (NOLOCK) ON C.TraderCode = H.TraderCode
INNER JOIN CTCDETAILHISTORY O WITH(NOLOCK) ON B.CCYCODE = O.CCYCODE AND B.WHCODE = O.WHCODE
INNER JOIN dbo.fn_ProfileLegalVechile(@ProfCode,@Branch) L ON B.Branch = L.LVCode
INNER JOIN dbo.fn_ProfileWareHouse(@ProfCode,@WHCode) M ON B.WHCode = M.WHCode
INNER JOIN dbo.fn_ProfileCurrency(@ProfCode,@CCYCode) N ON B.CcyCode = N.CcyCode
INNER JOIN PAAREPORTHISTORY A WITH(NOLOCK) ON A.ContractNo = B.ContractNo AND A.Branch = B.Branch AND A.EODDate = B.EODDate AND A.EODDATE = O.EODDATE
AND A.tradeaction = CASE B.action WHEN 'NEW' THEN 1 WHEN 'DELETE' THEN 2 WHEN 'AMEND' THEN 3 WHEN 'BREAK' THEN 4 ELSE 0 END
LEFT JOIN dbo.fn_getPreversionContracts(@FromDate,@ToDate) P ON A.ContractNo = P.Contractno AND A.EodDate = P.EodDate
WHERE B.EODDate BETWEEN @FromDate AND @ToDate
--AND A.EODDate BETWEEN @FromDate AND @ToDate <-- already in
--AND O.EODDate BETWEEN @FromDate AND @ToDate <-- join criteria
-- 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
KPR
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply