SP - HELP 2 SLOW - SQL Server Forums

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)

SP - HELP 2 SLOW

Author

Topic

dclinton22
Starting Member

1 Post

Posted - 2005-08-18 : 17:04:55

--I'm having a problem with this proc, takes too long :) each count takes 5 seconds, the rest takes less than 1. Does anyone know a better way of doing this??? Thanks...
CREATE PROCEDURE SP_LIST_ENROLLMENTS
(
@int_activity_class_id INT
)

as
DECLARE @ENROLL_COUNT INT
DECLARE @WAITLIST_COUNT INT
DECLARE @CONFIRM INT
DECLARE @PENDING INT
DECLARE @WITHDRAWN INT
DECLARE @APPROVED INT
DECLARE @DELETED INT

SET @ENROLL_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID NOT IN (4,5,6))
SET @WAITLIST_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 5)
SET @CONFIRM = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 3)
SET @PENDING = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 2)
SET @WITHDRAWN = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 4)
SET @APPROVED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 1)
SET @DELETED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_id AND ENROLLMENT_STATUS_ID = 6)

Select A.ACTIVITY_NAME,
A.DESCR,
C.CUSTOMER_ID,
C.CUSTOMER_NAME,
cast(A.PROVIDER_CODE as varchar) + '-' + cast(FY.FISCAL_YY as varchar) + '-' + cast(AC.CEU_ACTIVITY_CODE as varchar) + '-' + cast(isnull(ax.activity_seq_number,'XXX') as varchar) as ACTIVITY_CODE,
MIN(S.SCHEDULE_DATE) AS SCHEDULE_DATE,
SS.STATUS,
A.ACTIVITY_ID,
AX.ACTIVITY_SIZE,
AX.ECOMMERCE_IND,
@ENROLL_COUNT AS ENROLLMENT,
@WAITLIST_COUNT AS WAITLIST,
@CONFIRM AS CONFIRMED,
@PENDING AS PENDING,
@WITHDRAWN AS WITHDRAWN,
@APPROVED AS APPROVED,
@DELETED as DELETED,
AX.WAITLIST_IND
From ACTIVITY_CLASS AS AX
JOIN ACTIVITY AS A ON (AX.ACTIVITY_ID = A.ACTIVITY_ID)
JOIN CUSTOMER AS C ON (A.CUSTOMER_ID = C.CUSTOMER_ID)
JOIN FISCAL_YEAR AS FY ON (AX.FISCAL_YEAR_ID = FY.FISCAL_YEAR_ID)
JOIN ACTIVITY_CODE AS AC ON (AX.ACTIVITY_CODE_ID = AC.ACTIVITY_CODE_ID)
JOIN SCHEDULE AS S ON (AX.ACTIVITY_CLASS_ID = S.ACTIVITY_CLASS_ID)
JOIN ACTIVITY_STATUS AS SS ON (AX.ACTIVITY_STATUS_ID = SS.ACTIVITY_STATUS_ID)
Where AX.ACTIVITY_CLASS_ID=@int_activity_class_id
GROUP BY A.ACTIVITY_NAME, A.DESCR,C.CUSTOMER_NAME,C.CUSTOMER_ID, A.PROVIDER_CODE, FY.FISCAL_YY, AC.CEU_ACTIVITY_CODE, ax.activity_seq_number, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.WAITLIST_IND,AX.ECOMMERCE_IND

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-18 : 18:59:09

Well, to start, you could change all of the individual SET @var = ... statements to one large SELECT @var1 = subquery1, @var2 = subquery2... and do it all in one step. But probably even more efficient would be to do a GROUP BY to get your counts

SELECT ACTIVITY_CLASS_ID, ENROLLMENT_STATUS_ID, COUNT(DISTINCT(ENROLLMENTID)) FROM Enrollment GROUP BY ACTIVITY_CLASS_ID, ENROLLMENT_STATUS_ID

Perhaps put that into a View and then join to it?

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources