December 1, 2008 at 6:14 am
Hi chris by deploying your change the performance has improved from 1 min 33 secs to 56 sec. Can you pls give me some other suggestions to reduce it to half?
KPR
December 1, 2008 at 6:15 am
sorry to reduce it to one sec.
KPR
December 1, 2008 at 6:19 am
KPR (12/1/2008)
Hi Chris, The prev one is the Gilamonster thread I had tried. The below one is yours I had tried.
The suggestions aren't mutually exclusive.
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 6:20 am
KPR (12/1/2008)
Hi chris by deploying your change the performance has improved from 1 min 33 secs to 56 sec. Can you pls give me some other suggestions to reduce it to half?
Sure.
Chris Morris (12/1/2008)
Hello1. 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.
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 6:26 am
KPR (12/1/2008)
sorry to reduce it to one sec.
That may not be possible. You're doing a lot of work in there.
What are these?
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch)
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode)
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode)
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 6:33 am
These are the functions which takes the parameters from the tables and returns the selected rows only.actually these functions have improved the performance instead of using original tables .
KPR
December 1, 2008 at 6:34 am
GilaMonster (12/1/2008)
KPR (12/1/2008)
sorry to reduce it to one sec.That may not be possible. You're doing a lot of work in there.
What are these?
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch)
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode)
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode)
I'd agree with Gail here. If two of your tables are million-plus rows (and they're JOINed by a CASE), the best you're likely to get out of this will be in the region of 10-20 seconds. To get anywhere near this figure, you will need to answer the questions which have already been posted.
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:47 am
1)dbo.fn_ProfileWareHouse
------------------------------
ALTER FUNCTION dbo.fn_ProfileWareHouse
(
@ProfCode int,
@whcode varchar(10)
)
RETURNS @Warehouse TABLE (WHCode varchar(10) COLLATE DATABASE_DEFAULT not null)
AS
BEGIN
declare @AllWH bit
if (@whcode is not null)
begin
INSERT into @Warehouse values (@whcode)
end
else
begin
select @AllWH=AllWH from TRIPL_ForOffShore.dbo.TBL_Profiles where ProfCode=@ProfCode
if(@AllWH=1)
begin
INSERT @Warehouse (WHCode)
SELECT
WHOCode = WHCode
FROM TRIPL_ForOffShore.dbo.TBL_WAREHOUSE W WHERE W.Status=1
end
else if(@AllWH=0)
begin
INSERT @Warehouse (WHCode)
SELECT
WHOCode = WHCode
FROM TRIPL_ForOffShore.dbo.TBL_PROFWH W where W.ProfCode=@ProfCode AND W.WHCode
IN (select WHCode FROM TRIPL_ForOffShore.dbo.TBL_WAREHOUSE WHERE Status=1)
end
end
2)fn_ProfileLegalVechile
----------------------------------
ALTER FUNCTION dbo.fn_ProfileLegalVechile
(
@ProfCode int,
@LvCode int
)
RETURNS @LegalVechile TABLE (LvCode int not null)
AS
BEGIN
declare @AllLv bit
if (@LvCode is not null)
begin
INSERT into @LegalVechile values(@LvCode)
end
else
begin
select @AllLv=AllLV from TRIPL_ForOffShore.dbo.TBL_Profiles where ProfCode=@ProfCode
if(@AllLv=1)
begin
INSERT @LegalVechile (LvCode)
SELECT
LCode = LVCode
FROM TRIPL_ForOffShore.dbo.TBL_LEGALVEHICLE L
end
else if(@AllLv=0)
begin
INSERT @LegalVechile (LvCode)
SELECT LCode = LVCode
FROM TRIPL_ForOffShore.dbo.TBL_PROFLV L Where L.ProfCode=@ProfCode
end
end
RETURN
END
3)fn_ProfileCurrency
----------------------------------------
ALTER FUNCTION dbo.fn_ProfileCurrency
(
@ProfCode int,
@Ccycode varchar(5)
)
RETURNS @Currency TABLE (CcyCode char(3) COLLATE DATABASE_DEFAULT not null)
AS
BEGIN
declare @AllCy bit
if (@Ccycode is not null)
begin
INSERT into @Currency values(@Ccycode)
end
else
begin
select @AllCy=AllCcy from TRIPL_ForOffShore.dbo.TBL_Profiles where ProfCode=@ProfCode
if(@AllCy=1)
begin
INSERT @Currency (CcyCode)
SELECT
CcyCode = CcyCode
FROM TRIPL_ForOffShore.dbo.TBL_CURRENCY C
end
else if(@AllCy=0)
begin
INSERT @Currency (CcyCode)
SELECT
CcyCode = CcyCode
FROM TRIPL_ForOffShore.dbo.TBL_PROFCCY C where C.ProfCode=@ProfCode
end
end
RETURN
END
------------------
The tables used are having small data.
These functions have been employed by the superiors
KPR
December 1, 2008 at 7:02 am
Here's the relevant statements ripped from the sproc for easy testing:
[font="Courier New"]DECLARE @FromDate SmallDateTime,
@ToDate SmallDateTime,
@ProfCode smallint,
@Branch smallint = 0,
@WHCode VARCHAR(5) = NULL,
@CCYCode VARCHAR(5) = NULL,
@ContractFlag bit=0)
-- Assign values here
SET @FromDate =
SET @ToDate =
SET @ProfCode =
SET @Branch = 0
SET @WHCode = NULL
SET @CCYCode = NULL
SET @ContractFlag = 0
------------------------------------------------------------------------------
SET NOCOUNT ON
IF @Branch = 0 SET @Branch=NULL
--IF NOT (@ContractFlag = 0)
--RETURN 0 -- FAILURE
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
-- how many rows?
UPDATE #Trades SET Economic_Revenue = (Reval_Rates_MTM + Time_Decay_MTM + FX_Rates_MTM + New_Trades_MTM + Cancelled_Trades_MTM + Amended_Trades_MTM)
-- Extract from #Trades
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
-- how many rows?
-- Extract from TRIPL_ForOffShore.dbo.TBL_PROFILES
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
--RETURN 1 -- SUCCESS
[/font]
Try it out, ensure that the rowcount is what you would expect.
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 7:04 am
Ow! Multi-statement table-valued functions are an absolute pain and they tend to perform very badly with larger rowsets.
How many rows do those return?
This update is not necessary. You should be able to do that as part of the original insert.. select
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 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(A.PARALLELTIMTMUSD-A.TDMTMUSD AS numeric(28, 16)) + CAST(A.TDMTMUSD-A.PREIDMTMUSD AS numeric(28, 16)) +
CAST((A.FIMTMUSD-A.PARALLELTIMTMUSD) AS numeric(28, 16)) + CAST(CASE WHEN B.Action = 'NEW' THEN A.IDMTMUSD ELSE 0 END AS numeric(28, 16)) +
CAST(CASE WHEN B.Action = 'DELETE' AND P.Contractno IS NULL THEN A.IDMTMUSD - A.FIMTMUSD ELSE 0 END AS numeric(28, 16)) +
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)) AS [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
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 11:48 pm
Hi Chris,
' Select......... into #Trades' is returning 29,287 rows
and Select .......... from #Trades is returning 685 rows
KPR
December 2, 2008 at 4:07 am
Hi KPR
Examining the function which returns a warehouse code, dbo.fn_ProfileWareHouse:
the WHERE clause reads AND B.WHCode = M.WHCode where M is the alias for the table returned by the function.
If @WHCode is not null, then the same value of WHCode is returned, so the expression resolves to B.WHCode = @WHCode.
If @WHCode is null, then the function attempts to resolve value(s) for it based on @ProfCode. So it might be better to use...
AND B.WHCode IN (SELECT WHCode FROM dbo.fn_ProfileWareHouse(@ProfCode,@WHCode))
... as per your original rather than JOINing it.
If the function always returns only one row, then you should resolve @WHCode in a separate step prior to the main SELECT and simply use B.WHCode = @WHCode.
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 2, 2008 at 4:36 am
GilaMonster (12/1/2008)
KPR (12/1/2008)
sorry to reduce it to one sec.That may not be possible. You're doing a lot of work in there.
What are these?
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch)
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode)
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode)
These functions all appear to restrict rows in the same way...
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
...see previous post.
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 3, 2008 at 5:09 pm
Hi there
U can also create a var @ActionTable TABLE(id INT IDENTITY(1,1),Action VARCHAR(20))
insert the possible actions into this table
INSERT INTO @ActionTable (id,Action)
SELECT 'NEW'
UNION ALL
SELECT 'DELETE'
UNION ALL
...
or better u could create a physical not temporary table
Join this table with CONTACTHISTORY and do the CASE with the id column of this new table (it reduces the VARCHAR comparisons to 1 per row). Its not necessary to create a physical temporary table since it's a small one that works better in memory.
Hope this makes the procedure better
December 4, 2008 at 12:32 am
Skull Killer (12/3/2008)
Its not necessary to create a physical temporary table since it's a small one that works better in memory.
Just to clarify. Temp tables and table variables are both in memory and will only spill to disk if they get too large or the system comes under memory pressure.
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
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply