May 8, 2009 at 9:25 pm
Hi Friends .......
I have a Table Like This,And I tried one option to get the required result .....
Is There Any other Option to get the same result because my option is performance Issue..
DECLARE @Table TABLE
(
SubmissionId INT,
AssessmentId INT,
AttemptNo INT,
ObtainedMarks INT,
Attempteddate DATETIME,
Prefernce TINYINT -- 1 - Hihest Score,2- LowestScore ,3- FirstAttempt,4- LastAttempt,5 -Avg
)
INSERT INTO @Table VALUES(1,1,1,34,GETDATE(),1)
INSERT INTO @Table VALUES(1,1,2,23,DATEADD(dd,1,GETDATE()),1)
INSERT INTO @Table VALUES(1,1,3,26,DATEADD(dd,2,GETDATE()),1)
INSERT INTO @Table VALUES(2,2,1,10,GETDATE(),3)
INSERT INTO @Table VALUES(2,2,2,34,DATEADD(dd,1,GETDATE()),3)
INSERT INTO @Table VALUES(2,2,3,26,DATEADD(dd,2,GETDATE()),3)
INSERT INTO @Table VALUES(2,2,4,33,DATEADD(dd,2,GETDATE()),3)
INSERT INTO @Table VALUES(3,40,1,75,DATEADD(dd,1,GETDATE()),4)
INSERT INTO @Table VALUES(3,40,2,71,DATEADD(dd,2,GETDATE()),4)
INSERT INTO @Table VALUES(4,45,1,66,DATEADD(dd,1,GETDATE()),2)
INSERT INTO @Table VALUES(4,45,2,55,DATEADD(dd,2,GETDATE()),2)
INSERT INTO @Table VALUES(5,60,1,39,GETDATE(),1)
INSERT INTO @Table VALUES(5,60,2,44,DATEADD(dd,1,GETDATE()),1)
INSERT INTO @Table VALUES(5,60,3,76,DATEADD(dd,2,GETDATE()),1)
SELECT * FROM @Table
Select * from (
SELECT * ,
case
when Prefernce = 1 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks desc)
when Prefernce = 2 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Asc)
when Prefernce = 3 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Asc)
when Prefernce = 4 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Desc)
else
Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Desc)
end As Ranks
FROM @Table
) as S
where Ranks = 1
May 9, 2009 at 3:59 am
This performs better than the current solution
set nocount on
DECLARE @Table TABLE
(
SubmissionId INT,
AssessmentId INT,
AttemptNo INT,
ObtainedMarks INT,
Attempteddate DATETIME,
Prefernce TINYINT -- 1 - Hihest Score,2- LowestScore ,3- FirstAttempt,4- LastAttempt,5 -Avg
)
--INSERT INTO @Table VALUES(1,1,1,34,GETDATE(),1)
--INSERT INTO @Table VALUES(1,1,2,23,DATEADD(dd,1,GETDATE()),1)
--INSERT INTO @Table VALUES(1,1,3,26,DATEADD(dd,2,GETDATE()),1)
--INSERT INTO @Table VALUES(2,2,1,10,GETDATE(),3)
--INSERT INTO @Table VALUES(2,2,2,34,DATEADD(dd,1,GETDATE()),3)
--INSERT INTO @Table VALUES(2,2,3,26,DATEADD(dd,2,GETDATE()),3)
--INSERT INTO @Table VALUES(2,2,4,33,DATEADD(dd,2,GETDATE()),3)
--INSERT INTO @Table VALUES(3,40,1,75,DATEADD(dd,1,GETDATE()),4)
--INSERT INTO @Table VALUES(3,40,2,71,DATEADD(dd,2,GETDATE()),4)
--
--INSERT INTO @Table VALUES(4,45,1,66,DATEADD(dd,1,GETDATE()),2)
--INSERT INTO @Table VALUES(4,45,2,55,DATEADD(dd,2,GETDATE()),2)
--INSERT INTO @Table VALUES(5,60,1,39,GETDATE(),1)
--INSERT INTO @Table VALUES(5,60,2,44,DATEADD(dd,1,GETDATE()),1)
--INSERT INTO @Table VALUES(5,60,3,76,DATEADD(dd,2,GETDATE()),1)
INSERT INTO @Table
SELECTTOP 50000 ( ABS( CHECKSUM( NEWID() ) ) % 5000 ) + 1,
( ABS( CHECKSUM( NEWID() ) ) % 5000 ) + 1,
( ABS( CHECKSUM( NEWID() ) ) % 10 ) + 1,
( ABS( CHECKSUM( NEWID() ) ) % 100 ) + 1, GETDATE(),
( ABS( CHECKSUM( NEWID() ) ) % 5 ) + 1
FROMsys.columns c1, sys.columns c2
--SELECT * FROM @Table
-- OP Version
PRINT 'OP Version:'
declare @date datetime
set @date = getdate()
set statistics io on
Select * from (
SELECT * ,
case
when Prefernce = 1 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks desc)
when Prefernce = 2 then Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Asc)
when Prefernce = 3 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Asc)
when Prefernce = 4 then Row_number()over(Partition by AssessmentID,SubmissionId order by Attemptno Desc)
else
Row_number()over(Partition by AssessmentID,SubmissionId order by ObtainedMarks Desc)
end As Ranks
FROM @Table
) as S
where Ranks = 1
print datediff(ms, @date, getdate())
-- My Version
PRINT 'My Version:'
set @date = getdate()
; WITH Ranks
AS
(
SELECT *, ROW_NUMBER() OVER( PARTITION BY AssessmentID, SubmissionId ORDER BY ( CASE Prefernce WHEN 1 THEN -ObtainedMarks WHEN 2 THEN ObtainedMarks WHEN 3 THEN Attemptno WHEN 4 THEN -Attemptno ELSE -ObtainedMarks END ) ASC ) AS Ranks
FROM @Table
)
SELECT *
FROM Ranks
WHERE Ranks = 1
print datediff(ms, @date, getdate())
set statistics io off
Your version took 1561ms on an average 10 executions on the table with 50000 rows whereas the above version took 704ms.
--Ramesh
May 9, 2009 at 10:53 am
Wow!!
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2009 at 12:02 am
Thanx Friend!
May 11, 2009 at 12:51 am
You are welcome, and I am glad I could help you.
--Ramesh
May 11, 2009 at 3:28 am
Hi,Ramesh ..... May I know the reason for the Improvance of performance of your Query Over my Query....
I am keen to know ....
May 11, 2009 at 4:38 am
ningaraju.n (5/11/2009)
Hi,Ramesh ..... May I know the reason for the Improvance of performance of your Query Over my Query....I am keen to know ....
Its because of the four ROW_NUMBER() functions doing 4 SORT operations (can be seen in Execution Plans).
Just a note, SORT operation is one of the costliest operation in SQL Server.
--Ramesh
May 12, 2009 at 2:23 am
Thanx ramesh..... How do Increase my Coding technique in Sql Server in an effective way...Could u plz help me...
May 27, 2009 at 8:20 am
Hi Ramesh, I was trying to learn effectiveness from your well written query, In ur query you have been sorting all "Preference" categories by Ascending, but in original query ObtainedMarks and Attemptno are sorted in Descending order? just wanted to make sure if I am missing something or I took it right. Thanks
May 27, 2009 at 8:50 am
Mayank Khatri (5/27/2009)
Hi Ramesh, I was trying to learn effectiveness from your well written query, In ur query you have been sorting all "Preference" categories by Ascending, but in original query ObtainedMarks and Attemptno are sorted in Descending order? just wanted to make sure if I am missing something or I took it right. Thanks
Well, if you had studied that query correctly you would see that the ordering is done in ascending order but the values being used for ordering are reversed (i.e. negated the positive value and vice-versa).
For e.g. consider the value set (3, 5, -4), ordering on actual values in ascending order will give (-4, 3, 5) but ordering on negated values (i.e. value set would be become (-3, -5, 4)) will give (5, 3, -4).
I hope I made clear enough for you to understand.
--Ramesh
May 27, 2009 at 8:55 am
I apologise for missing that, thanks for taking out time and really appreciate your work. Thanks for sharing :-).
May 27, 2009 at 9:11 am
Mayank Khatri (5/27/2009)
I apologise for missing that, thanks for taking out time and really appreciate your work. Thanks for sharing :-).
It's okay, no need of apologies after all we are all humans bound to make mistakes. You are welcome, and thanks for the appreciation.
--Ramesh
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply