?
Solved

Refactor the SQL Query

Posted on 2011-10-10
4
Medium Priority
?
232 Views
Last Modified: 2012-05-12
The below query is taking high reads and resources . please help me to change the same to help reduce performance issue


SOS
Thanks

INSERT INTO #GradeBookTemp(UserID,UserName,StudentID,LoginName,LastName,IsActive,IsDeleted,IsDemoUser,FirstName,GBColumnID,GBID,Grades,GradeStatus )
		
		SELECT G.UserID,G.UserName,G.StudentID,G.LoginName,G.LastName,G.IsActive,G.IsDeleted,G.IsDemoUser,G.FirstName,C.GBColumnID,GB.GBID
		,
		
		CASE C.DisplayFormat WHEN 3 THEN 
		(CASE WHEN GB.PercentageMarksDeducted IS NOT NULL AND GB.PercentageMarksDeducted > 0 
		 THEN Convert(NVARCHAR(20),Convert(FLOAT,round(((gb.GBColumnValue / 100) * gb.MaxScore),1))) +'/' + Convert(NVARCHAR(20),
		 Convert(FLOAT,round(gb.MaxScore,1)))
		 ELSE Convert(NVARCHAR(20),Convert(FLOAT,round(gb.UserScore,1))) +'/' + Convert(NVARCHAR(20),Convert(FLOAT,round(gb.MaxScore,1))) 
		 END)	
		WHEN 2 THEN (SELECT CASE WHEN C.GBSchemaID IS NULL THEN Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) 
		ELSE (SELECT top 1 isnull(GradeValue,'') FROM PegTables.tblGBGrades WHERE GBGradeID = gb.GBGradeID AND isdeleted = 0) END )
		ELSE Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) END AS Grades,
		
		(CASE WHEN gb.IsOverridden = 1 THEN @OverriddenEnum ELSE 0 END) | (CASE WHEN gb.IsRemediation = 1 THEN @RemediationEnum ELSE 0 END) |
			(CASE WHEN gb.IsPending = 1 THEN @PendingEnum ELSE 0 END) | (CASE WHEN GB.PercentageMarksDeducted IS NOT NULL AND 
			GB.PercentageMarksDeducted > 0 THEN @GracePeriodEnum ELSE 0 END)| 
 			(CASE WHEN @dateToMatch IS NOT NULL
			THEN (SELECT CASE 
			WHEN @dateToMatch IS NULL OR (DATEDIFF(DAY, Convert(VARCHAR(100),@dateToMatch,113), gb.DateTimeStamp) >= 0 AND 
			(DATEDIFF(hour, Convert(VARCHAR(100),@dateToMatch,113) 
			, gb.DateTimeStamp )>=0 AND DATEDIFF(minute,Convert(VARCHAR(100),@dateToMatch,113) , gb.DateTimeStamp)>=0 AND 
			DATEDIFF(second,Convert(VARCHAR(100),@dateToMatch,113) 
			, gb.DateTimeStamp)>=0 )) THEN @NewGradeEnum
			ELSE 0
			END) 
			ELSE
			@NewGradeEnum END)
			AS GradeStatus
		FROM #GradeBook G , PegTables.tblGB GB 
		JOIN #collectiontable C ON C.GradeableAssetID=GB.GradeableAssetID 
		WHERE GB.UserID=G.UserID AND GB.CourseID = @bintCourseID AND GB.IsDeleted=0 AND C.GBColumnTypeID IN(1,13)

Open in new window

0
Comment
Question by:ExpertHelp79
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 36941716
1.  while using #-tables there is no advantage off using indexes or statistics

2. just picked the case-statement that strucks me the most

		WHEN 2 THEN (SELECT CASE WHEN C.GBSchemaID IS NULL THEN Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) 
		ELSE (SELECT top 1 isnull(GradeValue,'') FROM PegTables.tblGBGrades WHERE GBGradeID = gb.GBGradeID AND isdeleted = 0) END )
		ELSE Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) END AS Grades,

Open in new window

		WHEN 2 THEN (CASE WHEN C.GBSchemaID IS NULL THEN Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) 
//SELECT has no meaning, no idea if its off any performance meaning
		ELSE isnull(isnull(gb.GradeValue,  (SELECT top 1 isnull(GradeValue,'') FROM PegTables.tblGBGrades WHERE GBGradeID = gb.GBGradeID AND isdeleted = 0 and GradeValue is not null),'')) END )
// see below
		ELSE Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) END AS Grades,
// no change

Open in new window

In this case statement for each record you need the GradeValue you read again, depending on the number of times it occurs you maybe could move it to the join. But because the filter is off a table that is already in your join -> try first if there is already a value -> and no read has to occur
The way I changed it by using a double ISNULL
- if GradeValue is NULL - read the first non null value from the same grade
- if no non null value is found still make it ''

But because you now just randomly find the first, I even think the whole select for Gradevalue can just be replaced by
		ELSE isnull(gb.GradeValue,'') END )

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 36941736
Thanks a lot for the reply ... i am able to get the relations but can you fit the changes in my query above  so that i can try implementing the same
0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 36941754
Just replace this part from  'WHEN 2'  to 'Grades' in place off that piece in your query.  That's not to difficult is it.
		WHEN 2 THEN (CASE WHEN C.GBSchemaID IS NULL THEN Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) 
		ELSE isnull(gb.GradeValue,'') END 
		ELSE Convert(NVARCHAR(20),Convert(FLOAT,round(gb.GBColumnValue,1))) END AS Grades,

Open in new window

0
 
LVL 2

Author Closing Comment

by:ExpertHelp79
ID: 36941910
Increased performance by 25%
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question