SQL TOP X oder FETCH NEXT X ROWS ONLY

[SaxnPaule]

Hallo Community,

ich bin gerade dabei ein DB Query zusammenzustellen und habe einige Bedenken hinsichtlich der Performance.

Perspektivisch werden ca. 1 Mio. Datensätze in der Tabelle (MS SQL 2014) erwartet.

Ich benötige jetzt eine Query, die mir immer die X neusten Einträge einer bestimmten id ermittelt.

Hierfür habe ich jetzt die beiden Variante:

SELECT * FROM (SELECT * FROM table WHERE contentid = '"id1"' ORDER BY creationdate DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) bla
UNION
SELECT * FROM (SELECT * FROM table  WHERE contentid = '"id2"' ORDER BY creationdate DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) bla
UNION
SELECT * FROM (SELECT * FROM table  WHERE contentid = '"id3"' ORDER BY creationdate DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) bla
UNION
. . .

SELECT * FROM (SELECT TOP 3 * FROM table WHERE contentid = '"id1"' ORDER BY creationdate DESC) bla
UNION
SELECT * FROM (SELECT TOP 3 * FROM table WHERE contentid = '"id2"' ORDER BY creationdate DESC) bla
UNION
SELECT * FROM (SELECT TOP 3 * FROM table WHERE contentid = '"id3"' ORDER BY creationdate DESC) bla
UNION
. . .

Wisst ihr, welche Variante performanter ist? Seht ihr generell eine Variante die Abfrage besser zu gestalten?

 

[Enigma]

Ich komme aus der MySQL-Richtung, aber die Mengen und Index-Operationen müssten in der MSSQL gleich funktionieren

Das was du vorhast ist eigentlich kein Thema, du musst nur n Index auf (content-id, creationdate) legen. Und darauf achten, dass contentid wirklich ein integer ist und creationtime ein integer oder datetime-feld.

PS: Und falls möglich, würde ich den Union weg lassen und der DB die Anfragen einzeln stellen (zwecks query cache)

PS2: Falls bekannt, könntest du das creation-date noch weiter einschräken, damit die order-by operation im memory durchgeführt werden kann, falls dort zu viele rows je contentid sind:

SELECT * FROM table  WHERE contentid = 2 AND creationdate >= $HEUTE_MINUS_12MONATE ORDER BY creationdate DESC LIMIT 3

 

[SaxnPaule]

Ein Index liegt bereits auf dem contentid Feld, allerdings ist das ein varchar(255). Auf dem creationdate Feld vom Typ datetime liegt bisher noch kein Index.

PS: Ich denke das würde die DB in die Knie zwingen. Wenn pro Nutzer (600 concurrent users erwartet) statt einer 10 - 20 Queries abgesetzt werden.

PS2: Die Einschränkung auf einen bestimmten Zeitraum wird in einem anderen UseCase abgebildet, der hier explizit nicht gewünscht ist.

 

[Enigma]

Dann zerhackt es dir deine Performance
1. Die Länge 255 geht extrem auf den Arbeitsspeicher weil der Index riesig wird
2. Ein Index auf so langen Char-Feldern kann nicht mehr nach log(n) abgearbeitet werden, das heisst mit steigender Datensatzanzahl dauert die query nahezu linar länger

Wenn du das nicht umstellen kannst, schau dass du die länge des Felds auf das Mindeste beschränkst (z.B: CHAR(16)), dann hält sich der Impact halbwegs in grenzen.

Edit: Wichtig ist auch der Covering-Index auf (in genau der Reihenfolge) (content_id, creationdate), damit er ohne index-merge auf seine row-ids kommt

 

[SaxnPaule]

Okay, ich denke das ID Feld kann ich bis auf 32 Zeichen runterbrechen. Einen Index auf das creationdate zu legen ist auch kein Problem.

 

[Enigma]

Zum PS2:

bestimmten Zeitraum [...]explizit nicht gewünscht

Das war so nicht gemeint. Gemeint ist nur folgendes:
- Du hast eine Datenbank die wächst über 10 Jahre
- Du weisst wenn du die letzten 3 raussuchen willst, dass die innerhalb der letzten X Minuten reingekommen sind
- Ohne das das where auf creation-date muss er beim order by die GESAMTE historie sortieren (sprich die letzten 10 jahre)
- Durch den "trick" mit der Einschränkung auf eine nicht-realisitischen Zeitraum im use-case (z.B. 1 Tag, wenn du weisst alle 5 minuten kommen neue datensätze) hilft die Performance auch nach 10 Jahren nahezu unverändert ist, weil der order-by auf eine überschaubare anzahl hinaus läuft

 

[SaxnPaule]

Mhh dieser Usecase ist für Kommentare, daher wäre ein geeigneter Zeitraum wohl eher 6 Monate bei einer geschätzten Lebensdauer der Applikation von 5 Jahren.

Daher ist es auch schwer abzuschätzen, in welchem Zeitraum für alle angeforderten Contents die gewünschte Anzahl an Kommentaren abgegeben wurde.

Einen Caching Algoritmus implementiere ich noch applikationsseitig davor.

 

[Enigma]

Als Cache kann ich dir memcached empfehlen, der kennt schon TTL-Werte für Werte:

$ttl = 300;
$key = 'neuesteKommentare';
$neuesteKommentare = Memcache-Key-Lookup(key);
if(!$neuesteKommentare) {
    $neuesteKommentare = $datenbank->machquery("...");
    Memcache-Key-Store($key, $neuesteKommentare, $ttl);
}
return $neuesteKommentare;

PS: Großes Lob an dich, dass du jetzt schon so weit denkst. Ich seh es als Datenbank-Administrator, dass sich die Leute keine Gedanken darum machen und ich bei ner Lastspitze am offenen Herz operieren muss.

 

[SaxnPaule]

Ich setze überall ne Redis ein wenn es geht.

Ich würde die Kommentare so lange da drin lassen, bis ein neuer Kommentar für einen Content abgegeben wird, oder eine entsprechende Verdrängungstrategie greift.

PS: Danke dir ;-)
Wir haben in der Vergangenheit oft genug die von dir beschriebene Situation gehabt, weil am Anfang nicht mitgedacht wurde. Und dann durfte die eine oder andere Nachtschicht geschoben werden.

Ergänzung (22. Oktober 2015)

Um die Sache mal abzuschließen:
Laut Execution Plan ist es egal. Der größte Teil der Zeit geht für die Sortierung drauf.
Insgesamt sind die Queries beide gleich schnell.

Interessant wird das Ganze dann bei einer vollen Tabelle.