I need some help with this. I have a stored procedure which if I run as :
exec test_sptma34 @asofdate = '10/31/2007', @analysisid = 'TVE-2004-10'
would NOT Complete at all and goes in a loop looks like (waited for 12 mins and then cancelled). Whereas If I run the same statements which are in SP then I get around 41 rows and that too within 1 to 2 seconds. Am I missing anything here?
STORED PROCEDURE:
==================
Expand|Select|Wrap|Line Numbers
- CREATE PROCEDURE test_spTMA34
- (@asofdate DATETIME,
- @analysisid VARCHAR(50))
- AS
- SET NOCOUNT ON
- BEGIN
- declare @ShiftData TABLE
- ([BP Shift] float,
- [Elasticity] float,
- [Convexity] float)
- insert into @ShiftData ([BP Shift], Elasticity, Convexity)
- SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) as float) AS '[BP Shift]',
- SUM(S.Weight*D1.value) AS Elasticity,
- SUM(S.Weight*D2.Value) AS Convexity
- FROM tblData D1
- JOIN tblData D2 on D1.asofdate = D2.asofdate and D1.ticker = D2.ticker and Right(D1.datatype,LEN(D1.DataType) - 10) = Right(D2.datatype, LEN(D2.DataType) - 9)
- JOIN tblPortfolio P on P.ticker = D1.ticker
- JOIN tblSBMIWeights S on S.ticker = D1.ticker
- WHERE
- P.portfolio = 'SBMI-TBA'
- and P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' and asofdate <= @asofdate)
- and S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@asofdate)
- and D1.datatype like 'Elasticity@%' AND D1.DataSource = @analysisid
- and D2.datatype like 'Convexity@%' AND D2.DataSource = @analysisid
- and D1.asofdate = @asofdate
- GROUP BY D1.datatype, D2.datatype
- insert into @ShiftData ([BP Shift], Elasticity, Convexity)
- SELECT 0 AS '[BP Shift]',
- SUM(S.Weight*D1.value) AS Elasticity,
- SUM(S.Weight*D2.Value) AS Convexity
- FROM tblData D1
- JOIN tblData D2 on D1.asofdate = D2.asofdate and D1.ticker = D2.ticker and Right(D1.datatype,LEN(D1.DataType) - 10) = Right(D2.datatype, LEN(D2.DataType) - 9)
- JOIN tblPortfolio P on P.ticker = D1.ticker
- JOIN tblSBMIWeights S on S.ticker = D1.ticker
- WHERE
- P.portfolio = 'SBMI-TBA'
- and P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' and asofdate <= @asofdate)
- and S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@asofdate)
- and D1.datatype = 'Elasticity' AND D1.DataSource = @analysisid
- and D2.datatype = 'Convexity' AND D2.DataSource = @analysisid
- and D1.asofdate = @asofdate
- GROUP BY D1.datatype, D2.datatype
- SELECT * FROM @ShiftData
- ORDER BY [BP Shift]
- END
================================================== =======
SAME SQL STATEMENTS (this runs within 1 to 2 secs and returs around 41 rows which is right)
================================================== =====
Expand|Select|Wrap|Line Numbers
- SET NOCOUNT ON
- BEGIN
- declare @asofdate DATETIME,
- @analysisid VARCHAR(50)
- SELECT @asofdate = '10/31/2007'
- SELECT @analysisid = 'TVE-2004-10'
- declare @ShiftData TABLE
- ([BP Shift] float,
- [Elasticity] float,
- [Convexity] float)
- insert into @ShiftData ([BP Shift], Elasticity, Convexity)
- SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) as float) AS '[BP Shift]',
- SUM(S.Weight*D1.value) AS Elasticity,
- SUM(S.Weight*D2.Value) AS Convexity
- FROM tblData D1
- JOIN tblData D2 on D1.asofdate = D2.asofdate and D1.ticker = D2.ticker and Right(D1.datatype,LEN(D1.DataType) - 10) = Right(D2.datatype, LEN(D2.DataType) - 9)
- JOIN tblPortfolio P on P.ticker = D1.ticker
- JOIN tblSBMIWeights S on S.ticker = D1.ticker
- WHERE
- P.portfolio = 'SBMI-TBA'
- and P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' and asofdate <= @asofdate)
- and S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@asofdate)
- and D1.datatype like 'Elasticity@%' AND D1.DataSource = @analysisid
- and D2.datatype like 'Convexity@%' AND D2.DataSource = @analysisid
- and D1.asofdate = @asofdate
- GROUP BY D1.datatype, D2.datatype
- insert into @ShiftData ([BP Shift], Elasticity, Convexity)
- SELECT 0 AS '[BP Shift]',
- SUM(S.Weight*D1.value) AS Elasticity,
- SUM(S.Weight*D2.Value) AS Convexity
- FROM tblData D1
- JOIN tblData D2 on D1.asofdate = D2.asofdate and D1.ticker = D2.ticker and Right(D1.datatype,LEN(D1.DataType) - 10) = Right(D2.datatype, LEN(D2.DataType) - 9)
- JOIN tblPortfolio P on P.ticker = D1.ticker
- JOIN tblSBMIWeights S on S.ticker = D1.ticker
- WHERE
- P.portfolio = 'SBMI-TBA'
- and P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' and asofdate <= @asofdate)
- and S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@asofdate)
- and D1.datatype = 'Elasticity' AND D1.DataSource = @analysisid
- and D2.datatype = 'Convexity' AND D2.DataSource = @analysisid
- and D1.asofdate = @asofdate
- GROUP BY D1.datatype, D2.datatype
- SELECT * FROM @ShiftData
- ORDER BY [BP Shift]
- END