Stored Procedure slower than SQL in Query Analyzer???

[synapsevampire]

If I take the following and make an SP out of it (pass a SSNo parm and add it to the where clause), it takes several minutes to run, if I run it in the query analyzer (with the SQL provided below), it returns immediately, any thoughts?

I can even remove the where clause and it takes forever, but if I hardcode a SSNo in the where, it returns immediately.

declare @SSNo char(12)

set @ssno = '550512771'

SELECT dbo.AtsPerss.chrSSNo, SUM(dbo.AtsPersAcctTransacts.mnyPersonalPreTax) AS PersonalPretax, SUM(dbo.AtsPersAcctTransacts.mnyPersonalPostTax)
AS PersonalPosttax, SUM(dbo.AtsPersAcctTransacts.mnyYmcaPreTax) AS Expr3, SUM(dbo.AtsPersAcctTransacts.mnyYmcaPostTax) AS Expr4,
SUM(dbo.AtsPersAcctTransacts.mnyMonthlyComp) AS Expr5
FROM dbo.AtsPersAcctTransacts LEFT OUTER JOIN
dbo.AtsParticipants ON dbo.AtsPersAcctTransacts.guiPersID = dbo.AtsParticipants.guiPersID LEFT OUTER JOIN
dbo.AtsPersAcctTransactFunding ON
dbo.AtsPersAcctTransacts.guiUniqueID = dbo.AtsPersAcctTransactFunding.guiPersAcctTransactID LEFT OUTER JOIN
dbo.AtsPerss ON dbo.AtsPersAcctTransacts.guiPersID = dbo.AtsPerss.guiUniqueID
WHERE (dbo.AtsPersAcctTransactFunding.guiUniqueID IS NULL) AND (dbo.AtsPerss.chrSSNo = @SSNo) AND
(dbo.AtsParticipants.dtsEligibilityDate IS NULL)
GROUP BY dbo.AtsPerss.chrSSNo

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[sguslan]

Interesting ...
Have you tried declaring SSNo as Varchar instead of Char?

 

[synapsevampire]

I rewrote the joins and it runs quickly now.

I was more curious if others had experienced SQL which runs faster in Query Analyzer than a SP, especially by such a dramatic margin.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[susanma]

Hi,

It is not exactly the same but we are having performance issues running stored procedures in scheduled jobs. Run in Query Analyser it takes seconds, run overnight as part of a schedule and it takes 50 time longer. We tried it in SQL 2000 and this seemed to be OK, we have now found that there is a fix in SP4 for SQL Server 7.0 that may correct this type of problem.
Hope this is useful.

Sue

 

[synapsevampire]

Thanks Sue, but I'm on 2000.

I'm still perplexed and nervous about the experience, but so far everything else is running optimally.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com