December 4, 2008 at 12:41 am
Skull Killer (12/3/2008)
Hi thereU 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
Hi SK, I've been hanging on to this as a next step for the OP...
INTO #Trades
FROM CONTRACTHISTORY B WITH (NOLOCK)
...
...
INNER JOIN (SELECT 1 AS tradeaction, 'NEW' AS [action] UNION ALL
SELECT 2, 'DELETE' UNION ALL
SELECT 3, 'AMEND' UNION ALL
SELECT 4, 'BREAK' UNION ALL
SELECT 0, NULL) x ON x.[action] = B.[action]
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 = x.tradeaction
...because it can perform significantly better than the CASE construct which it replaces. Not sure how the ELSE 0 END fits in with this, however.
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 4, 2008 at 1:58 am
Hi Gila Monster
That's true. But creating a temp table, even with no data, costs more than a table variable. That's why I justified the choice that way.
December 4, 2008 at 2:13 am
Hi
I remembered, u can also replace the table variable I suggested before or the way u did it in the middle of the SELECT statement with a Common Table Expression (cte) like this
;WITH actionCTE as (SELECT 1 as ID,'NEW' as Action
UNION ALL
SELECT 2, 'DELETE'
UNION ALL
...)
--place your query (SELECT, INSERT, UPDATE or DELETE) here and use the actionCTE as a table
I don't know if it performs faster than the table variable, but since it removes a INSERT statement, it should be
December 4, 2008 at 8:02 am
Skull Killer (12/4/2008)
That's true. But creating a temp table, even with no data, costs more than a table variable.
Why?
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 4, 2008 at 8:44 am
Because it has to "reserve" and then destroy the structure in the temp database and it does the COMMITs.
Temp variables only get to disk if they are big, and since we can't do indexing in table variables they only better work better for small or shortuse rowsets. By shortuse I mean that they are not that small but doesn't need indexes nor a large rowsize. You can see this post Table types. I think there is also some articles here in sqlservercentral.
December 4, 2008 at 9:41 am
Skull Killer (12/4/2008)
Because it has to "reserve" and then destroy the structure in the temp database and it does the COMMITs.
Same with a table variable. Main difference is that table variables don't participate in transactions and temp tables do.
Temp variables only get to disk if they are big
Same with a temp table, though they both are recorded in TempDB's system tables and both get 'reserved' space in tempDB. SQL will try to keep them in memory as much as possible, because it knows that the data is very likely to be reused very soon. In case they do have to go to disk, they need entries in TempDB.
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
Also this one, though it is my blog and hence can't be used to prove my position
http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/
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 4, 2008 at 12:48 pm
since the transactions and locks does not occur in table variables, my point still stands for small tables like the one in this procedure. anyway, The cte solution works for this procedure since it is needed only in one statement. And tanks for the clarification in the difference between variable and temp tables.
In the first post about temp tables I also said it would be good to make a permanent table with the same structure. It would be good for 2 reasons: it would permanently register you actions (u could put a foreign key in the contacthistory table) and it would allow for a change in the contacthistory table replacing the type of the action field to integer, reducing the row size of the table. Since it's a history table you could, if that is an issue, create the new table as actionHistory and do the necessary things to maintain it, or adding a field to the table marking the deleted actions instead of deleting it.
Since I have been at work I was just shouting out some solutions/ideias I remembered, I will now take a better look at the procedure and reformulate it as I analyse it.
please give some feedback on the suggestion I made here about the permanent table.
December 4, 2008 at 5:32 pm
Hi
after "fast analyzing" the original procedure I achieved the statement. I didn't look into the other posts, so u should apply the same stuff u applied before, at least the things that worked about the functions. All the posts I read about it seems logic. I didn't alter all the things I think u should do since U can check it better if U do it yourself. Since I am not used with the tables and their relations I would probably do it wrong. So use the code i will post in the end and try the things I talk about in the comments. Let me know if it helped.
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 INTEGER
/* I don't see u using this in the procedure so
DECLARE @Profile TABLE (
ProfCode INTEGER,
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 INTEGER,
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
WITH cteActions AS (
SELECT ID = 1, [Action] = 'NEW'
UNION ALL
SELECT ID = 2, [Action] = 'DELETE'
UNION ALL
SELECT ID = 3, [Action] = 'AMEND'
UNION ALL
SELECT ID = 4, [Action] = 'BREAK'
)
/* About CTEs : u can only use the cte in the next insert, update, delete, select statement
u can use a previous declared cte in the next cte like this
WITH Cte1 as (...),
Cte1 as (select ... from cte1, ...
SELECT*/
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
EODDate = A.EODDate,
LV = D.LVDesc,
WH = RTRIM(C.WHCode),
Ccy = B.CcyCode,
Daily_Accrual = O.DailyAccrual,
Trader = H.TraderName,
Branch = A.Branch,
ContactNo = A.ContractNo,
Time_Decay_MTM = A.TDMTMUSD-A.PREIDMTMUSD,
Reval_Rates_MTM = A.PARALLELTIMTMUSD-A.TDMTMUSD,
FX_Rates_MTM = A.FIMTMUSD-A.PARALLELTIMTMUSD,
New_Trades_MTM = CASE
WHEN cteA.ID = 1 THEN A.IDMTMUSD
ELSE 0
END,
Cancelled_Trades_MTM = CASE
WHEN cteA.ID = 2 and P.Contractno is null THEN A.IDMTMUSD - A.FIMTMUSD
ELSE 0
END,
Amended_Trades_MTM = CASE
WHEN cteA.ID between 3 AND 4 THEN A.IDMTMUSD-0
WHEN cteA.ID = 2 and P.ContractNo is not null THEN 0-A.FIMTMUSD
ELSE 0
END,
--Since the select set the values sequentially in the order it appears, this sould work
Economic_Revenue = Reval_Rates_MTM +
Time_Decay_MTM +
FX_Rates_MTM +
New_Trades_MTM +
Cancelled_Trades_MTM +
Amended_Trades_MTM,
Difference_with_Intellect = 0,
Economic_Revenue_FromHandoff = ISNULL(O.ECONVAL,0),
TradeAction = A.TradeAction
FROM CONTRACTHISTORY B (NOLOCK) LEFT JOIN cteActions cteA on b.Action=cteA.Action
/*If the previous statement doesn't work u can put a "," after the cte and put another CTE as
select b.*,ActionID=cteA.id from CONTRACTHISTORY B (NOLOCK) LEFT JOIN cteActions cteA on b.Action=cteA.Action*/,
TRIPL_ForOffShore.dbo.TBL_WAREHOUSE C (NOLOCK), /*u should link every tables by inner join,
put the left join on a cte (it ensures that it work every time)*/
TRIPL_ForOffShore.dbo.TBL_LEGALVEHICLE D (NOLOCK),
TRIPL_ForOffShore.dbo.TBL_TRADER H (NOLOCK),
CTCDETAILHISTORY O (NOLOCK),
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch) L,/*If this is just to return a partial table/value U can put it in a CTE too
unless u use it for other stuff 2 since it permits a centralization of the code*/
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode) M,
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode) N,
PAAREPORTHISTORY A (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 = ISNULL(cteA.ID,0)
/*if u want u can put the previous statement in a CTE, removing temp tables and table variable from all the procedure*/
SELECT EODDate = CONVERT(varchar(15), EODDate,106),
LV = LVDesc,
WH = WHCode,
Ccy = CcyCode,
Trader = Trader,
Daily_Accrual = ISNULL(AVG(Daily_Accrual),0),
Time_Decay_MTM = ISNULL(CONVERT(NUMERIC,SUM(Time_Decay_MTM)),0),
Reval_Rates_MTM = ISNULL(CONVERT(NUMERIC,SUM(Reval_Rates_MTM)),0),
FX_Rates_MTM = ISNULL(CONVERT(NUMERIC,SUM(FX_Rates_MTM)),0),
New_Trades_MTM = ISNULL(CONVERT(NUMERIC,SUM(New_Trades_MTM)),0),
Cancelled_Trades_MTM = ISNULL(CONVERT(NUMERIC,SUM(Cancelled_Trades_MTM)),0),
Amended_Trades_MTM = ISNULL(CONVERT(NUMERIC,SUM(Amended_Trades_MTM)),0),
Economic_Revenue = ISNULL(SUM(Economic_Revenue),0),
Economic_Revenue_FromHandoff = Economic_Revenue_FromHandoff,
Difference_with_Intellect = 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 = ProfCode,
ProfName = ProfName,
Level0 = 'EODDate',
Level1 = Level1,
Level2 = Level2,
Level3 = Level3,
Level4 = Level4,
Level5 = Level5,
HCount = HCount + 1,
soeid = soeid,
AdhocProfile = AdhocProfile,
AllLV = AllLV,
AllWH = AllWH,
AllCcy = AllCcy,
ProfileTypeCode = 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
well it seems i discovered another way to do the 😉 . It means ) and not ; )
I did a little reformat, u copy what u think best for yourself but always format the code. I exaggerated a little in the [Field Name] = [Field Name].
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply