|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
executed for 50 seconds on a single execution.In the last step I have used a right join which makes a very slow ,IS there somebody can give some hints to enhance the last join.Thanks alot CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), @Affiliate_ID NUMERIC(9), @IF_FileType VARCHAR(50), @CurrentAcademicYear NUMERIC(5), @CurrentSeesion NUMERIC(5), @Enrollment_Type INT) RETURNS INT AS BEGIN ------DECLARATION DECLARE @ClientType VARCHAR(2) DECLARE @Counter1 INT DECLARE @Counter2 INT DECLARE @MissingEnrollmentNumbers INT -- VARIABLE TABLES DECLARE @TBActiveClients TABLE(INTERNAL_ID NUMERIC(9)) --Contains all the active clients for an specific member Type (ST or MB) for an specifc affiliate DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID NUMERIC(9),COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), INTERNAL_ID NUMERIC(9)) --Contains all enrollments for clients in above temp table -------STEP 1 IF @IF_FileType LIKE '%MEMBER%' --This means that the MEMBER keyword is part of the word BEGIN SET @ClientType='MB' END ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the STUDENT keyword is part of the word BEGIN SET @ClientType='ST' END ELSE BEGIN SET @ClientType='NF' --This simply means that ClientType(MB or ST) could not be extracted based on this information END -------STEP 2 IF @ClientType='NF' BEGIN SET @MissingEnrollmentNumbers =-1 END ELSE BEGIN --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR "ST" ) FOR THE SPECIFIC AFFILIATE INSERT INTO @TBActiveClients(INTERNAL_ID) SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN dbo.USER_STATUS T2 ON T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND T1.USER_STATUS_CODE =T2.USER_STATUS_CODE WHERE T1.AFFILIATE_ID = @Affiliate_ID AND T1.USER_TYPE_CODE=@ClientType AND T2.ACTIVE_STATUS='Y' IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP TABLE(@TBActiveClients) BEGIN INSERT INTO @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_SESSION,INTERNAL_ID) SELECT T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL_ID FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON T4.INTERNAL_ID = T3.INTERNAL_ID WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= @CurrentSeesion) END ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP TABLE BEGIN INSERT INTO @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_SESSION,INTERNAL_ID) SELECT T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL_ID FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON T4.INTERNAL_ID = T3.INTERNAL_ID WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < @CurrentSeesion) END SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM @TBEnrollmentsForActiveClients ***********Problem is this join*************************************************************** ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON T1.SESSION_CODE=T2.COURSE_SESSION AND T1.COURSE_CODE=T2.COURSE_CODE AND T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND T1.INTERNAL_ID=T2.INTERNAL_ID WHERE T1.FILEID = @FileID SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND (CURRENT/ FUTURE OR HISTORY ) END RETURN @MissingEnrollmentNumbers END I'm too lazy to compile this thing in my head. Have you tried excuting the
function from Query Analyzer with the "Show Execution Plan" option enabled? That should tell you a lot. Also, I hope you are not calling this function for every row in a large rowset. Show quote "Ray5531" <Ray***@microsft.com> wrote in message @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... > I'm having a UDF which is quite fast till the last step which makes > executed for 50 seconds on a single execution.In the last step I have used a > right join which makes a very slow ,IS there somebody can give some hints to > enhance the last join.Thanks alot > > CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), > @Affiliate_ID NUMERIC(9), > @IF_FileType VARCHAR(50), > @CurrentAcademicYear NUMERIC(5), > @CurrentSeesion NUMERIC(5), > @Enrollment_Type INT) > RETURNS INT AS > BEGIN > ------DECLARATION > > DECLARE @ClientType VARCHAR(2) > DECLARE @Counter1 INT > DECLARE @Counter2 INT > DECLARE @MissingEnrollmentNumbers INT > -- VARIABLE TABLES > DECLARE @TBActiveClients TABLE(INTERNAL_ID NUMERIC(9)) --Contains all > the active clients for an specific member Type (ST or MB) for an specifc > affiliate > DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > NUMERIC(9),COURSE_CODE VARCHAR(3), > COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > INTERNAL_ID NUMERIC(9)) --Contains all enrollments for clients > in above temp table > > -------STEP 1 > > IF @IF_FileType LIKE '%MEMBER%' --This means that the MEMBER > keyword is part of the word > > BEGIN > SET @ClientType='MB' > END > > ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the STUDENT > keyword is part of the word > > BEGIN > SET @ClientType='ST' > END > > ELSE > BEGIN > SET @ClientType='NF' --This simply means that ClientType(MB or ST) could > not be extracted based on this information > END > > -------STEP 2 > > IF @ClientType='NF' > BEGIN > SET @MissingEnrollmentNumbers =-1 > END > ELSE > BEGIN > > --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR "ST" ) > FOR THE SPECIFIC AFFILIATE > INSERT INTO @TBActiveClients(INTERNAL_ID) > SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN > dbo.USER_STATUS T2 ON > T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND > T1.USER_STATUS_CODE =T2.USER_STATUS_CODE > WHERE T1.AFFILIATE_ID = @Affiliate_ID AND > T1.USER_TYPE_CODE=@ClientType AND > T2.ACTIVE_STATUS='Y' > > IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** > ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP TABLE(@TBActiveClients) > > BEGIN > > INSERT INTO > SESSION,INTERNAL_ID) > SELECT T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL> _ID > FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_> T4.INTERNAL_ID = T3.INTERNAL_ID > WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR > (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= > @CurrentSeesion) > > END > ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR ACTIVE > CLIENTS OF THE ABOVE TEMP TABLE > > BEGIN > > INSERT INTO > SESSION,INTERNAL_ID) > SELECT T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL> _ID Show quote > FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON > T4.INTERNAL_ID = T3.INTERNAL_ID > WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR > (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < > @CurrentSeesion) > > > END > > SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM > @TBEnrollmentsForActiveClients > > > ***********Problem is this > join*************************************************************** > ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE > (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE > > SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 > RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON > T1.SESSION_CODE=T2.COURSE_SESSION AND > T1.COURSE_CODE=T2.COURSE_CODE AND > T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND > T1.INTERNAL_ID=T2.INTERNAL_ID > WHERE T1.FILEID = @FileID > > > > SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF > ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND > (CURRENT/ FUTURE OR HISTORY ) > END > > > RETURN @MissingEnrollmentNumbers > END > > That function might be called for maximum 100 records:-(
Is that disaster? Thanks Show quote "JT" <some***@microsoft.com> wrote in message news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... > I'm too lazy to compile this thing in my head. Have you tried excuting the > function from Query Analyzer with the "Show Execution Plan" option > enabled? > That should tell you a lot. Also, I hope you are not calling this function > for every row in a large rowset. > > "Ray5531" <Ray***@microsft.com> wrote in message > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >> I'm having a UDF which is quite fast till the last step which makes >> executed for 50 seconds on a single execution.In the last step I have >> used > a >> right join which makes a very slow ,IS there somebody can give some hints > to >> enhance the last join.Thanks alot >> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >> @Affiliate_ID NUMERIC(9), >> @IF_FileType VARCHAR(50), >> @CurrentAcademicYear NUMERIC(5), >> @CurrentSeesion NUMERIC(5), >> @Enrollment_Type INT) >> RETURNS INT AS >> BEGIN >> ------DECLARATION >> >> DECLARE @ClientType VARCHAR(2) >> DECLARE @Counter1 INT >> DECLARE @Counter2 INT >> DECLARE @MissingEnrollmentNumbers INT >> -- VARIABLE TABLES >> DECLARE @TBActiveClients TABLE(INTERNAL_ID NUMERIC(9)) --Contains >> all >> the active clients for an specific member Type (ST or MB) for an specifc >> affiliate >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for > clients >> in above temp table >> >> -------STEP 1 >> >> IF @IF_FileType LIKE '%MEMBER%' --This means that the > MEMBER >> keyword is part of the word >> >> BEGIN >> SET @ClientType='MB' >> END >> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the STUDENT >> keyword is part of the word >> >> BEGIN >> SET @ClientType='ST' >> END >> >> ELSE >> BEGIN >> SET @ClientType='NF' --This simply means that ClientType(MB or ST) > could >> not be extracted based on this information >> END >> >> -------STEP 2 >> >> IF @ClientType='NF' >> BEGIN >> SET @MissingEnrollmentNumbers =-1 >> END >> ELSE >> BEGIN >> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >> "ST" ) >> FOR THE SPECIFIC AFFILIATE >> INSERT INTO @TBActiveClients(INTERNAL_ID) >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >> dbo.USER_STATUS T2 ON >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >> T1.USER_TYPE_CODE=@ClientType AND >> T2.ACTIVE_STATUS='Y' >> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP TABLE(@TBActiveClients) >> >> BEGIN >> >> INSERT INTO >> > @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ > SESSION,INTERNAL_ID) >> SELECT >> > T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL > _ID >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >> T4.INTERNAL_ID = T3.INTERNAL_ID >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >> @CurrentSeesion) >> >> END >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR ACTIVE >> CLIENTS OF THE ABOVE TEMP TABLE >> >> BEGIN >> >> INSERT INTO >> > @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ > SESSION,INTERNAL_ID) >> SELECT >> > T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL > _ID >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >> T4.INTERNAL_ID = T3.INTERNAL_ID >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >> @CurrentSeesion) >> >> >> END >> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >> @TBEnrollmentsForActiveClients >> >> >> ***********Problem is this >> join*************************************************************** >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >> T1.SESSION_CODE=T2.COURSE_SESSION AND >> T1.COURSE_CODE=T2.COURSE_CODE AND >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >> T1.INTERNAL_ID=T2.INTERNAL_ID >> WHERE T1.FILEID = @FileID >> >> >> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND >> (CURRENT/ FUTURE OR HISTORY ) >> END >> >> >> RETURN @MissingEnrollmentNumbers >> END >> >> > > You be the judge, it's your app. What is the query execution plan telling
you? Show quote "Ray5531" <Ray***@microsft.com> wrote in message @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... > That function might be called for maximum 100 records:-( > > Is that disaster? > > Thanks > "JT" <some***@microsoft.com> wrote in message > news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... > > I'm too lazy to compile this thing in my head. Have you tried excuting the > > function from Query Analyzer with the "Show Execution Plan" option > > enabled? > > That should tell you a lot. Also, I hope you are not calling this function > > for every row in a large rowset. > > > > "Ray5531" <Ray***@microsft.com> wrote in message > > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... > >> I'm having a UDF which is quite fast till the last step which makes > >> executed for 50 seconds on a single execution.In the last step I have > >> used > > a > >> right join which makes a very slow ,IS there somebody can give some hints > > to > >> enhance the last join.Thanks alot > >> > >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), > >> @Affiliate_ID NUMERIC(9), > >> @IF_FileType VARCHAR(50), > >> @CurrentAcademicYear NUMERIC(5), > >> @CurrentSeesion NUMERIC(5), > >> @Enrollment_Type INT) > >> RETURNS INT AS > >> BEGIN > >> ------DECLARATION > >> > >> DECLARE @ClientType VARCHAR(2) > >> DECLARE @Counter1 INT > >> DECLARE @Counter2 INT > >> DECLARE @MissingEnrollmentNumbers INT > >> -- VARIABLE TABLES > >> DECLARE @TBActiveClients TABLE(INTERNAL_ID NUMERIC(9)) --Contains > >> all > >> the active clients for an specific member Type (ST or MB) for an specifc > >> affiliate > >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID > >> NUMERIC(9),COURSE_CODE VARCHAR(3), > >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), > >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for > > clients > >> in above temp table > >> > >> -------STEP 1 > >> > >> IF @IF_FileType LIKE '%MEMBER%' --This means that the > > MEMBER > >> keyword is part of the word > >> > >> BEGIN > >> SET @ClientType='MB' > >> END > >> > >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the STUDENT > >> keyword is part of the word > >> > >> BEGIN > >> SET @ClientType='ST' > >> END > >> > >> ELSE > >> BEGIN > >> SET @ClientType='NF' --This simply means that ClientType(MB or ST) > > could > >> not be extracted based on this information > >> END > >> > >> -------STEP 2 > >> > >> IF @ClientType='NF' > >> BEGIN > >> SET @MissingEnrollmentNumbers =-1 > >> END > >> ELSE > >> BEGIN > >> > >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR > >> "ST" ) > >> FOR THE SPECIFIC AFFILIATE > >> INSERT INTO @TBActiveClients(INTERNAL_ID) > >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN > >> dbo.USER_STATUS T2 ON > >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND > >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE > >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND > >> T1.USER_TYPE_CODE=@ClientType AND > >> T2.ACTIVE_STATUS='Y' > >> > >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** > >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP TABLE(@TBActiveClients) > >> > >> BEGIN > >> > >> INSERT INTO > >> > > > > SESSION,INTERNAL_ID) T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL> >> SELECT > >> > > Show quote > > _ID @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON > >> T4.INTERNAL_ID = T3.INTERNAL_ID > >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR > >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= > >> @CurrentSeesion) > >> > >> END > >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR ACTIVE > >> CLIENTS OF THE ABOVE TEMP TABLE > >> > >> BEGIN > >> > >> INSERT INTO > >> > > > > SESSION,INTERNAL_ID) T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL> >> SELECT > >> > > Show quote > > _ID > >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON > >> T4.INTERNAL_ID = T3.INTERNAL_ID > >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR > >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < > >> @CurrentSeesion) > >> > >> > >> END > >> > >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM > >> @TBEnrollmentsForActiveClients > >> > >> > >> ***********Problem is this > >> join*************************************************************** > >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE > >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE > >> > >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 > >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON > >> T1.SESSION_CODE=T2.COURSE_SESSION AND > >> T1.COURSE_CODE=T2.COURSE_CODE AND > >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND > >> T1.INTERNAL_ID=T2.INTERNAL_ID > >> WHERE T1.FILEID = @FileID > >> > >> > >> > >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF > >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND > >> (CURRENT/ FUTURE OR HISTORY ) > >> END > >> > >> > >> RETURN @MissingEnrollmentNumbers > >> END > >> > >> > > > > > > Enabled Estimated Execution plan and here is th query cost relative to the
batch for different parts: A) 95% ??????? <--- this one is hurting the performace ,it is nagging about my primary key on CLIENT table which is aclustered index.I dropped the index and recreate it-no difference.I deleted the index still the same!!!! INSERT INTO @TBActiveClients(INTERNAL_ID) SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN dbo.USER_STATUS T2 ON T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND T1.USER_STATUS_CODE =T2.USER_STATUS_CODE WHERE T1.AFFILIATE_ID = 3 AND T1.USER_TYPE_CODE='ST' AND T2.ACTIVE_STATUS='Y' B) 0.95% <---This one after putting index is very good now SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON T1.SESSION_CODE=T2.COURSE_SESSION AND T1.COURSE_CODE=T2.COURSE_CODE AND T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND T1.INTERNAL_ID=T2.INTERNAL_ID WHERE T1.FILEID = 1 Show quote "JT" <some***@microsoft.com> wrote in message news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... > You be the judge, it's your app. What is the query execution plan telling > you? > > "Ray5531" <Ray***@microsft.com> wrote in message > news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >> That function might be called for maximum 100 records:-( >> >> Is that disaster? >> >> Thanks >> "JT" <some***@microsoft.com> wrote in message >> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >> > I'm too lazy to compile this thing in my head. Have you tried excuting > the >> > function from Query Analyzer with the "Show Execution Plan" option >> > enabled? >> > That should tell you a lot. Also, I hope you are not calling this > function >> > for every row in a large rowset. >> > >> > "Ray5531" <Ray***@microsft.com> wrote in message >> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >> >> I'm having a UDF which is quite fast till the last step which makes >> >> executed for 50 seconds on a single execution.In the last step I have >> >> used >> > a >> >> right join which makes a very slow ,IS there somebody can give some > hints >> > to >> >> enhance the last join.Thanks alot >> >> >> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >> >> @Affiliate_ID NUMERIC(9), >> >> @IF_FileType VARCHAR(50), >> >> @CurrentAcademicYear NUMERIC(5), >> >> @CurrentSeesion NUMERIC(5), >> >> @Enrollment_Type INT) >> >> RETURNS INT AS >> >> BEGIN >> >> ------DECLARATION >> >> >> >> DECLARE @ClientType VARCHAR(2) >> >> DECLARE @Counter1 INT >> >> DECLARE @Counter2 INT >> >> DECLARE @MissingEnrollmentNumbers INT >> >> -- VARIABLE TABLES >> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID NUMERIC(9)) --Contains >> >> all >> >> the active clients for an specific member Type (ST or MB) for an > specifc >> >> affiliate >> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for >> > clients >> >> in above temp table >> >> >> >> -------STEP 1 >> >> >> >> IF @IF_FileType LIKE '%MEMBER%' --This means that the >> > MEMBER >> >> keyword is part of the word >> >> >> >> BEGIN >> >> SET @ClientType='MB' >> >> END >> >> >> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the > STUDENT >> >> keyword is part of the word >> >> >> >> BEGIN >> >> SET @ClientType='ST' >> >> END >> >> >> >> ELSE >> >> BEGIN >> >> SET @ClientType='NF' --This simply means that ClientType(MB or ST) >> > could >> >> not be extracted based on this information >> >> END >> >> >> >> -------STEP 2 >> >> >> >> IF @ClientType='NF' >> >> BEGIN >> >> SET @MissingEnrollmentNumbers =-1 >> >> END >> >> ELSE >> >> BEGIN >> >> >> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >> >> "ST" ) >> >> FOR THE SPECIFIC AFFILIATE >> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >> >> dbo.USER_STATUS T2 ON >> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >> >> T1.USER_TYPE_CODE=@ClientType AND >> >> T2.ACTIVE_STATUS='Y' >> >> >> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP > TABLE(@TBActiveClients) >> >> >> >> BEGIN >> >> >> >> INSERT INTO >> >> >> > > @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >> > SESSION,INTERNAL_ID) >> >> SELECT >> >> >> > > T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >> > _ID >> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >> >> T4.INTERNAL_ID = T3.INTERNAL_ID >> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >> >> @CurrentSeesion) >> >> >> >> END >> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR > ACTIVE >> >> CLIENTS OF THE ABOVE TEMP TABLE >> >> >> >> BEGIN >> >> >> >> INSERT INTO >> >> >> > > @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >> > SESSION,INTERNAL_ID) >> >> SELECT >> >> >> > > T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >> > _ID >> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >> >> T4.INTERNAL_ID = T3.INTERNAL_ID >> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >> >> @CurrentSeesion) >> >> >> >> >> >> END >> >> >> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >> >> @TBEnrollmentsForActiveClients >> >> >> >> >> >> ***********Problem is this >> >> join*************************************************************** >> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >> >> >> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >> >> T1.COURSE_CODE=T2.COURSE_CODE AND >> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >> >> T1.INTERNAL_ID=T2.INTERNAL_ID >> >> WHERE T1.FILEID = @FileID >> >> >> >> >> >> >> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND >> >> (CURRENT/ FUTURE OR HISTORY ) >> >> END >> >> >> >> >> >> RETURN @MissingEnrollmentNumbers >> >> END >> >> >> >> >> > >> > >> >> > > Try updating statistics with FULLSCAN, see if it makes a difference.
Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... > Enabled Estimated Execution plan and here is th query cost relative to > the batch for different parts: > > A) 95% ??????? <--- this one is hurting the performace ,it is nagging > about my primary key on CLIENT table which is aclustered index.I dropped > the index and recreate it-no difference.I deleted the index still the > same!!!! > > INSERT INTO @TBActiveClients(INTERNAL_ID) > SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN > dbo.USER_STATUS T2 ON > T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND > T1.USER_STATUS_CODE =T2.USER_STATUS_CODE > WHERE T1.AFFILIATE_ID = 3 AND > T1.USER_TYPE_CODE='ST' AND > T2.ACTIVE_STATUS='Y' > > > B) 0.95% <---This one after putting index is very good now > > SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 RIGHT > OUTER JOIN @TBEnrollmentsForActiveClients T2 ON > T1.SESSION_CODE=T2.COURSE_SESSION AND > T1.COURSE_CODE=T2.COURSE_CODE AND > T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND > T1.INTERNAL_ID=T2.INTERNAL_ID > WHERE T1.FILEID = 1 > > > > "JT" <some***@microsoft.com> wrote in message > news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >> You be the judge, it's your app. What is the query execution plan telling >> you? >> >> "Ray5531" <Ray***@microsft.com> wrote in message >> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>> That function might be called for maximum 100 records:-( >>> >>> Is that disaster? >>> >>> Thanks >>> "JT" <some***@microsoft.com> wrote in message >>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>> > I'm too lazy to compile this thing in my head. Have you tried excuting >> the >>> > function from Query Analyzer with the "Show Execution Plan" option >>> > enabled? >>> > That should tell you a lot. Also, I hope you are not calling this >> function >>> > for every row in a large rowset. >>> > >>> > "Ray5531" <Ray***@microsft.com> wrote in message >>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>> >> I'm having a UDF which is quite fast till the last step which makes >>> >> executed for 50 seconds on a single execution.In the last step I have >>> >> used >>> > a >>> >> right join which makes a very slow ,IS there somebody can give some >> hints >>> > to >>> >> enhance the last join.Thanks alot >>> >> >>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>> >> @Affiliate_ID NUMERIC(9), >>> >> @IF_FileType VARCHAR(50), >>> >> @CurrentAcademicYear NUMERIC(5), >>> >> @CurrentSeesion NUMERIC(5), >>> >> @Enrollment_Type INT) >>> >> RETURNS INT AS >>> >> BEGIN >>> >> ------DECLARATION >>> >> >>> >> DECLARE @ClientType VARCHAR(2) >>> >> DECLARE @Counter1 INT >>> >> DECLARE @Counter2 INT >>> >> DECLARE @MissingEnrollmentNumbers INT >>> >> -- VARIABLE TABLES >>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID >>> >> MERIC(9)) --Contains >>> >> all >>> >> the active clients for an specific member Type (ST or MB) for an >> specifc >>> >> affiliate >>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for >>> > clients >>> >> in above temp table >>> >> >>> >> -------STEP 1 >>> >> >>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that the >>> > MEMBER >>> >> keyword is part of the word >>> >> >>> >> BEGIN >>> >> SET @ClientType='MB' >>> >> END >>> >> >>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >> STUDENT >>> >> keyword is part of the word >>> >> >>> >> BEGIN >>> >> SET @ClientType='ST' >>> >> END >>> >> >>> >> ELSE >>> >> BEGIN >>> >> SET @ClientType='NF' --This simply means that ClientType(MB or >>> >> ST) >>> > could >>> >> not be extracted based on this information >>> >> END >>> >> >>> >> -------STEP 2 >>> >> >>> >> IF @ClientType='NF' >>> >> BEGIN >>> >> SET @MissingEnrollmentNumbers =-1 >>> >> END >>> >> ELSE >>> >> BEGIN >>> >> >>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >>> >> "ST" ) >>> >> FOR THE SPECIFIC AFFILIATE >>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>> >> dbo.USER_STATUS T2 ON >>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>> >> T1.USER_TYPE_CODE=@ClientType AND >>> >> T2.ACTIVE_STATUS='Y' >>> >> >>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >> TABLE(@TBActiveClients) >>> >> >>> >> BEGIN >>> >> >>> >> INSERT INTO >>> >> >>> > >> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>> > SESSION,INTERNAL_ID) >>> >> SELECT >>> >> >>> > >> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>> > _ID >>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>> >> @CurrentSeesion) >>> >> >>> >> END >>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR >> ACTIVE >>> >> CLIENTS OF THE ABOVE TEMP TABLE >>> >> >>> >> BEGIN >>> >> >>> >> INSERT INTO >>> >> >>> > >> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>> > SESSION,INTERNAL_ID) >>> >> SELECT >>> >> >>> > >> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>> > _ID >>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>> >> @CurrentSeesion) >>> >> >>> >> >>> >> END >>> >> >>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>> >> @TBEnrollmentsForActiveClients >>> >> >>> >> >>> >> ***********Problem is this >>> >> join*************************************************************** >>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>> >> >>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM >>> >> T1 >>> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >>> >> T1.COURSE_CODE=T2.COURSE_CODE AND >>> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>> >> T1.INTERNAL_ID=T2.INTERNAL_ID >>> >> WHERE T1.FILEID = @FileID >>> >> >>> >> >>> >> >>> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >>> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND >>> >> (CURRENT/ FUTURE OR HISTORY ) >>> >> END >>> >> >>> >> >>> >> RETURN @MissingEnrollmentNumbers >>> >> END >>> >> >>> >> >>> > >>> > >>> >>> >> >> > > I know how to update statistic ,but I have no idea how to mention FULLSCAN..
Thanks for help Show quote "Michael C#" <ho***@boutdat.com> wrote in message news:Oq2LexzVFHA.616@TK2MSFTNGP12.phx.gbl... > Try updating statistics with FULLSCAN, see if it makes a difference. > > "Ray5531" <Ray***@microsft.com> wrote in message > news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... >> Enabled Estimated Execution plan and here is th query cost relative to >> the batch for different parts: >> >> A) 95% ??????? <--- this one is hurting the performace ,it is nagging >> about my primary key on CLIENT table which is aclustered index.I dropped >> the index and recreate it-no difference.I deleted the index still the >> same!!!! >> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >> dbo.USER_STATUS T2 ON >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >> WHERE T1.AFFILIATE_ID = 3 AND >> T1.USER_TYPE_CODE='ST' AND >> T2.ACTIVE_STATUS='Y' >> >> >> B) 0.95% <---This one after putting index is very good now >> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >> T1.SESSION_CODE=T2.COURSE_SESSION AND >> T1.COURSE_CODE=T2.COURSE_CODE AND >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >> T1.INTERNAL_ID=T2.INTERNAL_ID >> WHERE T1.FILEID = 1 >> >> >> >> "JT" <some***@microsoft.com> wrote in message >> news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >>> You be the judge, it's your app. What is the query execution plan >>> telling >>> you? >>> >>> "Ray5531" <Ray***@microsft.com> wrote in message >>> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>>> That function might be called for maximum 100 records:-( >>>> >>>> Is that disaster? >>>> >>>> Thanks >>>> "JT" <some***@microsoft.com> wrote in message >>>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>>> > I'm too lazy to compile this thing in my head. Have you tried >>>> > excuting >>> the >>>> > function from Query Analyzer with the "Show Execution Plan" option >>>> > enabled? >>>> > That should tell you a lot. Also, I hope you are not calling this >>> function >>>> > for every row in a large rowset. >>>> > >>>> > "Ray5531" <Ray***@microsft.com> wrote in message >>>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>>> >> I'm having a UDF which is quite fast till the last step which makes >>>> >> executed for 50 seconds on a single execution.In the last step I >>>> >> have >>>> >> used >>>> > a >>>> >> right join which makes a very slow ,IS there somebody can give some >>> hints >>>> > to >>>> >> enhance the last join.Thanks alot >>>> >> >>>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>>> >> @Affiliate_ID NUMERIC(9), >>>> >> @IF_FileType VARCHAR(50), >>>> >> @CurrentAcademicYear NUMERIC(5), >>>> >> @CurrentSeesion NUMERIC(5), >>>> >> @Enrollment_Type INT) >>>> >> RETURNS INT AS >>>> >> BEGIN >>>> >> ------DECLARATION >>>> >> >>>> >> DECLARE @ClientType VARCHAR(2) >>>> >> DECLARE @Counter1 INT >>>> >> DECLARE @Counter2 INT >>>> >> DECLARE @MissingEnrollmentNumbers INT >>>> >> -- VARIABLE TABLES >>>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID MERIC(9)) --Contains >>>> >> all >>>> >> the active clients for an specific member Type (ST or MB) for an >>> specifc >>>> >> affiliate >>>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for >>>> > clients >>>> >> in above temp table >>>> >> >>>> >> -------STEP 1 >>>> >> >>>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that >>>> >> the >>>> > MEMBER >>>> >> keyword is part of the word >>>> >> >>>> >> BEGIN >>>> >> SET @ClientType='MB' >>>> >> END >>>> >> >>>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >>> STUDENT >>>> >> keyword is part of the word >>>> >> >>>> >> BEGIN >>>> >> SET @ClientType='ST' >>>> >> END >>>> >> >>>> >> ELSE >>>> >> BEGIN >>>> >> SET @ClientType='NF' --This simply means that ClientType(MB or >>>> >> ST) >>>> > could >>>> >> not be extracted based on this information >>>> >> END >>>> >> >>>> >> -------STEP 2 >>>> >> >>>> >> IF @ClientType='NF' >>>> >> BEGIN >>>> >> SET @MissingEnrollmentNumbers =-1 >>>> >> END >>>> >> ELSE >>>> >> BEGIN >>>> >> >>>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >>>> >> "ST" ) >>>> >> FOR THE SPECIFIC AFFILIATE >>>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>> >> dbo.USER_STATUS T2 ON >>>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>>> >> T1.USER_TYPE_CODE=@ClientType AND >>>> >> T2.ACTIVE_STATUS='Y' >>>> >> >>>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >>> TABLE(@TBActiveClients) >>>> >> >>>> >> BEGIN >>>> >> >>>> >> INSERT INTO >>>> >> >>>> > >>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>> > SESSION,INTERNAL_ID) >>>> >> SELECT >>>> >> >>>> > >>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>> > _ID >>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>>> >> @CurrentSeesion) >>>> >> >>>> >> END >>>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR >>> ACTIVE >>>> >> CLIENTS OF THE ABOVE TEMP TABLE >>>> >> >>>> >> BEGIN >>>> >> >>>> >> INSERT INTO >>>> >> >>>> > >>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>> > SESSION,INTERNAL_ID) >>>> >> SELECT >>>> >> >>>> > >>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>> > _ID >>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>>> >> @CurrentSeesion) >>>> >> >>>> >> >>>> >> END >>>> >> >>>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>>> >> @TBEnrollmentsForActiveClients >>>> >> >>>> >> >>>> >> ***********Problem is this >>>> >> join*************************************************************** >>>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>>> >> >>>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM >>>> >> T1 >>>> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>> >> T1.COURSE_CODE=T2.COURSE_CODE AND >>>> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>> >> T1.INTERNAL_ID=T2.INTERNAL_ID >>>> >> WHERE T1.FILEID = @FileID >>>> >> >>>> >> >>>> >> >>>> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >>>> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND >>>> >> (CURRENT/ FUTURE OR HISTORY ) >>>> >> END >>>> >> >>>> >> >>>> >> RETURN @MissingEnrollmentNumbers >>>> >> END >>>> >> >>>> >> >>>> > >>>> > >>>> >>>> >>> >>> >> >> > > I did the update with full scan and the time got increased to 80% :-)
Thanks Show quote "Michael C#" <ho***@boutdat.com> wrote in message news:Oq2LexzVFHA.616@TK2MSFTNGP12.phx.gbl... > Try updating statistics with FULLSCAN, see if it makes a difference. > > "Ray5531" <Ray***@microsft.com> wrote in message > news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... >> Enabled Estimated Execution plan and here is th query cost relative to >> the batch for different parts: >> >> A) 95% ??????? <--- this one is hurting the performace ,it is nagging >> about my primary key on CLIENT table which is aclustered index.I dropped >> the index and recreate it-no difference.I deleted the index still the >> same!!!! >> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >> dbo.USER_STATUS T2 ON >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >> WHERE T1.AFFILIATE_ID = 3 AND >> T1.USER_TYPE_CODE='ST' AND >> T2.ACTIVE_STATUS='Y' >> >> >> B) 0.95% <---This one after putting index is very good now >> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >> T1.SESSION_CODE=T2.COURSE_SESSION AND >> T1.COURSE_CODE=T2.COURSE_CODE AND >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >> T1.INTERNAL_ID=T2.INTERNAL_ID >> WHERE T1.FILEID = 1 >> >> >> >> "JT" <some***@microsoft.com> wrote in message >> news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >>> You be the judge, it's your app. What is the query execution plan >>> telling >>> you? >>> >>> "Ray5531" <Ray***@microsft.com> wrote in message >>> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>>> That function might be called for maximum 100 records:-( >>>> >>>> Is that disaster? >>>> >>>> Thanks >>>> "JT" <some***@microsoft.com> wrote in message >>>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>>> > I'm too lazy to compile this thing in my head. Have you tried >>>> > excuting >>> the >>>> > function from Query Analyzer with the "Show Execution Plan" option >>>> > enabled? >>>> > That should tell you a lot. Also, I hope you are not calling this >>> function >>>> > for every row in a large rowset. >>>> > >>>> > "Ray5531" <Ray***@microsft.com> wrote in message >>>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>>> >> I'm having a UDF which is quite fast till the last step which makes >>>> >> executed for 50 seconds on a single execution.In the last step I >>>> >> have >>>> >> used >>>> > a >>>> >> right join which makes a very slow ,IS there somebody can give some >>> hints >>>> > to >>>> >> enhance the last join.Thanks alot >>>> >> >>>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>>> >> @Affiliate_ID NUMERIC(9), >>>> >> @IF_FileType VARCHAR(50), >>>> >> @CurrentAcademicYear NUMERIC(5), >>>> >> @CurrentSeesion NUMERIC(5), >>>> >> @Enrollment_Type INT) >>>> >> RETURNS INT AS >>>> >> BEGIN >>>> >> ------DECLARATION >>>> >> >>>> >> DECLARE @ClientType VARCHAR(2) >>>> >> DECLARE @Counter1 INT >>>> >> DECLARE @Counter2 INT >>>> >> DECLARE @MissingEnrollmentNumbers INT >>>> >> -- VARIABLE TABLES >>>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID MERIC(9)) --Contains >>>> >> all >>>> >> the active clients for an specific member Type (ST or MB) for an >>> specifc >>>> >> affiliate >>>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments for >>>> > clients >>>> >> in above temp table >>>> >> >>>> >> -------STEP 1 >>>> >> >>>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that >>>> >> the >>>> > MEMBER >>>> >> keyword is part of the word >>>> >> >>>> >> BEGIN >>>> >> SET @ClientType='MB' >>>> >> END >>>> >> >>>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >>> STUDENT >>>> >> keyword is part of the word >>>> >> >>>> >> BEGIN >>>> >> SET @ClientType='ST' >>>> >> END >>>> >> >>>> >> ELSE >>>> >> BEGIN >>>> >> SET @ClientType='NF' --This simply means that ClientType(MB or >>>> >> ST) >>>> > could >>>> >> not be extracted based on this information >>>> >> END >>>> >> >>>> >> -------STEP 2 >>>> >> >>>> >> IF @ClientType='NF' >>>> >> BEGIN >>>> >> SET @MissingEnrollmentNumbers =-1 >>>> >> END >>>> >> ELSE >>>> >> BEGIN >>>> >> >>>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >>>> >> "ST" ) >>>> >> FOR THE SPECIFIC AFFILIATE >>>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>> >> dbo.USER_STATUS T2 ON >>>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>>> >> T1.USER_TYPE_CODE=@ClientType AND >>>> >> T2.ACTIVE_STATUS='Y' >>>> >> >>>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >>> TABLE(@TBActiveClients) >>>> >> >>>> >> BEGIN >>>> >> >>>> >> INSERT INTO >>>> >> >>>> > >>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>> > SESSION,INTERNAL_ID) >>>> >> SELECT >>>> >> >>>> > >>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>> > _ID >>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>>> >> @CurrentSeesion) >>>> >> >>>> >> END >>>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR >>> ACTIVE >>>> >> CLIENTS OF THE ABOVE TEMP TABLE >>>> >> >>>> >> BEGIN >>>> >> >>>> >> INSERT INTO >>>> >> >>>> > >>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>> > SESSION,INTERNAL_ID) >>>> >> SELECT >>>> >> >>>> > >>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>> > _ID >>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 ON >>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>>> >> @CurrentSeesion) >>>> >> >>>> >> >>>> >> END >>>> >> >>>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>>> >> @TBEnrollmentsForActiveClients >>>> >> >>>> >> >>>> >> ***********Problem is this >>>> >> join*************************************************************** >>>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>>> >> >>>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM >>>> >> T1 >>>> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>> >> T1.COURSE_CODE=T2.COURSE_CODE AND >>>> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>> >> T1.INTERNAL_ID=T2.INTERNAL_ID >>>> >> WHERE T1.FILEID = @FileID >>>> >> >>>> >> >>>> >> >>>> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >>>> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE AND >>>> >> (CURRENT/ FUTURE OR HISTORY ) >>>> >> END >>>> >> >>>> >> >>>> >> RETURN @MissingEnrollmentNumbers >>>> >> END >>>> >> >>>> >> >>>> > >>>> > >>>> >>>> >>> >>> >> >> > > Look at your graphical query execution plan in QA and see how many rows are
being returned in the various steps, and the relative cost of each step in the query. There may be a way to cut down the number of rows or re-write the query a little to speed it up based on the execution plan. Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:%23qbO3%23zVFHA.2520@TK2MSFTNGP09.phx.gbl... >I did the update with full scan and the time got increased to 80% :-) > Thanks > "Michael C#" <ho***@boutdat.com> wrote in message > news:Oq2LexzVFHA.616@TK2MSFTNGP12.phx.gbl... >> Try updating statistics with FULLSCAN, see if it makes a difference. >> >> "Ray5531" <Ray***@microsft.com> wrote in message >> news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... >>> Enabled Estimated Execution plan and here is th query cost relative to >>> the batch for different parts: >>> >>> A) 95% ??????? <--- this one is hurting the performace ,it is nagging >>> about my primary key on CLIENT table which is aclustered index.I dropped >>> the index and recreate it-no difference.I deleted the index still the >>> same!!!! >>> >>> INSERT INTO @TBActiveClients(INTERNAL_ID) >>> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>> dbo.USER_STATUS T2 ON >>> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>> WHERE T1.AFFILIATE_ID = 3 AND >>> T1.USER_TYPE_CODE='ST' AND >>> T2.ACTIVE_STATUS='Y' >>> >>> >>> B) 0.95% <---This one after putting index is very good now >>> >>> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >>> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>> T1.SESSION_CODE=T2.COURSE_SESSION AND >>> T1.COURSE_CODE=T2.COURSE_CODE AND >>> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>> T1.INTERNAL_ID=T2.INTERNAL_ID >>> WHERE T1.FILEID = 1 >>> >>> >>> >>> "JT" <some***@microsoft.com> wrote in message >>> news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >>>> You be the judge, it's your app. What is the query execution plan >>>> telling >>>> you? >>>> >>>> "Ray5531" <Ray***@microsft.com> wrote in message >>>> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>>>> That function might be called for maximum 100 records:-( >>>>> >>>>> Is that disaster? >>>>> >>>>> Thanks >>>>> "JT" <some***@microsoft.com> wrote in message >>>>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>>>> > I'm too lazy to compile this thing in my head. Have you tried >>>>> > excuting >>>> the >>>>> > function from Query Analyzer with the "Show Execution Plan" option >>>>> > enabled? >>>>> > That should tell you a lot. Also, I hope you are not calling this >>>> function >>>>> > for every row in a large rowset. >>>>> > >>>>> > "Ray5531" <Ray***@microsft.com> wrote in message >>>>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>>>> >> I'm having a UDF which is quite fast till the last step which makes >>>>> >> executed for 50 seconds on a single execution.In the last step I >>>>> >> have >>>>> >> used >>>>> > a >>>>> >> right join which makes a very slow ,IS there somebody can give some >>>> hints >>>>> > to >>>>> >> enhance the last join.Thanks alot >>>>> >> >>>>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>>>> >> @Affiliate_ID NUMERIC(9), >>>>> >> @IF_FileType VARCHAR(50), >>>>> >> @CurrentAcademicYear NUMERIC(5), >>>>> >> @CurrentSeesion NUMERIC(5), >>>>> >> @Enrollment_Type INT) >>>>> >> RETURNS INT AS >>>>> >> BEGIN >>>>> >> ------DECLARATION >>>>> >> >>>>> >> DECLARE @ClientType VARCHAR(2) >>>>> >> DECLARE @Counter1 INT >>>>> >> DECLARE @Counter2 INT >>>>> >> DECLARE @MissingEnrollmentNumbers INT >>>>> >> -- VARIABLE TABLES >>>>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID >>>>> >> RIC(9)) --Contains >>>>> >> all >>>>> >> the active clients for an specific member Type (ST or MB) for an >>>> specifc >>>>> >> affiliate >>>>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>>>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>>>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>>>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments >>>>> >> for >>>>> > clients >>>>> >> in above temp table >>>>> >> >>>>> >> -------STEP 1 >>>>> >> >>>>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that >>>>> >> the >>>>> > MEMBER >>>>> >> keyword is part of the word >>>>> >> >>>>> >> BEGIN >>>>> >> SET @ClientType='MB' >>>>> >> END >>>>> >> >>>>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >>>> STUDENT >>>>> >> keyword is part of the word >>>>> >> >>>>> >> BEGIN >>>>> >> SET @ClientType='ST' >>>>> >> END >>>>> >> >>>>> >> ELSE >>>>> >> BEGIN >>>>> >> SET @ClientType='NF' --This simply means that ClientType(MB or >>>>> >> ST) >>>>> > could >>>>> >> not be extracted based on this information >>>>> >> END >>>>> >> >>>>> >> -------STEP 2 >>>>> >> >>>>> >> IF @ClientType='NF' >>>>> >> BEGIN >>>>> >> SET @MissingEnrollmentNumbers =-1 >>>>> >> END >>>>> >> ELSE >>>>> >> BEGIN >>>>> >> >>>>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" OR >>>>> >> "ST" ) >>>>> >> FOR THE SPECIFIC AFFILIATE >>>>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>>> >> dbo.USER_STATUS T2 ON >>>>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>>>> >> T1.USER_TYPE_CODE=@ClientType AND >>>>> >> T2.ACTIVE_STATUS='Y' >>>>> >> >>>>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>>>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >>>> TABLE(@TBActiveClients) >>>>> >> >>>>> >> BEGIN >>>>> >> >>>>> >> INSERT INTO >>>>> >> >>>>> > >>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>> > SESSION,INTERNAL_ID) >>>>> >> SELECT >>>>> >> >>>>> > >>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>> > _ID >>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>> >> ON >>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>>>> >> @CurrentSeesion) >>>>> >> >>>>> >> END >>>>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR >>>> ACTIVE >>>>> >> CLIENTS OF THE ABOVE TEMP TABLE >>>>> >> >>>>> >> BEGIN >>>>> >> >>>>> >> INSERT INTO >>>>> >> >>>>> > >>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>> > SESSION,INTERNAL_ID) >>>>> >> SELECT >>>>> >> >>>>> > >>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>> > _ID >>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>> >> ON >>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>>>> >> @CurrentSeesion) >>>>> >> >>>>> >> >>>>> >> END >>>>> >> >>>>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>>>> >> @TBEnrollmentsForActiveClients >>>>> >> >>>>> >> >>>>> >> ***********Problem is this >>>>> >> join*************************************************************** >>>>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>>>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>>>> >> >>>>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM >>>>> >> T1 >>>>> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>>> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>>> >> T1.COURSE_CODE=T2.COURSE_CODE AND >>>>> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>>> >> T1.INTERNAL_ID=T2.INTERNAL_ID >>>>> >> WHERE T1.FILEID = @FileID >>>>> >> >>>>> >> >>>>> >> >>>>> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER OF >>>>> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE >>>>> >> AND >>>>> >> (CURRENT/ FUTURE OR HISTORY ) >>>>> >> END >>>>> >> >>>>> >> >>>>> >> RETURN @MissingEnrollmentNumbers >>>>> >> END >>>>> >> >>>>> >> >>>>> > >>>>> > >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > All stepes cost are so imperceptible,the only one which is huge in campe
with the others is the first one and it is returning 74000 records which I need them all. Thanks Show quote "Michael C#" <x**@abcdef.com> wrote in message news:PGRge.10825$NZ1.5383@fe09.lga... > Look at your graphical query execution plan in QA and see how many rows > are being returned in the various steps, and the relative cost of each > step in the query. There may be a way to cut down the number of rows or > re-write the query a little to speed it up based on the execution plan. > > "Ray5531" <Ray***@microsft.com> wrote in message > news:%23qbO3%23zVFHA.2520@TK2MSFTNGP09.phx.gbl... >>I did the update with full scan and the time got increased to 80% :-) >> Thanks >> "Michael C#" <ho***@boutdat.com> wrote in message >> news:Oq2LexzVFHA.616@TK2MSFTNGP12.phx.gbl... >>> Try updating statistics with FULLSCAN, see if it makes a difference. >>> >>> "Ray5531" <Ray***@microsft.com> wrote in message >>> news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... >>>> Enabled Estimated Execution plan and here is th query cost relative to >>>> the batch for different parts: >>>> >>>> A) 95% ??????? <--- this one is hurting the performace ,it is nagging >>>> about my primary key on CLIENT table which is aclustered index.I >>>> dropped the index and recreate it-no difference.I deleted the index >>>> still the same!!!! >>>> >>>> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>> dbo.USER_STATUS T2 ON >>>> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>> WHERE T1.AFFILIATE_ID = 3 AND >>>> T1.USER_TYPE_CODE='ST' AND >>>> T2.ACTIVE_STATUS='Y' >>>> >>>> >>>> B) 0.95% <---This one after putting index is very good now >>>> >>>> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >>>> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>> T1.COURSE_CODE=T2.COURSE_CODE AND >>>> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>> T1.INTERNAL_ID=T2.INTERNAL_ID >>>> WHERE T1.FILEID = 1 >>>> >>>> >>>> >>>> "JT" <some***@microsoft.com> wrote in message >>>> news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >>>>> You be the judge, it's your app. What is the query execution plan >>>>> telling >>>>> you? >>>>> >>>>> "Ray5531" <Ray***@microsft.com> wrote in message >>>>> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>>>>> That function might be called for maximum 100 records:-( >>>>>> >>>>>> Is that disaster? >>>>>> >>>>>> Thanks >>>>>> "JT" <some***@microsoft.com> wrote in message >>>>>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>>>>> > I'm too lazy to compile this thing in my head. Have you tried >>>>>> > excuting >>>>> the >>>>>> > function from Query Analyzer with the "Show Execution Plan" option >>>>>> > enabled? >>>>>> > That should tell you a lot. Also, I hope you are not calling this >>>>> function >>>>>> > for every row in a large rowset. >>>>>> > >>>>>> > "Ray5531" <Ray***@microsft.com> wrote in message >>>>>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>>>>> >> I'm having a UDF which is quite fast till the last step which >>>>>> >> makes >>>>>> >> executed for 50 seconds on a single execution.In the last step I >>>>>> >> have >>>>>> >> used >>>>>> > a >>>>>> >> right join which makes a very slow ,IS there somebody can give >>>>>> >> some >>>>> hints >>>>>> > to >>>>>> >> enhance the last join.Thanks alot >>>>>> >> >>>>>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>>>>> >> @Affiliate_ID NUMERIC(9), >>>>>> >> @IF_FileType VARCHAR(50), >>>>>> >> @CurrentAcademicYear NUMERIC(5), >>>>>> >> @CurrentSeesion NUMERIC(5), >>>>>> >> @Enrollment_Type INT) >>>>>> >> RETURNS INT AS >>>>>> >> BEGIN >>>>>> >> ------DECLARATION >>>>>> >> >>>>>> >> DECLARE @ClientType VARCHAR(2) >>>>>> >> DECLARE @Counter1 INT >>>>>> >> DECLARE @Counter2 INT >>>>>> >> DECLARE @MissingEnrollmentNumbers INT >>>>>> >> -- VARIABLE TABLES >>>>>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID RIC(9)) --Contains >>>>>> >> all >>>>>> >> the active clients for an specific member Type (ST or MB) for an >>>>> specifc >>>>>> >> affiliate >>>>>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>>>>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>>>>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>>>>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments >>>>>> >> for >>>>>> > clients >>>>>> >> in above temp table >>>>>> >> >>>>>> >> -------STEP 1 >>>>>> >> >>>>>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that >>>>>> >> the >>>>>> > MEMBER >>>>>> >> keyword is part of the word >>>>>> >> >>>>>> >> BEGIN >>>>>> >> SET @ClientType='MB' >>>>>> >> END >>>>>> >> >>>>>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >>>>> STUDENT >>>>>> >> keyword is part of the word >>>>>> >> >>>>>> >> BEGIN >>>>>> >> SET @ClientType='ST' >>>>>> >> END >>>>>> >> >>>>>> >> ELSE >>>>>> >> BEGIN >>>>>> >> SET @ClientType='NF' --This simply means that ClientType(MB or >>>>>> >> ST) >>>>>> > could >>>>>> >> not be extracted based on this information >>>>>> >> END >>>>>> >> >>>>>> >> -------STEP 2 >>>>>> >> >>>>>> >> IF @ClientType='NF' >>>>>> >> BEGIN >>>>>> >> SET @MissingEnrollmentNumbers =-1 >>>>>> >> END >>>>>> >> ELSE >>>>>> >> BEGIN >>>>>> >> >>>>>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" >>>>>> >> OR >>>>>> >> "ST" ) >>>>>> >> FOR THE SPECIFIC AFFILIATE >>>>>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>>>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>>>> >> dbo.USER_STATUS T2 ON >>>>>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>>>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>>>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>>>>> >> T1.USER_TYPE_CODE=@ClientType AND >>>>>> >> T2.ACTIVE_STATUS='Y' >>>>>> >> >>>>>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>>>>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >>>>> TABLE(@TBActiveClients) >>>>>> >> >>>>>> >> BEGIN >>>>>> >> >>>>>> >> INSERT INTO >>>>>> >> >>>>>> > >>>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>>> > SESSION,INTERNAL_ID) >>>>>> >> SELECT >>>>>> >> >>>>>> > >>>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>>> > _ID >>>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>>> >> ON >>>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>>>>> >> @CurrentSeesion) >>>>>> >> >>>>>> >> END >>>>>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS FOR >>>>> ACTIVE >>>>>> >> CLIENTS OF THE ABOVE TEMP TABLE >>>>>> >> >>>>>> >> BEGIN >>>>>> >> >>>>>> >> INSERT INTO >>>>>> >> >>>>>> > >>>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>>> > SESSION,INTERNAL_ID) >>>>>> >> SELECT >>>>>> >> >>>>>> > >>>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>>> > _ID >>>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>>> >> ON >>>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>>>>> >> @CurrentSeesion) >>>>>> >> >>>>>> >> >>>>>> >> END >>>>>> >> >>>>>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>>>>> >> @TBEnrollmentsForActiveClients >>>>>> >> >>>>>> >> >>>>>> >> ***********Problem is this >>>>>> >> join*************************************************************** >>>>>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>>>>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>>>>> >> >>>>>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM >>>>>> >> dbo.IF_C4TRANSFORM T1 >>>>>> >> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>>>> >> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>>>> >> T1.COURSE_CODE=T2.COURSE_CODE AND >>>>>> >> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>>>> >> T1.INTERNAL_ID=T2.INTERNAL_ID >>>>>> >> WHERE T1.FILEID = @FileID >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> SET @MissingEnrollmentNumbers= @Counter1 - @Counter2 --NUMBER >>>>>> >> OF >>>>>> >> ENROLLMENT FOR THIS ACTIVE TYPE(MB OR ST) FOR SPECIFIC AFFILIATE >>>>>> >> AND >>>>>> >> (CURRENT/ FUTURE OR HISTORY ) >>>>>> >> END >>>>>> >> >>>>>> >> >>>>>> >> RETURN @MissingEnrollmentNumbers >>>>>> >> END >>>>>> >> >>>>>> >> >>>>>> > >>>>>> > >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > That first step - is it scanning or seeking an Index or Clustered Index, or is it scanning the table? Show quote "Ray5531" <Ray***@microsft.com> wrote in message news:evcVi20VFHA.2616@TK2MSFTNGP14.phx.gbl... > All stepes cost are so imperceptible,the only one which is huge in campe > with the others is the first one and it is returning 74000 records which I > need them all. > > Thanks > "Michael C#" <x**@abcdef.com> wrote in message > news:PGRge.10825$NZ1.5383@fe09.lga... >> Look at your graphical query execution plan in QA and see how many rows >> are being returned in the various steps, and the relative cost of each >> step in the query. There may be a way to cut down the number of rows or >> re-write the query a little to speed it up based on the execution plan. >> >> "Ray5531" <Ray***@microsft.com> wrote in message >> news:%23qbO3%23zVFHA.2520@TK2MSFTNGP09.phx.gbl... >>>I did the update with full scan and the time got increased to 80% :-) >>> Thanks >>> "Michael C#" <ho***@boutdat.com> wrote in message >>> news:Oq2LexzVFHA.616@TK2MSFTNGP12.phx.gbl... >>>> Try updating statistics with FULLSCAN, see if it makes a difference. >>>> >>>> "Ray5531" <Ray***@microsft.com> wrote in message >>>> news:OtWiitzVFHA.2496@TK2MSFTNGP10.phx.gbl... >>>>> Enabled Estimated Execution plan and here is th query cost relative >>>>> to the batch for different parts: >>>>> >>>>> A) 95% ??????? <--- this one is hurting the performace ,it is nagging >>>>> about my primary key on CLIENT table which is aclustered index.I >>>>> dropped the index and recreate it-no difference.I deleted the index >>>>> still the same!!!! >>>>> >>>>> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>>> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>>> dbo.USER_STATUS T2 ON >>>>> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>>> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>>> WHERE T1.AFFILIATE_ID = 3 AND >>>>> T1.USER_TYPE_CODE='ST' AND >>>>> T2.ACTIVE_STATUS='Y' >>>>> >>>>> >>>>> B) 0.95% <---This one after putting index is very good now >>>>> >>>>> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM dbo.IF_C4TRANSFORM T1 >>>>> RIGHT OUTER JOIN @TBEnrollmentsForActiveClients T2 ON >>>>> T1.SESSION_CODE=T2.COURSE_SESSION AND >>>>> T1.COURSE_CODE=T2.COURSE_CODE AND >>>>> T1.ACADEMIC_YEAR=T2.COURSE_YEAR AND >>>>> T1.INTERNAL_ID=T2.INTERNAL_ID >>>>> WHERE T1.FILEID = 1 >>>>> >>>>> >>>>> >>>>> "JT" <some***@microsoft.com> wrote in message >>>>> news:OYBQBdyVFHA.3840@tk2msftngp13.phx.gbl... >>>>>> You be the judge, it's your app. What is the query execution plan >>>>>> telling >>>>>> you? >>>>>> >>>>>> "Ray5531" <Ray***@microsft.com> wrote in message >>>>>> news:OPYniByVFHA.628@tk2msftngp13.phx.gbl... >>>>>>> That function might be called for maximum 100 records:-( >>>>>>> >>>>>>> Is that disaster? >>>>>>> >>>>>>> Thanks >>>>>>> "JT" <some***@microsoft.com> wrote in message >>>>>>> news:uFpBm4xVFHA.3188@TK2MSFTNGP09.phx.gbl... >>>>>>> > I'm too lazy to compile this thing in my head. Have you tried >>>>>>> > excuting >>>>>> the >>>>>>> > function from Query Analyzer with the "Show Execution Plan" option >>>>>>> > enabled? >>>>>>> > That should tell you a lot. Also, I hope you are not calling this >>>>>> function >>>>>>> > for every row in a large rowset. >>>>>>> > >>>>>>> > "Ray5531" <Ray***@microsft.com> wrote in message >>>>>>> > news:uWFaiYxVFHA.2692@TK2MSFTNGP15.phx.gbl... >>>>>>> >> I'm having a UDF which is quite fast till the last step which >>>>>>> >> makes >>>>>>> >> executed for 50 seconds on a single execution.In the last step I >>>>>>> >> have >>>>>>> >> used >>>>>>> > a >>>>>>> >> right join which makes a very slow ,IS there somebody can give >>>>>>> >> some >>>>>> hints >>>>>>> > to >>>>>>> >> enhance the last join.Thanks alot >>>>>>> >> >>>>>>> >> CREATE FUNCTION dbo.GetC4MissingEnrollments (@FileID NUMERIC(9), >>>>>>> >> @Affiliate_ID NUMERIC(9), >>>>>>> >> @IF_FileType VARCHAR(50), >>>>>>> >> @CurrentAcademicYear NUMERIC(5), >>>>>>> >> @CurrentSeesion NUMERIC(5), >>>>>>> >> @Enrollment_Type INT) >>>>>>> >> RETURNS INT AS >>>>>>> >> BEGIN >>>>>>> >> ------DECLARATION >>>>>>> >> >>>>>>> >> DECLARE @ClientType VARCHAR(2) >>>>>>> >> DECLARE @Counter1 INT >>>>>>> >> DECLARE @Counter2 INT >>>>>>> >> DECLARE @MissingEnrollmentNumbers INT >>>>>>> >> -- VARIABLE TABLES >>>>>>> >> DECLARE @TBActiveClients TABLE(INTERNAL_ID >>>>>>> >> C(9)) --Contains >>>>>>> >> all >>>>>>> >> the active clients for an specific member Type (ST or MB) for an >>>>>> specifc >>>>>>> >> affiliate >>>>>>> >> DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID >>>>>>> >> NUMERIC(9),COURSE_CODE VARCHAR(3), >>>>>>> >> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), >>>>>>> >> INTERNAL_ID NUMERIC(9)) --Contains all enrollments >>>>>>> >> for >>>>>>> > clients >>>>>>> >> in above temp table >>>>>>> >> >>>>>>> >> -------STEP 1 >>>>>>> >> >>>>>>> >> IF @IF_FileType LIKE '%MEMBER%' --This means that >>>>>>> >> the >>>>>>> > MEMBER >>>>>>> >> keyword is part of the word >>>>>>> >> >>>>>>> >> BEGIN >>>>>>> >> SET @ClientType='MB' >>>>>>> >> END >>>>>>> >> >>>>>>> >> ELSE IF @IF_FileType LIKE '%STUDENT%' --This means that the >>>>>> STUDENT >>>>>>> >> keyword is part of the word >>>>>>> >> >>>>>>> >> BEGIN >>>>>>> >> SET @ClientType='ST' >>>>>>> >> END >>>>>>> >> >>>>>>> >> ELSE >>>>>>> >> BEGIN >>>>>>> >> SET @ClientType='NF' --This simply means that ClientType(MB >>>>>>> >> or ST) >>>>>>> > could >>>>>>> >> not be extracted based on this information >>>>>>> >> END >>>>>>> >> >>>>>>> >> -------STEP 2 >>>>>>> >> >>>>>>> >> IF @ClientType='NF' >>>>>>> >> BEGIN >>>>>>> >> SET @MissingEnrollmentNumbers =-1 >>>>>>> >> END >>>>>>> >> ELSE >>>>>>> >> BEGIN >>>>>>> >> >>>>>>> >> --POPULATES THE ACTIVE CLIENTS (OF AN SPECIFIC TYPE -"MB" >>>>>>> >> OR >>>>>>> >> "ST" ) >>>>>>> >> FOR THE SPECIFIC AFFILIATE >>>>>>> >> INSERT INTO @TBActiveClients(INTERNAL_ID) >>>>>>> >> SELECT T1.INTERNAL_ID FROM dbo.CLIENT T1 LEFT OUTER JOIN >>>>>>> >> dbo.USER_STATUS T2 ON >>>>>>> >> T1.USER_TYPE_CODE=T2.USER_TYPE_CODE AND >>>>>>> >> T1.USER_STATUS_CODE =T2.USER_STATUS_CODE >>>>>>> >> WHERE T1.AFFILIATE_ID = @Affiliate_ID AND >>>>>>> >> T1.USER_TYPE_CODE=@ClientType AND >>>>>>> >> T2.ACTIVE_STATUS='Y' >>>>>>> >> >>>>>>> >> IF @Enrollment_Type =1 --POPULATES THE **CURRENT/FUTURE** >>>>>>> >> ENROLLMENTS FOR ACTIVE CLIENTS OF THE ABOVE TEMP >>>>>> TABLE(@TBActiveClients) >>>>>>> >> >>>>>>> >> BEGIN >>>>>>> >> >>>>>>> >> INSERT INTO >>>>>>> >> >>>>>>> > >>>>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>>>> > SESSION,INTERNAL_ID) >>>>>>> >> SELECT >>>>>>> >> >>>>>>> > >>>>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>>>> > _ID >>>>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>>>> >> ON >>>>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>>>> >> WHERE T3.COURSE_YEAR > @CurrentAcademicYear OR >>>>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION >= >>>>>>> >> @CurrentSeesion) >>>>>>> >> >>>>>>> >> END >>>>>>> >> ELSE --POPULATES THE **HISTORY** ENROLLMENTS >>>>>>> >> FOR >>>>>> ACTIVE >>>>>>> >> CLIENTS OF THE ABOVE TEMP TABLE >>>>>>> >> >>>>>>> >> BEGIN >>>>>>> >> >>>>>>> >> INSERT INTO >>>>>>> >> >>>>>>> > >>>>>> @TBEnrollmentsForActiveClients(ENROLLMENT_ID,COURSE_CODE,COURSE_YEAR,COURSE_ >>>>>>> > SESSION,INTERNAL_ID) >>>>>>> >> SELECT >>>>>>> >> >>>>>>> > >>>>>> T3.ENROLLMENT_ID,T3.COURSE_CODE,T3.COURSE_YEAR,T3.COURSE_SESSION,T3.INTERNAL >>>>>>> > _ID >>>>>>> >> FROM @TBActiveClients T4 INNER JOIN dbo.ENROLLMENT T3 >>>>>>> >> ON >>>>>>> >> T4.INTERNAL_ID = T3.INTERNAL_ID >>>>>>> >> WHERE T3.COURSE_YEAR < @CurrentAcademicYear OR >>>>>>> >> (T3.COURSE_YEAR = @CurrentAcademicYear AND T3.COURSE_SESSION < >>>>>>> >> @CurrentSeesion) >>>>>>> >> >>>>>>> >> >>>>>>> >> END >>>>>>> >> >>>>>>> >> SELECT @Counter1 = COUNT (ENROLLMENT_ID) FROM >>>>>>> >> @TBEnrollmentsForActiveClients >>>>>>> >> >>>>>>> >> >>>>>>> >> ***********Problem is this >>>>>>> >> join*************************************************************** >>>>>>> >> ----FIND OUT HOW MANY OF RECORDS OF THE above TEMP TABLE >>>>>>> >> (@TBEnrollmentsForActiveClients) ABOVE EXIST IN C4 WORKING TABLE >>>>>>> >> >>>>>>> >> SELECT @Counter2= COUNT(T2.INTERNAL_ID) FROM >>>>>>> >> dbo.IF_C4TRANSFORM T1 >>>>>>> >> RIGHT OUTER JOIN | |||||||||||||||||||||||
