Paging på stor tabell

Hejsan!

Under en lång tid har jag haft ett paging-script som jag gjort med getrows. Har lagt samtliga rader i en array och sedan portionerat ut det per sida genom att bara loopa genom vissa arrayer.

Problemet är att tabellen nu har blivit på tok för stor. Att köra nedanstående SQL-fråga tar 13 sekunder då den inte har någon belastning. (Tabellen filmer har runt 10000 rader och tabellen betyg runt 60000).

SELECT titel, ar, f.id,COUNT(b.poang) AS votes, AVG(b.poang+0.0) AS betyg, CAST(handling AS varchar)
FROM filmer AS f
LEFT JOIN betyg b ON f.id = b.filmid
WHERE f.Status='ok'
GROUP BY f.id, titel, ar, CAST(handling AS varchar)
ORDER BY Titel

Har någon ett tips på hur jag ska göra en paging? Jag kör ASP+SQL Server.

Tack på förhand!

Tretton sekunder låter lite mycket. Har du indexerat dina tabeller ordentligt?

- - - - -

Varför inte bara använda vanlig paging med ADO?
http://www.codeproject.com/asp/rspaging.asp

mvh

Hejsan,

I tabellen Filmer har jag satt index på följande:
ID (clustred)
Titel
Ar
Status

I tabellen Betyg har jag satt index på följande:
ID
FilmID (clustred)
Poang

Efter att jag dessutom har tagit bort CAST(handling AS varchar) har jag fått ner det till 5 sekunder.

Är 5 sekunder acceptabelt för denna typ av frågor? Eller har du något tips på hur jag bättre bör indexera tabellerna?

/tillägg
Finns det inget annat sätt att smidigt göra paging på, så man slipper köra hela tabellen? Såsom det i MySQL finns LIMIT 100,10

5 sekunder låter också lite mycket. Troligen skulle ett kombinerat index, där f.Status ingår, hjälpa upp det hela.

Finns det inget annat sätt att smidigt göra paging på, så man slipper köra hela tabellen? Såsom det i MySQL finns LIMIT 100,10

Tyvärr finns inte LIMIT i SQL server (T-SQL). Det finns dock en del trick som gör att man kan efterlikna det.

Man kan t.ex. begränsa mängden data som returneras genom

SET ROWCOUNT 100
SELECT * FROM Tabellen

..returnerar bara de första 100 raderna. Precis som...

SELECT TOP 100 * FROM Tabellen

...gör

LIMIT 100,10 kan efterliknas genom

SELECT TOP 10 * FROM Tabellen WHERE NyckelID NOT IN (SELECT TOP 100 NyckelID FROM Tabellen ORDER BY Något) ORDER BY Något

Fördelen med att hämta ut hela tabellen är att man då även får det totala radantalet (kan vara lämpligt för att räkna ut antalet sidor). Det går dock utmärkt att ta med det genom en extra COUNT-sökning, vars resultat kan tas ut i ett eget recordset, eller via en extrakolumn i huvud-recordsetet.

DECLARE @count int
SELECT @count=COUNT(*) FROM Tabellen
SELECT TOP 10 *,@count as TotalCount FROM Tabellen WHERE NyckelID NOT IN (SELECT TOP 100 NyckelID FROM Tabellen ORDER BY Något) ORDER BY Något

Huruvida det hela blir snabbare än att hämta allt beror mycket på frågans komplexitet.

Hej igen,

Jag har försökt att optimera det så gott jag kan, men det verkar inte gå längre.

Jag har försökt att skriva om T-SQL-lösningen på MySQL:s LIMIT 100,10. Men vet ni hur jag skriver om den när jag kör med JOIN?

Tack på förhand!

Men en JOIN så ska du normalt sett använda samma join i alla tre SQL-satserna, men eftersom det är en LEFT JOIN i ditt fall så är den helt verkningslös. Det enda som ska vara med är Status-villkoret

DECLARE @count int

SELECT @count=COUNT(*) FROM filmer WHERE Status='ok'

SELECT TOP 10 titel, ar, f.id,COUNT(b.poang) AS votes, AVG(b.poang) AS betyg, CAST(handling AS varchar),@count as totalcount
FROM filmer AS f
LEFT JOIN betyg b ON f.id = b.filmid
WHERE f.Status='ok' 
AND f.id NOT IN (SELECT TOP 100 id FROM filmer WHERE Status='ok' ORDER BY Titel)
GROUP BY f.id, titel, ar, CAST(handling AS varchar)
ORDER BY Titel

Super! :-)