Running SP versus SQL Statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running SP versus SQL Statements

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-11-01 : 13:35:11

Folks:

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:
==================

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)
=======================================================

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
====================

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-01 : 13:42:57

That sounds like a parameter sniffing issue. Drop/recreate the proc and try again.Also, another way to get around this is to assign the values to local variables. create 2 more local variables and assign the values from the parameters in the proc definition to these variables and use them across the proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources