MySQL's LIMIT clause in MSSQL!

[_Thanatos_]

Ik begin ff met een link naar een topic dat ik eerder in mijn leven hier gepost heb:

[rml][ MSSQL] Search-results limiteren[/rml]

Kort samengevat: het gaat er in dit topic om, dat een statement in MSSQL de eerste 10, de tweede 10, de derde 10 (enz) records teruggeeft. Het kwam erop neer dat er 3 oplossingen waren:

• gebruik TOP en scroll door
• gebruik ADO's paging mechanisme
• combinatie van deze twee

Nu, ik en m'n SQL-collega hebben twee manieren gevonden waarbij het efficiënter kan, ook al zal het nog steeds heel veel CPU-tijd van de server opeisen.

De eerste manier is door TOP 2x te gebruiken met een intensievere WHERE clause. Stel dat je de tweede set van 10 records wilt. Je pakt alle records en kijkt welke daarvan niet de bovenste 10 zijn. van wat je overhoudt pak je weer de bovenste 10:

code:

SELECT TOP 10 * FROM (
   SELECT * FROM Artikelen WHERE ID NOT IN (
      SELECT TOP 10 ID FROM Artikelen ORDER BY ID
   )
) AS x

De tweede manier lijkt er wel op, maar is toch iets anders en ik denk ook minder efficiënt omdat de sortering twee keer omgedraaid wordt. Aan de andere kant wordt er geen "SELECT * FROM" gedaan over de betreffende tabel. Het principe is vrij eenvoudig, maar niet om zelf 'ff' te bedenken
Men neme de bovenste 20 records, je sorteert ze in normale volgorde, zodat je de bovenste 20 krijgt. Daaruit pak je de bovenste 10 die je in omgekeerde volgorde sorteert, zodat je de onderste 10 krijgt. Tot slot pak je van het resultaat alles dat je in normale volgorde sorteert:

code:

SELECT * FROM (
   SELECT TOP 10 * FROM (
      SELECT TOP 20 * FROM Artikelen ORDER BY ID
   ) as x
   ORDER BY ID DESC) as y
ORDER BY ID

Dit is misschien wel een leuke voor de faq, want ik kan me voorstellen dat meer mensen met hetzelfde probleem zitten. Dit is immers een veel voorkomend probleem bij het weergeven van zoekresultaten per pagina van bijv 10.

Mijn vraag nu (3 eigenlijk): zijn er mensen die dit misschien ook gedaan hebben en tot een andere efficiëntere oplossing gekomen zijn? Of zijn er misschien mensen die deze twee gegeven queries kunnen verbeteren in performance? Zijn er misschien lui die het voormekaar krijgen om in dit soort queries geen veld met unieke inhoud nodig te hebben?

Ben benieuwd

[ACM]

Kan je dit niet beter met een cursor oplossen?
Je beide queries zijn erg complex voor zulk soort simpele dingen, misschien is het zelfs efficienter om gewoon domweg de top20 op te halen en dan de eerste 10 over te slaan in je applicatie code.

In Oracle had ik bovenstaande principe en iets ala:
select *, rownum from (select * from tabelletje...) where rownum BETWEEN 10 and 20

de laatste was bij "diep bladeren" sneller, de eerste was bij "ondiep bladeren" sneller.

dit topic had ik daarover: [rml][ Oracle(/sql/php)] Efficiente "limit" vervanger[/rml]
in hoeverre dat toepasbaar in MSSQL is weet ik niet.

[_Thanatos_]

Rownum ken ik niet in MSSQL...

Maar als je de TOP 20 naar de client haalt, veroorzaakt dat bij de 100ste pagina natuurlijk gigantische hoeveelheden netwerkverkeer tussen de sql server en de webserver of client applicatie (het laatste geval is nog het ergste..)

Ik denk ook dat het cacheing machanisme van MSSQL slim genoeg is om bij soortgelijke queries niet de hele meuk opnieuw te hoeven rekenen, maarja, dat weet ik niet zeker dus.

Bovendien kun je door een beetje slim te indexeren natuurlijk ook een hoop performance winnen.

[zneek]

Als ik me niet vergis kan MSSQL ook van-tot selecties doen. Dus record 10 - 20. Kweet alleen niet precies hoe. Dat zoek ik maandag op kantoor eff op

[cameodski]

In het verleden ben ik uitgebreid aan het zoeken geweest naar een mogelijkheid om bijv. record 10 - 20 op te vragen, maar tot nu toe heb ik niets gevonden.
Er blijven dan grofweg twee oplossingen over voor dit probleem:

De eerste is degene waarvan al twee varianten beschreven zijn.
Bij deze een aangepaste en iets efficientere versie van de eerste variant:

SELECT TOP 10 *
FROM Artikelen
WHERE ID NOT IN (SELECT TOP 10 ID FROM Artikelen ORDER BY ID)

Hoe meer records de subquery teruggeeft, hoe trager deze query trouwens wordt. Dus na verloop van tijd krijg je bijvoorbeeld deze query:

SELECT TOP 10 *
FROM Artikelen
WHERE ID NOT IN (SELECT TOP 1000 ID FROM Artikelen ORDER BY ID)

Daarom had ik nog een andere variant bedacht:

Elke keer als je tien records opgevraagd heb, het grootste ID van de vorige keer meegeven als filter. En als je hier vervolgens een stored procedure van maakt dan hoeft je query ook niets steeds "gecompiled" te worden.
De stored procedure gaat er dan als volgt uitzien:

CREATE PROCEDURE dbo.sp_Artikel
@PrevID int
AS
SELECT TOP 10 *
FROM Artikelen
WHERE ID > @PrevID
ORDER BY ID
RETURN (0)

Als er dan ook nog een (clustered) index ID ligt, dan is het echt bloedsnel.

[zneek]

zneek schreef op 16 augustus 2002 @ 23:54:
Als ik me niet vergis kan MSSQL ook van-tot selecties doen. Dus record 10 - 20. Kweet alleen niet precies hoe. Dat zoek ik maandag op kantoor eff op

Mmmmmm, ik dacht dat ik het met iemand over dit probleem gehad had, en dat daar een oplossing uit gerold was. Dit ging echter over de LIMIT clause... Ik weet het ook niet dus.

[_Thanatos_]

Ik heb zojuist op Google Groups nog een optie gevonden. Het grote voordeel van deze methode is dat je em kan gebruiken wanneer je geen identificerende kolom hebt (PK en/of identity). Maar ook deze methode heeft weer nadelen

code:

SELECT IDENTITY(int, 1, 1) AS Rownum, Titel INTO #Temp FROM Artikelen
SELECT * FROM #Temp WHERE Rownum BETWEEN 11 AND 20
DROP TABLE #Temp

Je ziet de nadelen? Ten eerste wordt de data iedere keer opnieuw opgevraagd (gelukkig kan dit door MSSQL wel heel goed gecached worden, als er voldoende geheugen beschikbaar is) en ten tweede is er een temp tabel nodig. Bovendien heb ik rare verhalen gehoord over dat een identity kolom niet altijd de juiste waarden genereert.
Wat vinden jullie hiervan?

[ACM]

_Thanatos_ schreef op 20 augustus 2002 @ 12:42:
Ten eerste wordt de data iedere keer opnieuw opgevraagd (gelukkig kan dit door MSSQL wel heel goed gecached worden, als er voldoende geheugen beschikbaar is) en ten tweede is er een temp tabel nodig. Bovendien heb ik rare verhalen gehoord over dat een identity kolom niet altijd de juiste waarden genereert.
Wat vinden jullie hiervan?

Met mysql's limit-clause wordt ook elke keer het hele resultset opgehaald tot en met de limit (en afhankelijk van de sortering dus alles).
Voor het naar de client gestuurd wordt.

[Woy]

Ik heb zelf ook zoiets gemaakt voor een gastenboek wat ik gemaakt heb. Dit heb ik toen opgelost door gewoon gebruik te maken van de primary id waar natuurlijk ook een index opzit. en vervolgens gebruik te maken van een offset. volgens mij heb ik het ongeveer als volgt gedaan

select top 10 *
from tabel
where id < (( Select max( id ) from tabel ) + offset )

[Annie]

_Thanatos_ schreef op 20 augustus 2002 @ 12:42:
Je ziet de nadelen? Ten eerste wordt de data iedere keer opnieuw opgevraagd (gelukkig kan dit door MSSQL wel heel goed gecached worden, als er voldoende geheugen beschikbaar is) en ten tweede is er een temp tabel nodig. Bovendien heb ik rare verhalen gehoord over dat een identity kolom niet altijd de juiste waarden genereert.
Wat vinden jullie hiervan?

Methode ziet er goed uit. Eventueel kan je deze nog wat versnellen in sommige gevallen door gebruik te maken van ROWCOUNT zodat je niet telkens de complete dataset meeneemt in je temptable. In mssql2000 kan je overigens ook gebruik maken van het table vartype waardoor de gegevens in het geheugen worden afgehandeld ipv in je tempdb (maar dan moet je weer wel voldoende geheugen hebben en/of een niet te grote resultset).

Voorbeeldje voor de Northwind db:

code:

use Northwind

declare @start int, @stop int
set @start = 21
set @stop  = 30

set rowcount @stop

select identity(int, 1, 1) as Rownum, CustomerID, OrderDate
into #Temp from Orders

select * from #Temp where Rownum between @start and @stop
drop table #Temp

Verder blijft het natuurlijk altijd voor- en nadelen afwegen en op basis van je eigen situatie een keuze maken voor een bepaalde techniek.

Keep 'm coming, * Annie vindt dit wel leuk.

[ Voor 0% gewijzigd door Annie op 20-08-2002 14:57 . Reden: layout ]