April 29, 2002 at 7:00 pm
Edit... Found a post from last September that covers this and I will look into that.
I am trying to select a set of records by rank from a table. The records are sorted by the field esum_AVGScore (desc). I will have about 20k+ records in the table.
create proc ech_LeaderboardByRange_s
@iStartRankint,
@iNumRows int
as begin
set nocount on
create table #Leaderboard (
ldb_UID int identity(1,1),
ldb_UserUID int,
ldb_AtBats int NULL ,
ldb_Runs int,
ldb_Hits int,
ldb_Doubles int,
ldb_Triples int,
ldb_Homeruns int,
ldb_SO int,
ldb_Fouls int,
ldb_RBIs int,
ldb_Score int,
ldb_SLG float,
ldb_AVG float,
ldb_Games int,
ldb_AVGScore float
)
declare @iEndRank int
set @iEndRank = @iStartRank + @iNumRows - 1
insert#Leaderboard
selecthusr_UID, esum_AtBats, esum_Runs, esum_Hits, esum_Doubles,
esum_Triples, esum_Homeruns, esum_SO, esum_Fouls, esum_RBIs,
esum_Score, esum_SLG, esum_AVG, esum_Games, esum_AVGScore
fromECH_Summary
order by esum_AVGScore desc
selecthusr_DisplayName, husr_UID, ldb_AtBats, ldb_Runs, ldb_Hits,
ldb_Doubles, ldb_Triples, ldb_Homeruns, ldb_SO, ldb_Fouls,
ldb_RBIs, ldb_Score, ldb_SLG, ldb_AVG, ldb_Games, ldb_AVGScore
from#Leaderboard L
join HTP_User U on L.ldb_UserUID = U.husr_UID
whereldb_UID between @iStartRank and @iEndRank
order byldb_AVGScore desc
drop table #Leaderboard
end
The only way I have figured out how to do this is by creating a temp table that ranks the records then I can select using the identity field. I've also thought about doing this with a SQL Job that updates a leaderboard table at a set interval and use that to get my range of records.
Playing around with "SELECT Top n", it appears that n can't be a variable?
Any suggestions would be appreciated.
thx, Joe
Edited by - jmoneil on 04/29/2002 7:09:54 PM
April 29, 2002 at 11:46 pm
you can use the SET ROWCOUNT command to return a variable number of rows however your query requires a range lookup !! i.e (BETWEEN) so i don't think the top n approach or rowcount would work ..your procedure looks clean and simple ,best to keep it that way. if the rowset is quite big you can probably put a Primary key constraint on the ldb_UID field (CLUSTERED) to speed it up a bit
April 30, 2002 at 6:26 am
Can you post the ddl of the main tables so I cn make sure I getting this right and an example of the output you expect. I believe it can be done without the temp table and give you the range you desire but need a bit to make sure I am on the same page as you.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 11:19 am
ECH_Summary DDL
-------------------------------------
CREATE TABLE [dbo].[ECH_Summary] (
[husr_UID] [int] NOT NULL ,
[esum_AtBats] [smallint] NULL ,
[esum_Runs] [smallint] NOT NULL ,
[esum_Hits] [smallint] NOT NULL ,
[esum_Doubles] [smallint] NOT NULL ,
[esum_Triples] [smallint] NOT NULL ,
[esum_Homeruns] [smallint] NOT NULL ,
[esum_SO] [smallint] NOT NULL ,
[esum_Fouls] [smallint] NOT NULL ,
[esum_RBIs] [smallint] NOT NULL ,
[esum_Score] [int] NOT NULL ,
[esum_SLG] AS ((convert(float,[esum_Hits]) + convert(float,[esum_Doubles]) + convert(float,[esum_Triples]) * 2 + convert(float,[esum_Homeruns]) * 3) / convert(float,[esum_AtBats])) ,
[esum_AVG] AS (convert(float,[esum_Hits]) / convert(float,[esum_AtBats])) ,
[esum_Games] [int] NULL ,
[esum_AVGScore] AS (convert(float,[esum_Score]) / convert(float,[esum_Games]))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ECH_Summary] WITH NOCHECK ADD
CONSTRAINT [DF_ECH_Summary_erst_Runs] DEFAULT (0) FOR [esum_Runs],
CONSTRAINT [DF_ECH_Summary_erst_Hits] DEFAULT (0) FOR [esum_Hits],
CONSTRAINT [DF_ECH_Summary_erst_Doubles] DEFAULT (0) FOR [esum_Doubles],
CONSTRAINT [DF_ECH_Summary_erst_Triples] DEFAULT (0) FOR [esum_Triples],
CONSTRAINT [DF_ECH_Summary_erst_Homeruns] DEFAULT (0) FOR [esum_Homeruns],
CONSTRAINT [DF_ECH_Summary_esum_SO] DEFAULT (0) FOR [esum_SO],
CONSTRAINT [DF_ECH_Summary_erst_RBIs] DEFAULT (0) FOR [esum_RBIs],
CONSTRAINT [DF_ECH_Summary_esum_Score] DEFAULT (0) FOR [esum_Score],
CONSTRAINT [PK_ECH_Summary] PRIMARY KEY NONCLUSTERED
(
[husr_UID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ECH_Summary] ADD
CONSTRAINT [FK_ECH_Summary_HTP_User] FOREIGN KEY
(
[husr_UID]
) REFERENCES [dbo].[HTP_User] (
[husr_UID]
)
GO
---------------------------------------
The expected output would just be a list of users with their related stats. The below is from "exec ech_LeaderboardByRange_s 20, 10". i.e. Find the 20th ranked person and then return the next 10 users starting with the 20th.
User961049610120105198392465996590.50.1583333333333333319659.0
User28824288237790186310202255496570.734748010610079550.4933687002652519819657.0
User67384673817074561941335131296230.717647058823529420.3294117647058823519623.0
User2448424486168618343207299695960.41071428571428570.2970779220779220519596.0
User2314423141277022390489411695751.61417322834645670.1732283464566929119575.0
User83214832115046984011748349595631.27333333333333340.6533333333333333219563.0
User2864428645417745133335156695570.301293900184842878.3179297597042512E-219557.0
User1420414205008340533232188595420.293999999999999988.0000000000000002E-219542.0
User22234222356475177323443187394890.40248226950354610.3138297872340425319489.0
User3249432495622030622116403594790.183274021352313165.3380782918149468E-219479.0
May 1, 2002 at 3:42 pm
Hi,
Try the following. I've only tried it on a cut down version of data, so excuse me if there is a syntax error (or two). It seems to work OK.
select rank = (select count(*)
from ECH_Summary ech2
where ech2.AVGScore >= ech1.AVGScore ),
ech1.husr_UID,
ech1.esum_AtBats,
ech1.esum_Runs,
ech1.esum_Hits,
ech1.esum_Doubles,
ech1.esum_Triples,
ech1.esum_Homeruns,
ech1.esum_SO,
ech1.esum_Fouls,
ech1.esum_RBIs,
ech1.esum_Score,
ech1.esum_SLG,
ech1.esum_AVG,
ech1.esum_Games,
ech1.esum_AVGScore
from ECH_Summary ech1
where rank between @iStartRank and @iEndRank
order by esum_AVGScore desc
Regards
Tony Healey
http://www.SQLCoder.com - Free Code generation for SQL Server 7/2000
Regards
Tony Healey
www.SQLCoder.com - Free Code generation for SQL Server 7/2000
May 1, 2002 at 3:55 pm
Sorry, it only works if you omit the 'where rank between @iStartRank and @iEndRank' clause.
Regards
Tony Healey
http://www.SQLCoder.com - Free Code generation for SQL Server 7/2000
Regards
Tony Healey
www.SQLCoder.com - Free Code generation for SQL Server 7/2000
May 1, 2002 at 4:07 pm
Interesting way to create the rankings. Unfortunately this is rather slow even with only 10k records in my table. I will be having at least 20k+ records in my table.
Thanks, Joe.
May 1, 2002 at 8:06 pm
Just a little twist on the other but I believe short of doing a temp table this is possible. However the fact that esum_AVGScore is a calculated field and caoont be indexed does make it harder to do.
SELECT
ech1.husr_UID,
ech1.esum_AtBats,
ech1.esum_Runs,
ech1.esum_Hits,
ech1.esum_Doubles,
ech1.esum_Triples,
ech1.esum_Homeruns,
ech1.esum_SO,
ech1.esum_Fouls,
ech1.esum_RBIs,
ech1.esum_Score,
ech1.esum_SLG,
ech1.esum_AVG,
ech1.esum_Games,
ech1.esum_AVGScore
FROM
ECH_Summary ech1
WHERE
(SELECT COUNT(ech1.esum_AVGScore) + 1 AS Cnt FROM ECH_Summary ech2 where ech2.esum_AVGScore > ech1.esum_AVGScore) between @iStartRank and @iEndRank
ORDER BY
esum_AVGScore desc
If that doesn't do it then try the following.
CREATE TABLE #Leaderboard (
ldb_UID int identity(1,1),
ldb_UserUID int
)
INSERT
#Leaderboard (ldb_UserUID)
SELECT
husr_UID
FROM
ECH_Summary
ORDER BY
esum_AVGScore desc
SELECT
ech1.husr_UID,
ech1.esum_AtBats,
ech1.esum_Runs,
ech1.esum_Hits,
ech1.esum_Doubles,
ech1.esum_Triples,
ech1.esum_Homeruns,
ech1.esum_SO,
ech1.esum_Fouls,
ech1.esum_RBIs,
ech1.esum_Score,
ech1.esum_SLG,
ech1.esum_AVG,
ech1.esum_Games,
ech1.esum_AVGScore
FROM
ECH_Summary ech1
INNER JOIN
(
SELECT
ldb_UID,
ldb_UserUID
FROM
#Leaderboard
WHERE
ldb_UID BETWEEN @iStartRank and @iEndRank
)
ON
ech1.husr_UID = ldb_UserUID
ORDER BY
ldb_UID
Now a few notes to speed things up. Change your primary key to a clustered index instead of nonclustered, makes joins go faster and saves data space unless you have a specific reason why not.
Also if you there is no reason to absolutely have it as a temp table make it into an actuall table with ldb_UID as the primary index with clustered index. When changes are made to the data refresh this ranking table by truncating the table and refill with the new data. This will make reporting when no changes have occurred a much easier and faster process. Just depends on your specific needs but these will help you save resources on the server overall.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 1, 2002 at 8:40 pm
Thanks for everyone who responded. Looks like based on our needs we will probably just go with a table that gets "refreshed" as needed.
Trying to rank potentially 20k+ records and get a range "real time" just can't get the performance that we need.
If anyone is interested in timing on different solutions let me know and I can post that.
Antares, I added the clustered indexes as suggested (don't know why I didn't have that on anyway). Also, thanks for pointing out that my temp table didn't really need all of the fields.
Again, thanks to all.
--Joe
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply