synapsevampire
Programmer
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 kai@informeddatadecisions.com
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 kai@informeddatadecisions.com