Effiziente Suche nach eindeutigen Werten in einer Datenbanktabelle

Question

Ich habe eine Datenbanktabelle mit einer sehr großen Anzahl von Zeilen. Diese Tabelle stellt Meldungen dar, die von einem System protokolliert werden. Jede Meldung hat einen Meldungstyp und dieser wird in einem eigenen Feld in der Tabelle gespeichert. Ich schreibe eine Website zur Abfrage dieses Meldungsprotokolls. Wenn ich nach dem Meldungstyp suchen möchte, würde ich idealerweise ein Dropdown-Feld haben wollen, in dem die Meldungstypen aufgelistet sind, die in der Datenbank aufgetaucht sind. Die Nachrichtentypen können sich im Laufe der Zeit ändern, so dass ich die Typen nicht fest in die Auswahlliste einfügen kann. Ich muss eine Art von Lookup durchführen. Das Iterieren über den gesamten Tabelleninhalt, um eindeutige Meldungswerte zu finden, ist offensichtlich sehr dumm, aber da ich auf dem Gebiet der Datenbank dumm bin, frage ich hier nach einem besseren Weg. Vielleicht wäre eine separate Nachschlagetabelle, die von der Datenbank gelegentlich aktualisiert wird und nur die eindeutigen Nachrichtentypen auflistet, aus denen ich meine Auswahlliste auffüllen kann, eine bessere Idee.

Für Vorschläge wären wir sehr dankbar.

Die von mir verwendete Plattform ist ASP.NET MVC und SQL Server 2005

Answer 1

Eine separate Nachschlagetabelle mit der Kennung des in Ihrem Protokoll gespeicherten Nachrichtentyps. Dies verringert die Größe und erhöht die Effizienz des Protokolls. Außerdem würde es Normalisieren Ihre Daten.

Answer 2

Ja, ich würde auf jeden Fall die separate Nachschlagetabelle verwenden. Sie können dann befüllen es mit etwas wie:

INSERT TypeLookup (Type)
SELECT DISTINCT Type
FROM BigMassiveTable

Sie könnten dann in regelmäßigen Abständen einen Zusatzauftrag ausführen, um neue Typen aus Ihrer Haupttabelle zu holen, die noch nicht in der Lookup-Tabelle vorhanden sind.

Answer 3

SELECT  DISTINCT message_type
FROM    message_log

ist der einfachste, aber nicht sehr effiziente Weg.

Wenn Sie eine Liste von Typen haben, die möglicherweise im Protokoll erscheinen, verwenden Sie dies:

SELECT  message_type
FROM    message_types mt
WHERE   message_type IN
        (
        SELECT  message_type
        FROM    message_log
        )

Dies wird effizienter sein, wenn message_log.message_type indiziert ist.

Wenn Sie diese Tabelle nicht haben, aber eine erstellen wollen, und message_log.message_type indiziert ist, verwenden Sie eine rekursive CTE um eine lose Indexsuche zu emulieren:

WITH    rows (message_type) AS
        (
        SELECT  MIN(message_type) AS mm
        FROM    message_log
        UNION ALL
        SELECT  message_type
        FROM    (
                SELECT  mn.message_type, ROW_NUMBER() OVER (ORDER BY mn.message_type) AS rn
                FROM    rows r
                JOIN    message_type mn
                ON      mn.message_type > r.message_type
                WHERE   r.message_type IS NOT NULL
                ) q
        WHERE   rn = 1
        )
SELECT  message_type
FROM    rows r
OPTION (MAXRECURSION 0)

Answer 4

Ich wollte nur auf das Offensichtliche hinweisen: Normalisieren Sie die Daten.

message_types
message_type | message_type_name

messages
message_id | message_type | message_type_name

Dann können Sie einfach auf eine zwischengespeicherte DISTINCT verzichten:

Für Ihr Dropdown

SELECT * FROM message_types

Für Ihren Abruf

SELECT * FROM messages WHERE message_type = ? 

SELECT m.*, mt.message_type_name FROM messages AS m
JOIN message_types AS mt
ON ( m.message_type = mt.message_type)

Ich bin mir nicht sicher, warum Sie eine gecachte DISTINCT die Sie aktualisieren müssen, wenn Sie können leicht das Schema zu optimieren und eine mit RI zu haben.

Answer 5

Erstellen Sie einen Index für den Nachrichtentyp:

CREATE INDEX IX_Messages_MessageType ON Messages (MessageType)

Um dann eine Liste von eindeutigen Nachrichtentypen rennen Sie:

SELECT DISTINCT MessageType
FROM Messages
ORDER BY MessageType

Da der Index physisch in der Reihenfolge der MessageType SQL Server kann sehr schnell, und effizient Durchsuchen Sie den Index und erstellen Sie eine Liste eindeutiger Nachrichtentypen.

Das ist keine schlechte Leistung - das ist die Stärke von SQL Server.

---

Zugegeben, Sie können etwas Platz sparen, indem Sie einen " Nachrichtentypen Tisch". Und wenn Sie nur einige wenige Nachrichten auf einmal anzeigen: dann wird die Lesezeichen-Suche da sie sich wieder mit der MessageTypes Tisch, wird kein Problem sein. Wenn Sie aber anfangen, Hunderte oder Tausende von Nachrichten auf einmal anzuzeigen, dann wird die Verknüpfung zurück zu MessageTypes kann ziemlich teuer und unnötig werden, und es wird schneller sein, die MessageType mit der Nachricht gespeichert.

Aber ich hätte kein Problem mit der Erstellung eines Indexes für die MessageType und die Auswahl der Spalte distinct . SQL Server liebt diese Art von Dingen. Wenn Sie jedoch feststellen, dass dies eine echte Belastung für Ihren Server darstellt, sobald Sie Dutzende von Treffern pro Sekunde erhalten, sollten Sie den anderen Vorschlag befolgen und diese im Speicher zwischenspeichern.

Meine persönliche Lösung wäre:

• den Index erstellen
• einzeln auswählen

und wenn ich immer noch Probleme hätte

• Cache im Speicher, der nach 30 Sekunden abläuft

---

Was die Frage normalisiert/denormalisiert betrifft. Die Normalisierung spart Platz, allerdings auf Kosten der CPU, wenn ständig Joins durchgeführt werden. Der logische Sinn der Denormalisierung besteht jedoch darin, doppelte Daten zu vermeiden, was zu inkonsistenten Daten führen kann.

Haben Sie vor, den Text eines Nachrichtentyps zu ändern, der, wenn Sie ihn zusammen mit den Nachrichten speichern, alle Zeilen aktualisieren müsste?

Oder spricht etwas dafür, dass zum Zeitpunkt der Meldung der Meldungstyp war "Antwort des Kunden angefordert"?