[MSSQL] select top 3 record van elke categorie

[haroldd]

ik heb een 25-tal vakken en wil van elke categorie de laatste 3 geplaatste berichten hebben.

daarvoor heb ik de volgende query:

code:

SELECT vak.vak_id, vak.vak_naam, adv.adv_titel, advertentie_id FROM tbl_advertentie adv
    INNER JOIN tbl_vak vak ON adv.vak_id = vak.vak_id
    WHERE adv.gebruiker_id NOT IN (1,2,3,4) ' lijst geblokkeerde gebruikers
        AND (adv.advertentie_id IN
            (SELECT TOP 3 advertentie_ID FROM tbl_advertentie
            WHERE vak_id = adv.vak_id
                AND gebruiker_id NOT IN (1,2,3,4) ' lijst geblokkeerde gebruikers
            ORDER BY advertentie_datum DESC))
    ORDER BY vak_naam

het probleem is dat deze query bij zo'n 2000 records er 10 seconden over doet en dat is natuurlijk wel wat lang. is er een betere (snellere) manier om de gewenste gegevens te krijgen?

[TheRookie]

Heb je indexen op de kolommen staan ?
Wat zegt het execution plan van de query analyser ?

[whoami]

Zoals TheRookie al zegt; kijk eens naar het execution plan.
Indexen op de goede kolommen, en de juiste clustered indexen kunnen heel wat oplossen.

Advertentie_datum zou bv een goede kandidaat kunnen zijn om een clustered index op te leggen.
Als je veel 'range-queries' doet (LIKE bv) en sorteert op vak_naam, kan je daar wellicht ook een clustered index op leggen.
(Lees wel eens de voor- en nadelen van clustered indexen na, en weet dat een tabel slechts 1 clustered index kan hebben, aangezien deze de fysieke opslagvolgorde bepaald).

Daarnaast kan je ook nagaan op welke velden je non-clustered indexen wilt leggen; op de velden waarop je joined en veel zoekt bv. Evt kan je beslissen om composite-indexen (indexen die meer dan 1 veld bevatten) te maken. Denk dan wel goed na over de volgorde van de velden binnen je index.

[ Voor 22% gewijzigd door whoami op 28-06-2006 10:41 ]

[haroldd]

TheRookie schreef op woensdag 28 juni 2006 @ 10:29:
Heb je indexen op de kolommen staan ?
Wat zegt het execution plan van de query analyser ?

advertentie_id en vak_id zijn primary keys van de tabellen. voor de rest heb ik geen indexen

het execution plan geeft overal cost: 0% aan.

[whoami]

haroldd schreef op woensdag 28 juni 2006 @ 10:42:
[...]


advertentie_id en vak_id zijn primary keys van de tabellen. voor de rest heb ik geen indexen

Maak dan de goeie indexen op andere velden, waar je op zoekt, sorteert, joined. (Zie m'n eerdere post).

het execution plan geeft overal cost: 0% aan.

Maar het execution plan zal wel aangeven dat er vrijwel overal table - scans gebruikt worden.

[The Fox NL]

Je zou de eerste "adv.gebruiker_id NOT IN (1,2,3,4)" weg kunnen halen omdat die check al in de subquery daarna komt.
Verder is het aan te raden om die 1,2,3,4 te vervangen door een sebquery die geblockte gebruikers ophaalt. (Ken de code niet, dus weet niet of het mogelijk is)

[haroldd]

The Fox NL schreef op woensdag 28 juni 2006 @ 10:54:
Je zou de eerste "adv.gebruiker_id NOT IN (1,2,3,4)" weg kunnen halen omdat die check al in de subquery daarna komt.
Verder is het aan te raden om die 1,2,3,4 te vervangen door een sebquery die geblockte gebruikers ophaalt. (Ken de code niet, dus weet niet of het mogelijk is)

die word ook door een subquery erin geplaatst maar heb deze even weggelaten om query niet onnodig complex te laten overkomen.

[haroldd]

whoami schreef op woensdag 28 juni 2006 @ 10:47:
[...]

Maak dan de goeie indexen op andere velden, waar je op zoekt, sorteert, joined. (Zie m'n eerdere post).


[...]

Maar het execution plan zal wel aangeven dat er vrijwel overal table - scans gebruikt worden.

een index op advertentie_datum scheelde inderdaad veel (binnen 2 sec uitgevoerd).

maar toen ik de query in m'n asp-pagina uitvoerde deed ie er nog ruim 10-15 seconden over. het bleek dat ik nog een foutje had gemaakt in een functie die het aantal vakken telt. Na verhelpen daarvan is ie weer lekker snel. bedankt voor de hulp

[whoami]

2 seconden is nog relatief veel.

Heb je daar een clustered index van gemaakt; op advertentie_datum ?
Heb je een (gewone) index op vak_id in de tabel advertentie ?
Heb je een index op gebruiker_id ?
Heb je een index op vak_naam ?

[haroldd]

whoami schreef op woensdag 28 juni 2006 @ 11:37:
2 seconden is nog relatief veel.

Heb je daar een clustered index van gemaakt; op advertentie_datum ?
Heb je een (gewone) index op vak_id in de tabel advertentie ?
Heb je een index op gebruiker_id ?
Heb je een index op vak_naam ?

nou ja op onze ontwikkel/test-machine deed ie er 2 seconden over (oud beestje), op de live-server deed ie er 0,0 sec over

ik heb alleen op advertentie_datum een gewone index gezet (clustered ging niet omdat de pk al clustered is), is het ook weer niet slecht als je teveel indexen gaat plaatsen?

[whoami]

Als je teveel indexen plaatst, heb je idd een overhead bij het inserten / updaten. Je moet gewoon de 'sweet spot' zien te vinden.
Echter, je moet nu ook niet denken dat een tabel slechts 1 index mag hebben. De overhead bij inserts/updates zal miniem zijn, terwijl je select queries veel sneller zullen zijn.
Je moet gewoon oppassen dat je geen indexen legt op velden die geen index nodig hebben (omdat er bv nooit of zelden op gezocht / gejoined / gesorteerd wordt).

Verwijder de clustered index op je PK, want daar heb je toch geen voordeel bij. Zorg ervoor dat de index op je PK dus een gewone index is.
Maak een clustered index op die datum, en leg ook indexen op die andere velden die ik aangaf.

[haroldd]

whoami schreef op woensdag 28 juni 2006 @ 12:04:
Verwijder de clustered index op je PK, want daar heb je toch geen voordeel bij. Zorg ervoor dat de index op je PK dus een gewone index is.
Maak een clustered index op die datum, en leg ook indexen op die andere velden die ik aangaf.

Ja baas

maar je hebt wel gelijk, nu ook op testmachine in 0,0 seconden