Jag håller på med ett system där ett stort antal användare (>50.000) tävlar mot varandra. Varje inmatning av tävlingsdata ska resultera i en uppdatering av statistiken, dvs användarnas inbördes placering grundat på deras totala poäng.
Varje enskild användare kommer att göra sin inmatning en gång om dagen, men kommer antagligen att kolla statistiken desto oftare.

Problemet är att jag inte riktigt vet hur jag ska få fram ställningen i tävligen, dvs användarnas placering i resultatlistan.
Den lösning jag har kommit fram till så här långt går ut på följande:

1. Vid varje inmatning räknas totalen om för användaren mha en stored procedure.
2. Läs in alla rader från användartabellen i en temporärtabell med en identitykolumn sorterat på poäng.
3. Genomför någon slags batch update för att skriva placeringen (identitykolumnen) till placeringsfältet i användartabellen.

Steg 1 är inga problem, men 2 & 3 känns som riktiga flaskhalsar. Det kan trots allt handla om att skyffla runt 50.000 rader. Det är också ganska troligt att den här funktionen kan komma att köras ofta, tex när alla användare ska gå in samtidigt på måndag morgon för att registrera sina resultat.

Någon som har en smartare lösning?

Tillägg: Här kommer resultatet av mina tester.

Först en dummy tabell "a", som jag har fyllt med 100.000 rader med slumptal i fältet Points.

CREATE TABLE [dbo].[a](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Points] [int] NOT NULL,
[Placement] [int] NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Sen kommer då artilleriet i form av denna stored procedure:

CREATE PROCEDURE [dbo].[atest]
AS
BEGIN
SET NOCOUNT ON;
create table #a (Rownumber int identity(1,1), aid int, points int)
insert into #a
select id, points from a order by points desc

update a
set placement = rownumber
from #a
where a.id = #a.aid

select * from a order by placement

END

Denna tar ca två sekunder att köra när den väl är kompilerad på min 3GHz maskin med 3GB RAM.

Skulle verkligen bli glad för lite feedback! Är detta bra eller dåliga värden? Och framförallt - vad kan hända om tio olika trådar vill köra proceduren samtidigt?

Varför inte beräkna placeringen varje gång någon vill veta.

Med index på points borde inte följande fråga ta någon tid

SELECT count(id)+1 FROM order WHERE points > (SELECT points FROM order WHERE id = 'den som frågar')

Främst av skälet att man inte bara vill se sin egen placering, man vill även se de som ligger tio platser ovanför och tio platser efter. (Eller topp 100, sämsta 100 etc)

Men visst skulle det vara snyggare om man kunde beräkna placeringen dynamiskt.

Bara en tanke, men kan du inte skriva om proceduren så den inte måste in och peta på hela tabellen varje gång den körs? Då borde du slippa en del overhead och jobbiga lås

Kunden vill absolut att resultaten ska visas i realtid, så det är kört med schemaläggning.

Ska testa lite mer med row_count().

Du har SELECT RANK, DENSE_RANK, ROWNUMBER m.fl. funktioner, som gör att du bara sorterar listan, därefter (när användaren tittar på statistik) räknas placeringen automatiskt ut.

/micke

Tack för alla tips! Det blir en lösning baserad på ROW_NUMBER.