Link to home
Start Free TrialLog in
Avatar of Chandu M
Chandu M

asked on

Performance issue in stored procedure in SQL Server 2012

I am new to SQL server, I have a Stored procedure that returning only almost 1733 rows but taking long time to execute.Please find the stored procedure below and help me to get increase the performance I am using MS SQL Server 2012 I am unable to attache test result and execution plan
TEST_SP.txt
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
We will definitely need to see the Query Execution Plan.
At the first sight I can see issues with subselects so I've transformed them in INNER JOIN. Check if with the following change the SP will run faster (only the changed block):
IF @MASTEREDFROM IS NULL
BEGIN
	INSERT INTO #FINALRESULT
	SELECT TEMP.USERID AS SUPERUSERID0,
		TEMP2.DISPLAYUSERID,
		TEMP2.LASTNAME +' '+ TEMP2.FIRSTNAME + ' (' + TEMP2.DISPLAYUSERID  + ')' AS SUPERUSERID,
		CAST(COUNT(DISTINCT UC.USERID) AS NUMERIC(9,2)) AS TOTALUSERS,
		CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))  AS TOTALCOURSE,
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS IS NULL THEN 'NOTSTARTED' END) AS NUMERIC(9,2))  AS 'NOOFNOTSTARTED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS IS NULL THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))* 100  AS NUMERIC(9,2)) AS 'NOOFNOTSTARTEDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 1 THEN 'STARTED' END)  AS NUMERIC(9,2)) AS 'NOOFSTARTED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 1 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2)) *100  AS NUMERIC(9,2)) AS 'NOOFSTARTEDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 2 THEN 'INPROGRESS' END) AS NUMERIC(9,2))  AS 'NOOFINPROGRESS',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 2 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID)AS NUMERIC(9,2))*100  AS NUMERIC(9,2)) AS 'NOOFINPROGRESSPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 3 THEN 'MASTERED' END)  AS NUMERIC(9,2)) AS 'MASTCRSCNT',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 3 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))*100  AS NUMERIC(9,2)) AS 'MASTPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 4 THEN 'EXPIRED' END)  AS NUMERIC(9,2)) AS 'NOOFEXPIRED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 4 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))* 100  AS NUMERIC(9,2)) AS 'NOOFEXPIREDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 5 THEN 'COMPLETED' END)  AS NUMERIC(9,2)) AS COMPLETED,
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 5 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID)AS NUMERIC(9,2))*100 AS NUMERIC(9,2)) AS COMPLETEDPERCENTAGE
	FROM USERCOURSES UC
		INNER JOIN USERS U ON U.USERID = UC.USERID
		INNER JOIN #USERID TEMP ON TEMP.USERID = U.SUPERUSERID
		INNER JOIN #USERID TEMP2 ON U.USERID = TEMP2.USERID
		INNER JOIN [VW_RPTCOURSES] C ON C.COURSEID = UC.COURSEID
		INNER JOIN USERGROUPS1 UG1 ON UG1.USERID = U.USERID
		LEFT JOIN USERCOURSEINFO UCI ON UCI.USERID = UC.USERID AND UCI.COURSEID = UC.COURSEID 
		INNER JOIN TRAININGSUITES TS ON TS.SUITEID = C.SUITEID
		LEFT JOIN DBO.FN_SPLIT(@USERID,',') USERID ON USERID.ELEMENT = U.SUPERUSERID
		LEFT JOIN DBO.FN_SPLIT(@GROUP1,',') GROUP1 ON GROUP1.ELEMENT = UG1.GROUP1
		LEFT JOIN DBO.FN_SPLIT(@GROUP2,',') GROUP2 ON GROUP2.ELEMENT = UG1.GROUP2
		LEFT JOIN DBO.FN_SPLIT(@GROUP3,',') GROUP3 ON GROUP3.ELEMENT = UG1.GROUP3
		LEFT JOIN DBO.FN_SPLIT(@GROUP4,',') GROUP4 ON GROUP4.ELEMENT = UG1.GROUP4
		LEFT JOIN DBO.FN_SPLIT(@GROUP5,',') GROUP5 ON GROUP5.ELEMENT = UG1.GROUP5
		LEFT JOIN DBO.FN_SPLIT(@GROUP6,',') GROUP6 ON GROUP6.ELEMENT = UG1.GROUP6
		LEFT JOIN DBO.FN_SPLIT(@GROUP7,',') GROUP7 ON GROUP7.ELEMENT = UG1.GROUP7
		LEFT JOIN DBO.FN_SPLIT(@GROUP8,',') GROUP8 ON GROUP8.ELEMENT = UG1.GROUP8
		LEFT JOIN DBO.FN_SPLIT(@GROUP9,',') GROUP9 ON GROUP9.ELEMENT = UG1.GROUP9
		LEFT JOIN DBO.FN_SPLIT(@GROUP10,',') GROUP10 ON GROUP10.ELEMENT = UG1.GROUP10
		LEFT JOIN DBO.FN_SPLIT(@GROUP11,',') GROUP11 ON GROUP11.ELEMENT = UG1.GROUP11
		LEFT JOIN DBO.FN_SPLIT(@GROUP12,',') GROUP12 ON GROUP12.ELEMENT = UG1.GROUP12
		LEFT JOIN DBO.FN_SPLIT(@GROUP13,',') GROUP13 ON GROUP13.ELEMENT = UG1.GROUP13
		LEFT JOIN DBO.FN_SPLIT(@GROUP14,',') GROUP14 ON GROUP14.ELEMENT = UG1.GROUP14
		LEFT JOIN DBO.FN_SPLIT(@GROUP15,',') GROUP15 ON GROUP15.ELEMENT = UG1.GROUP15
		LEFT JOIN DBO.FN_SPLIT(@COURSEID,',')COURSEID ON COURSEID.ELEMENT = C.DISPLAYCOURSEID
		LEFT JOIN DBO.FN_SPLIT(@SUITEID,',')SUITEID ON SUITEID.ELEMENT =C.SUITEID
		LEFT JOIN DBO.FN_SPLIT(@ISREQUIRED,',') ISREQUIRED ON ISREQUIRED.ELEMENT = UC.ISREQUIRED
		LEFT JOIN DBO.FN_SPLIT(@ACTIVE,',') ACTIVE ON ACTIVE.ELEMENT = U.ACTIVE
		LEFT JOIN DBO.FN_SPLIT(@CRSACTIVE,',') CRSACTIVE ON CRSACTIVE.ELEMENT = C.ACTIVE
		LEFT JOIN DBO.FN_SPLIT(@COURSETYPEID,',') CRSTYPEID ON CRSTYPEID.ELEMENT = C.COURSETYPEID
	WHERE TEMP.USERID = CASE WHEN @USERID <> '' THEN USERID.ELEMENT ELSE TEMP.USERID END
		AND ISNULL(UG1.GROUP1,0) = CASE WHEN @GROUP1 <> '' THEN GROUP1.ELEMENT ELSE ISNULL(UG1.GROUP1,0) END
		AND ISNULL(UG1.GROUP2,0) = CASE WHEN @GROUP2 <> '' THEN GROUP2.ELEMENT ELSE ISNULL(UG1.GROUP2,0) END
		AND ISNULL(UG1.GROUP3,0) = CASE WHEN @GROUP3 <> '' THEN GROUP3.ELEMENT ELSE ISNULL(UG1.GROUP3,0) END
		AND ISNULL(UG1.GROUP4,0) = CASE WHEN @GROUP4 <> '' THEN GROUP4.ELEMENT ELSE ISNULL(UG1.GROUP4,0) END
		AND ISNULL(UG1.GROUP5,0) = CASE WHEN @GROUP5 <> '' THEN GROUP5.ELEMENT ELSE ISNULL(UG1.GROUP5,0) END
		AND ISNULL(UG1.GROUP6,0) = CASE WHEN @GROUP6 <> '' THEN GROUP6.ELEMENT ELSE ISNULL(UG1.GROUP6,0) END
		AND ISNULL(UG1.GROUP7,0) = CASE WHEN @GROUP7 <> '' THEN GROUP7.ELEMENT ELSE ISNULL(UG1.GROUP7,0) END
		AND ISNULL(UG1.GROUP8,0) = CASE WHEN @GROUP8 <> '' THEN GROUP8.ELEMENT ELSE ISNULL(UG1.GROUP8,0) END
		AND ISNULL(UG1.GROUP9,0) = CASE WHEN @GROUP9 <> '' THEN GROUP9.ELEMENT ELSE ISNULL(UG1.GROUP9,0) END
		AND ISNULL(UG1.GROUP10,0) = CASE WHEN @GROUP10 <> '' THEN GROUP10.ELEMENT ELSE ISNULL(UG1.GROUP10,0) END
		AND ISNULL(UG1.GROUP11,0) = CASE WHEN @GROUP11 <> '' THEN GROUP11.ELEMENT ELSE ISNULL(UG1.GROUP11,0) END
		AND ISNULL(UG1.GROUP12,0) = CASE WHEN @GROUP12 <> '' THEN GROUP12.ELEMENT ELSE ISNULL(UG1.GROUP12,0) END
		AND ISNULL(UG1.GROUP13,0) = CASE WHEN @GROUP13 <> '' THEN GROUP13.ELEMENT ELSE ISNULL(UG1.GROUP13,0) END
		AND ISNULL(UG1.GROUP14,0) = CASE WHEN @GROUP14 <> '' THEN GROUP14.ELEMENT ELSE ISNULL(UG1.GROUP14,0) END
		AND ISNULL(UG1.GROUP15,0) = CASE WHEN @GROUP15 <> '' THEN GROUP15.ELEMENT ELSE ISNULL(UG1.GROUP15,0) END
		AND UC.ISREQUIRED = CASE WHEN @ISREQUIRED <> '' THEN ISREQUIRED.ELEMENT ELSE UC.ISREQUIRED END
		AND U.ACTIVE = CASE WHEN @ACTIVE <> '' THEN ACTIVE.ELEMENT ELSE U.ACTIVE END
		AND C.ACTIVE = CASE WHEN @CRSACTIVE <> '' THEN CRSACTIVE.ELEMENT ELSE C.ACTIVE END
		AND C.COURSETYPEID = CASE WHEN @COURSETYPEID <> '' THEN CRSTYPEID.ELEMENT ELSE C.COURSETYPEID END
		AND C.DISPLAYCOURSEID = CASE WHEN @COURSEID <> '' THEN COURSEID.ELEMENT ELSE C.DISPLAYCOURSEID END
		AND C.SUITEID = CASE WHEN @SUITEID <> '' THEN SUITEID.ELEMENT ELSE C.SUITEID END
		AND UC.DELFLAG=0
		AND U.DELFLAG=0 AND C.DELFLAG=0 AND UC.ISSCHEDULE IN(1,3)  
		AND CONVERT(DATETIME,UC.ASSIGNEDDATE,101) BETWEEN CONVERT(DATETIME,@FROMDATE,101) AND CONVERT(DATETIME,@TODATE,101)
	--AND CONVERT(DATETIME,ISNULL(ISNULL(MASTEREDDATE,TESTCOMPLETEDDATE),ISNULL(@MASTEREDFROM,'1940-01-01')),101) BETWEEN CONVERT(DATETIME,ISNULL(@MASTEREDFROM,'1940-01-01'),101) AND CONVERT(DATETIME,ISNULL(@MASTEREDTO,'3000-01-01'),101)
	GROUP BY TEMP.USERID ORDER BY SUPERUSERID
END
ELSE
BEGIN
	INSERT INTO #FINALRESULT
	SELECT TEMP.USERID AS SUPERUSERID0,
		TEMP2.DISPLAYUSERID,
		TEMP2.LASTNAME +' '+ TEMP2.FIRSTNAME + ' (' + TEMP2.DISPLAYUSERID  + ')' AS SUPERUSERID,
		CAST(COUNT(DISTINCT UC.USERID) AS NUMERIC(9,2)) AS TOTALUSERS,
		COUNT(UC.COURSEID) AS TOTALCOURSE,
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS IS NULL THEN 'NOTSTARTED' END) AS NUMERIC(9,2))  AS 'NOOFNOTSTARTED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS IS NULL THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))* 100  AS NUMERIC(9,2)) AS 'NOOFNOTSTARTEDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 1 THEN 'STARTED' END)  AS NUMERIC(9,2)) AS 'NOOFSTARTED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 1 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2)) *100  AS NUMERIC(9,2)) AS 'NOOFSTARTEDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 2 THEN 'INPROGRESS' END) AS NUMERIC(9,2))  AS 'NOOFINPROGRESS',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 2 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID)AS NUMERIC(9,2))*100  AS NUMERIC(9,2)) AS 'NOOFINPROGRESSPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 3 THEN 'MASTERED' END)  AS NUMERIC(9,2)) AS 'MASTCRSCNT',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 3 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))*100  AS NUMERIC(9,2)) AS 'MASTPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 4 THEN 'EXPIRED' END)  AS NUMERIC(9,2)) AS 'NOOFEXPIRED',
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 4 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID) AS NUMERIC(9,2))* 100  AS NUMERIC(9,2)) AS 'NOOFEXPIREDPERCENT',
		CAST(COUNT(CASE WHEN UCI.COURSESTATUS = 5 THEN 'COMPLETED' END)  AS NUMERIC(9,2)) AS COMPLETED,
		CAST(SUM(CASE WHEN UCI.COURSESTATUS = 5 THEN 1 ELSE 0 END)/CAST(COUNT(UC.COURSEID)AS NUMERIC(9,2))*100 AS NUMERIC(9,2)) AS COMPLETEDPERCENTAGE
	FROM USERCOURSES UC
		INNER JOIN USERS U ON U.USERID = UC.USERID
		INNER JOIN #USERID TEMP ON TEMP.USERID = U.SUPERUSERID
		INNER JOIN #USERID TEMP2 ON U.USERID = TEMP2.USERID
		INNER JOIN [VW_RPTCOURSES] C ON C.COURSEID = UC.COURSEID
		INNER JOIN USERGROUPS1 UG1 ON UG1.USERID = U.USERID
		LEFT JOIN USERCOURSEINFO UCI ON UCI.USERID = UC.USERID AND UCI.COURSEID = UC.COURSEID 
		INNER JOIN TRAININGSUITES TS ON TS.SUITEID = C.SUITEID
		LEFT JOIN DBO.FN_SPLIT(@USERID,',') USERID ON USERID.ELEMENT = U.SUPERUSERID
		LEFT JOIN DBO.FN_SPLIT(@GROUP1,',') GROUP1 ON GROUP1.ELEMENT = UG1.GROUP1
		LEFT JOIN DBO.FN_SPLIT(@GROUP2,',') GROUP2 ON GROUP2.ELEMENT = UG1.GROUP2
		LEFT JOIN DBO.FN_SPLIT(@GROUP3,',') GROUP3 ON GROUP3.ELEMENT = UG1.GROUP3
		LEFT JOIN DBO.FN_SPLIT(@GROUP4,',') GROUP4 ON GROUP4.ELEMENT = UG1.GROUP4
		LEFT JOIN DBO.FN_SPLIT(@GROUP5,',') GROUP5 ON GROUP5.ELEMENT = UG1.GROUP5
		LEFT JOIN DBO.FN_SPLIT(@GROUP6,',') GROUP6 ON GROUP6.ELEMENT = UG1.GROUP6
		LEFT JOIN DBO.FN_SPLIT(@GROUP7,',') GROUP7 ON GROUP7.ELEMENT = UG1.GROUP7
		LEFT JOIN DBO.FN_SPLIT(@GROUP8,',') GROUP8 ON GROUP8.ELEMENT = UG1.GROUP8
		LEFT JOIN DBO.FN_SPLIT(@GROUP9,',') GROUP9 ON GROUP9.ELEMENT = UG1.GROUP9
		LEFT JOIN DBO.FN_SPLIT(@GROUP10,',') GROUP10 ON GROUP10.ELEMENT = UG1.GROUP10
		LEFT JOIN DBO.FN_SPLIT(@GROUP11,',') GROUP11 ON GROUP11.ELEMENT = UG1.GROUP11
		LEFT JOIN DBO.FN_SPLIT(@GROUP12,',') GROUP12 ON GROUP12.ELEMENT = UG1.GROUP12
		LEFT JOIN DBO.FN_SPLIT(@GROUP13,',') GROUP13 ON GROUP13.ELEMENT = UG1.GROUP13
		LEFT JOIN DBO.FN_SPLIT(@GROUP14,',') GROUP14 ON GROUP14.ELEMENT = UG1.GROUP14
		LEFT JOIN DBO.FN_SPLIT(@GROUP15,',') GROUP15 ON GROUP15.ELEMENT = UG1.GROUP15
		LEFT JOIN DBO.FN_SPLIT(@COURSEID,',')COURSEID ON COURSEID.ELEMENT = C.DISPLAYCOURSEID
		LEFT JOIN DBO.FN_SPLIT(@SUITEID,',')SUITEID ON SUITEID.ELEMENT =C.SUITEID
		LEFT JOIN DBO.FN_SPLIT(@ISREQUIRED,',') ISREQUIRED ON ISREQUIRED.ELEMENT = UC.ISREQUIRED
		LEFT JOIN DBO.FN_SPLIT(@ACTIVE,',') ACTIVE ON ACTIVE.ELEMENT = U.ACTIVE
		LEFT JOIN DBO.FN_SPLIT(@CRSACTIVE,',') CRSACTIVE ON CRSACTIVE.ELEMENT = C.ACTIVE
		LEFT JOIN DBO.FN_SPLIT(@COURSETYPEID,',') CRSTYPEID ON CRSTYPEID.ELEMENT = C.COURSETYPEID
	WHERE TEMP.USERID = CASE WHEN @USERID <> '' THEN USERID.ELEMENT ELSE TEMP.USERID END
		AND ISNULL(UG1.GROUP1,0) = CASE WHEN @GROUP1 <> '' THEN GROUP1.ELEMENT ELSE ISNULL(UG1.GROUP1,0) END
		AND ISNULL(UG1.GROUP2,0) = CASE WHEN @GROUP2 <> '' THEN GROUP2.ELEMENT ELSE ISNULL(UG1.GROUP2,0) END
		AND ISNULL(UG1.GROUP3,0) = CASE WHEN @GROUP3 <> '' THEN GROUP3.ELEMENT ELSE ISNULL(UG1.GROUP3,0) END
		AND ISNULL(UG1.GROUP4,0) = CASE WHEN @GROUP4 <> '' THEN GROUP4.ELEMENT ELSE ISNULL(UG1.GROUP4,0) END
		AND ISNULL(UG1.GROUP5,0) = CASE WHEN @GROUP5 <> '' THEN GROUP5.ELEMENT ELSE ISNULL(UG1.GROUP5,0) END
		AND ISNULL(UG1.GROUP6,0) = CASE WHEN @GROUP6 <> '' THEN GROUP6.ELEMENT ELSE ISNULL(UG1.GROUP6,0) END
		AND ISNULL(UG1.GROUP7,0) = CASE WHEN @GROUP7 <> '' THEN GROUP7.ELEMENT ELSE ISNULL(UG1.GROUP7,0) END
		AND ISNULL(UG1.GROUP8,0) = CASE WHEN @GROUP8 <> '' THEN GROUP8.ELEMENT ELSE ISNULL(UG1.GROUP8,0) END
		AND ISNULL(UG1.GROUP9,0) = CASE WHEN @GROUP9 <> '' THEN GROUP9.ELEMENT ELSE ISNULL(UG1.GROUP9,0) END
		AND ISNULL(UG1.GROUP10,0) = CASE WHEN @GROUP10 <> '' THEN GROUP10.ELEMENT ELSE ISNULL(UG1.GROUP10,0) END
		AND ISNULL(UG1.GROUP11,0) = CASE WHEN @GROUP11 <> '' THEN GROUP11.ELEMENT ELSE ISNULL(UG1.GROUP11,0) END
		AND ISNULL(UG1.GROUP12,0) = CASE WHEN @GROUP12 <> '' THEN GROUP12.ELEMENT ELSE ISNULL(UG1.GROUP12,0) END
		AND ISNULL(UG1.GROUP13,0) = CASE WHEN @GROUP13 <> '' THEN GROUP13.ELEMENT ELSE ISNULL(UG1.GROUP13,0) END
		AND ISNULL(UG1.GROUP14,0) = CASE WHEN @GROUP14 <> '' THEN GROUP14.ELEMENT ELSE ISNULL(UG1.GROUP14,0) END
		AND ISNULL(UG1.GROUP15,0) = CASE WHEN @GROUP15 <> '' THEN GROUP15.ELEMENT ELSE ISNULL(UG1.GROUP15,0) END
		AND UC.ISREQUIRED = CASE WHEN @ISREQUIRED <> '' THEN ISREQUIRED.ELEMENT ELSE UC.ISREQUIRED END
		AND U.ACTIVE = CASE WHEN @ACTIVE <> '' THEN ACTIVE.ELEMENT ELSE U.ACTIVE END
		AND C.ACTIVE = CASE WHEN @CRSACTIVE <> '' THEN CRSACTIVE.ELEMENT ELSE C.ACTIVE END
		AND C.COURSETYPEID = CASE WHEN @COURSETYPEID <> '' THEN CRSTYPEID.ELEMENT ELSE C.COURSETYPEID END
		AND C.DISPLAYCOURSEID = CASE WHEN @COURSEID <> '' THEN COURSEID.ELEMENT ELSE C.DISPLAYCOURSEID END
		AND C.SUITEID = CASE WHEN @SUITEID <> '' THEN SUITEID.ELEMENT ELSE C.SUITEID END
		AND UC.DELFLAG=0
		AND U.DELFLAG=0 AND C.DELFLAG=0 AND UC.ISSCHEDULE IN(1,3)  
		AND CONVERT(DATETIME,UC.ASSIGNEDDATE,101) BETWEEN CONVERT(DATETIME,@FROMDATE,101) AND CONVERT(DATETIME,@TODATE,101)
		AND CONVERT(DATETIME,ISNULL(MASTEREDDATE,TESTCOMPLETEDDATE),101) BETWEEN CONVERT(DATETIME,ISNULL(@MASTEREDFROM,'1940-01-01'),101) AND CONVERT(DATETIME,ISNULL(@MASTEREDTO,'3000-01-01'),101)
	GROUP BY TEMP.USERID  ORDER BY SUPERUSERID
END

Open in new window

Avatar of Chandu M
Chandu M

ASKER

Hi Vitor,
Thanks for your comments,
I have executed the by the replacing you changed block and i got the below error

Msg 8120, Level 16, State 1, Procedure ADMIN_TEST_SP, Line 114
Column '#USERID.DISPLAYUSERID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure ADMIN_TEST_SP, Line 192
Column '#USERID.DISPLAYUSERID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please find attachment for execution plan
execution_plan.sqlplan
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chandu M
Chandu M

ASKER

Hi Vitor,

Thanks for your update.
SP was compiled and SP got executed the given the result in 1 min and 17 sec  for 1733 records .This duration is same as previous  and no much betterment in performance.Could you please help me to get for some more better performance.
And thank you so much for your prompt response.Please look into execution plan if possible
Thanks in advance
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Chandu, a feedback will be appreciated.
Cheers
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Recommendation to close this question by accepting the above comments as answer.